Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cross Apply to get child parent value from Xml in SQL Server Expand / Collapse
Author
Message
Posted Tuesday, December 25, 2012 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 11:46 PM
Points: 6, Visits: 28
I have following xml:
<root>
<row value="US">
<col value="00">Jon</col>
<col value="01">David</col>
<col value="02">Mike</col>
<col value="03">Nil</col>
</row>
<row value="Canada">
<col value="C1">Pollard</col>
</row>
<row value="Japan">
<col value="J1">Yin</col>
<col value="J2">Li</col>
</row>
<row value="India">
<col value="MP">Ram</col>
<col value="UP">Paresh</col>
<col value="AP">Mohan</col>
</row>
</root>


and I want following output by using SQL Server query:

US 00 Jon
US 01 David
US 02 Mike
US 03 Nil
Canada C1 Pollard
Japan J1 Yin
Japan J2 Li
India MP Ram
India UP Paresh
India AP Mohan


I am using following SQL query:


declare @x xml
set @x =
'<root>
<row value="US">
<col value="00">Jon</col>
<col value="01">David</col>
<col value="02">Mike</col>
<col value="03">Nil</col>
</row>
<row value="Canada">
<col value="C1">Pollard</col>
</row>
<row value="Japan">
<col value="J1">Yin</col>
<col value="J2">Li</col>
</row>
<row value="India">
<col value="MP">Ram</col>
<col value="UP">Paresh</col>
<col value="AP">Mohan</col>
</row>
</root>'

select r.value('@value','varchar(100)'),r.value('.','varchar(100)')
from @x.nodes('root') as m(c)
cross apply m.c.nodes('row/col') as x(r)


I am unable to get first column which contains the value of parent row. Can you please suggest what changes I can make to get first column value?

Thanks in advance

Paresh
Post #1400040
Posted Tuesday, December 25, 2012 3:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:44 AM
Points: 995, Visits: 2,574
Hi,

I was just paling around with the query and got this:
declare @x xml
set @x =
'<root>
<row value="US">
<col value="00">Jon</col>
<col value="01">David</col>
<col value="02">Mike</col>
<col value="03">Nil</col>
</row>
<row value="Canada">
<col value="C1">Pollard</col>
</row>
<row value="Japan">
<col value="J1">Yin</col>
<col value="J2">Li</col>
</row>
<row value="India">
<col value="MP">Ram</col>
<col value="UP">Paresh</col>
<col value="AP">Mohan</col>
</row>
</root>'

select v.value('../@value','varchar(100)'), v.value('@value','varchar(100)'), v.value('.[1]','varchar(100)')
from @x.nodes('root') as m(c)
cross apply m.c.nodes('row/col') as z(v)

Post #1400042
Posted Tuesday, December 25, 2012 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 11:46 PM
Points: 6, Visits: 28
Thanks it is working. I also got below another way:

select c.value('(../@value)[1]', 'varchar(100)'),c.value('@value','varchar(100)'), c.value('.','varchar(100)')
from @x.nodes('/root/row/col') as m(c)

Thanks
Paresh
Post #1400044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse