December 9, 2014 at 5:14 am
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
December 9, 2014 at 8:12 am
Quick solution using the nodes method
😎
USE tempdb;
GO
SET NOCOUNT ON;
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>';
SELECT
R.DATA.value('@id','INT') AS R_ID
,WF.DATA.value('@id','INT') AS WF_ID
,WFS.DATA.value('@id','INT') AS WFS_ID
,WFS.DATA.value('fname[1]','VARCHAR(25)') AS FNAME
FROM @MyXML.nodes('RS/R') AS R(DATA)
OUTER APPLY R.DATA.nodes('WF') AS WF(DATA)
OUTER APPLY WF.DATA.nodes('WFS') AS WFS(DATA) ;
Results
R_ID WF_ID WFS_ID FNAME
----------- ----------- ----------- ------------
6330 71445 12790 John12790
6330 71445 12791 Eric12791
6331 71446 12793 John12793
6331 71446 12794 Eric12794
December 9, 2014 at 10:52 pm
Million of thanks . It worked for me. 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply