Insert XML ( having multiple childs and attributes) into table ( Please help )
-
Hey guys, please help me
My xml is in below format:
DECLARE @MyXML XML
SET @MyXML = '<RS>
<R id="6330">
<WF id="71445">
<WFS id="12790"> <fname>John12790</fname> </WFS>
<WFS id="12791"> <fname>Eric12791</fname> </WFS>
</WF>
</R>
<R id="6331">
<WF id="71446">
<WFS id="12793"> <fname>John12793</fname> </WFS>
<WFS id="12794"> <fname>Eric12794</fname> </WFS>
</WF>
</R>
</RS>'
I need to insert into table with TSQL query:
My output should be similar below:
John12790 12790 71445 6330
Eric 12791 12790 71445 6330
John12793 12793 71446 6331
Eric12794 12794 71446 6331
otherwise i need to write huge c# code to execute such thing.. is it possible or i am going in wrong direction
-
Please help me guys
-
SELECT x5.r5.value('.','VARCHAR(10)'),x4.r4.value('@id','INT'),
x3.r3.value('@id','INT'),
x2.r2.value('@id','INT')
FROM @MyXML.nodes('/RS') AS x1(r1)
CROSS APPLY x1.r1.nodes('R') AS x2(r2)
CROSS APPLY x2.r2.nodes('WF') AS x3(r3)
CROSS APPLY x3.r3.nodes('WFS') AS x4(r4)
CROSS APPLY x4.r4.nodes('fname') AS x5(r5)
____________________________________________________
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply