Get Data From XML variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get Data From XML variable

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-05 : 04:00:09

I am having an XML variable which is having data like.. this
DECLARE @xml XML
SET @xml=
'<order>
<item1d>
<id>1</id>
<qty>2</qty>
<itemprice>0</itemprice>
<addon>
<id>1</id>
<id>2</id>
<id>3</id>
</addon>
</item1d>
<item1d>
<id>3</id>
<qty>1</qty>
<itemprice>0</itemprice>
<addon></addon>
</item1d>
</order>'

I want results like this from this XML..
/*
iid qty ipr addon
1 2 0 1
1 2 0 2
1 2 0 3
3 1 0 NULL
*/

What i have done yet is..
SELECT x.v.value('(./id)[1]', 'INT') as itm_id,
x.v.value('(./qty)[1]', 'INT') as itm_qty,
x.v.value('(./itemprice)[1]', 'INT') as itm_price,
x.v.value('./addon[1]/id[1]', 'INT') as i
FROM @xml.nodes('/order/item1d')x(v)

But it is giving me value of first addon only.
Any Help..??

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 05:43:34

see this


DECLARE @xml XML
SET @xml=
'<order>
<item1d>
<id>1</id>
<qty>2</qty>
<itemprice>0</itemprice>
<addon>
<id>1</id>
<id>2</id>
<id>3</id>
</addon>
</item1d>
<item1d>
<id>3</id>
<qty>1</qty>
<itemprice>0</itemprice>
<addon></addon>
</item1d>
</order>'

SELECT --t.u.value('.[1]','int') as id,
x.v.value('(../id)[1]', 'INT') as iid,
x.v.value('(../qty)[1]', 'INT') as qty,
x.v.value('(../itemprice)[1]', 'INT') as ipr,
t.u.value('(.)[1]', 'INT') as i
FROM @xml.nodes('/order/item1d/addon')x(v)
outer apply x.v.nodes('./id')t(u)

output
---------------------------
iid	qty	ipr	i
---------------------------
1	2	0	1
1	2	0	2
1	2	0	3
3	1	0	NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-06 : 03:21:51

Thanks it saved my day..!!
Still trying to understand how you did it..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 04:23:15

welcome
just determine level at which you need to iterate. write xquery for other nodes with reference to that. in your case level you need to iterate is '/order/item1d/addon' and most of other nodes are in its parent level hence the ../

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources