SQL: How to get multiple records from xml column

Question

I have an xml which looks like this. <name_row> nodes has the column names and <value_row> has all the values. How do I cross join?

Declare @myXML xml
SET @myXML = '<root>
 <name_row>
  <column>  
     <name>CustomerID</name>
   </column>
     <column>  
     <name>ProductID</name>
   </column>
   <column>  
     <name>Price</name>
   </column>
</name_row>
<value_row>  
   <column>  
     <value>123</value>
   </column>
    <column>  
     <value>101</value>
   </column> 
    <column>  
     <value>$12.00</value>
   </column>      
</value_row>
<value_row>  
   <column>  
     <value>123</value>
   </column>
    <column>  
     <value>102</value>
   </column> 
    <column>  
     <value>$15.00</value>
   </column>      
</value_row>
</root>'

I need to save each values in a different column fields.

select x.i.value('(./text()[1]', 'varchar(max)')
from @myXML x 
cross apply x.nodes('/root/value_row/column/value') as x(i)

Thanks

Alexander Petrov · Accepted Answer · 2016-05-13 17:49:26Z

1

Try something like

select v.value('(column/value)[1]', 'varchar(max)') as CustomerID
     , v.value('(column/value)[2]', 'varchar(max)') as ProductID
     , v.value('(column/value)[3]', 'varchar(max)') as Price
into #tempTable
from @myXML.nodes('root/value_row') t(v)

select * from #tempTable

drop table #tempTable

answered May 13, 2016 at 17:49

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: How to get multiple records from xml column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related