Convert XML into SQL server table

Question

Here is my XML Format:

<ROOT>
<Orders>
    <OrderID>423</OrderID>
    <ProductID>54</ProductID>
    <ProductID>23</ProductID>
</Orders>
<Orders>
    <OrderID>523</OrderID>
    <ProductID>5</ProductID>
    <ProductID>26</ProductID>
</Orders>

I want to have my output in the below format

OrderID         ProductID   
423             54  
423             23
523             5
523             26

I found ways that help me read attribute from XML and convert it to SQL table. But didn't find ways or solution for my XML input. Any help is appreciated. Thanks.

What's stopping you from adapting it to work with your XML format? — byxor
– byxor, Commented Sep 7, 2016 at 15:47
Hi jordan28, is this issue solved or do you need further help? — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 7, 2016 at 20:40
Thanks Shnugo, that was really helpful. Yes the issue is solved. Cheers! — jordan28
– jordan28, Commented Sep 8, 2016 at 9:37
I upvoted your solution but it showed a message. I was not aware of the tick sign. Thanks again for making me aware :) — jordan28
– jordan28, Commented Sep 14, 2016 at 7:31

Gottfried Lesigang · Accepted Answer · 2016-09-07 15:47:11Z

Try it like this

DECLARE @xml XML=
'<ROOT>
<Orders>
    <OrderID>423</OrderID>
    <ProductID>54</ProductID>
    <ProductID>23</ProductID>
</Orders>
<Orders>
    <OrderID>523</OrderID>
    <ProductID>5</ProductID>
    <ProductID>26</ProductID>
</Orders>
</ROOT>';

SELECT  Ord.value('OrderID[1]','int') AS OrderID
       ,Prod.value('.','int') AS ProductID
FROM @xml.nodes('/ROOT/Orders') AS A(Ord)
CROSS APPLY A.Ord.nodes('ProductID') AS B(Prod)

As your leading value is the ProductID you need a nodes()-call for this. It was possible to use only one .nodes('/ROOT/Orders/ProductID') and find the corresponding OrderID with a backward move:

SELECT  Prod.value('(../OrderID)[1]','int') AS OrderID
       ,Prod.value('.','int') AS ProductID
FROM @xml.nodes('/ROOT/Orders/ProductID') AS  B(Prod)

But it is cleaner and better performing to move down the tree step by step...

Collectives™ on Stack Overflow

Convert XML into SQL server table

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