how to convert xml input to a single table in sp

Question

Post reply

how to convert xml input to a single table in sp

ASFSDJG

Mr or Mrs. 500

Points: 500
More actions
April 9, 2011 at 2:25 am

#238173

<root>
<product>
<product_id ID = "1"/>
<product_id ID = "2"/>
<product_id ID = "3"/>
</product>
<discount>
<discount_id ID ="9"/>
<discount_id ID ="10"/>
</discount>
<tax>
<tax_id ID = "0"/>
</tax>
</root>
this is my xml input to a stored procedure.
is it possible to convert this xml to a single table like this
Product_ID Discount_ID Tax_ID
1 9 0
1 10 0
2 9 0
2 10 0
3 9 0
3 10 0
and if possible can you please give me the solution

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Yes, it's possible. I think, XQuery together with CrossApply is the solution here.

You might want to have a look at Jacob Sebastians blog.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

DECLARE @x XML

SET @x='<root>

<product>

<product_id ID = "1"/>

<product_id ID = "2"/>

<product_id ID = "3"/>

</product>

<discount>

<discount_id ID ="9"/>

<discount_id ID ="10"/>

</discount>

<tax>

<tax_id ID = "0"/>

</tax>

</root>

';

SELECT p.value('@ID','INT') AS Product_ID,

d.value('@ID','INT') AS Discount_ID,

t.value('@ID','INT') AS Tax_ID

FROM @x.nodes('/root/product/product_id') AS pr(p)

CROSS JOIN @x.nodes('/root/discount/discount_id') AS dr(d)

CROSS JOIN @x.nodes('/root/tax/tax_id') AS tr(t)

ORDER BY 1,2;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ASFSDJG Mr or Mrs. 500 Points: 500 More actions · Answer 3

ASFSDJG

Mr or Mrs. 500

Points: 500

April 10, 2011 at 11:26 pm

#1309657

Thanks a lot !!!