Cross Apply to get child parent value from Xml in SQL Server

Question

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?

score 11 · Answer 1 · answered Dec 25 '12 at 10:06

11

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)

answered Dec 25 '12 at 10:06

marc_s

732,580
175
1,330
1,459

2

@Paresh: if this answer helped you solve you problem, you should [**accept this answer**](http://meta.stackexchange.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Dec 25 '12 at 10:54
3

Very helpful! The ../@value suggestion helped to reduce CROSS'es :). – Developer Marius Žilėnas May 19 '15 at 10:01

score 3 · Answer 2 · edited Dec 15 '16 at 12:36

3

Using CROSS APPLY is more efficient. Look at the execution plan, Query cost is just 16% when CROSS APPLY is used and 84% when no CROSS APPLY is not used. Here is my solution that uses CROSS APPLY :

select 
T.c.value('@value[1]','varchar(100)') as 'Country'
,T2.col.value('@value[1]','varchar(100)') as 'Col2'
,T2.col.value('data(.)','varchar(100)') as 'Col3'
from 
@x.nodes('/root/row') T(c)       
CROSS APPLY T.c.nodes('col')  as T2(col)

edited Dec 15 '16 at 12:36

Luke Girvin

13,221
9
64
84

answered Sep 05 '14 at 15:45

Susana Maniscalco

31
2

1

The query cost is just an estimate and when it comes to XML queries it is a really bad estimate. But using the parent axis as in the other answer is carastrophic for performance. Using cross apply is the way to do this. +1. – Mikael Eriksson Sep 05 '14 at 17:24

Cross Apply to get child parent value from Xml in SQL Server

2 Answers2