I have the 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 the 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?
Try this:
select
ParentValue = c.value('(../@value)[1]', 'varchar(100)'),
ValueAttr = c.value('@value','varchar(100)'),
ColValue = c.value('.','varchar(100)')
from
@x.nodes('/root/row/col') as m(c)
Basically, there's really no need to use CROSS APPLY
at all - just select the /root/row/col
nodes from the .nodes()
call, and use ../@value
to get the value
attribute on the parent node (the <row>
element)