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

• Paresh Dehadray

  Mr or Mrs. 500

  Points: 534

  More actions

  December 25, 2012 at 1:31 am

  #286526

  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

• kupy

  SSCommitted

  Points: 1652

  More actions

  December 25, 2012 at 3:38 am

  #1571405

  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)

• Paresh Dehadray

  Mr or Mrs. 500

  Points: 534

  More actions

  December 25, 2012 at 4:11 am

  #1571407

  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