squvi.87 (5/9/2016)
Hi There,I require to convert single xml into multiple rows, as explained below
Thanks in advance
In the future try to include the actual XML (or table data) instead of a screenshot as it's easier for us to provide a good answer. Also, try to explain where the XML (or data) is coming from; this information is also helpful for providing a better answer. That said, here's how you would do this against a table or a variable. Note my comments:
-----------------------------------------------------------------------------------------
-- if the xml is coming in as a variable or parameter
-----------------------------------------------------------------------------------------
-- sample XML
DECLARE @xml XML =
'<consumerTransactionList>
<consumerTransaction>
<sourceSystem>
<id>1</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
<consumerTransaction>
<sourceSystem>
<id>2</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
</consumerTransactionList>';
-- solution
SELECT
CAST
('<consumerTransactionList>'+
CAST(nd.r.query('.') AS varchar(8000))+
'</consumerTransactionList>'
AS XML)
FROM (VALUES (@xml)) doc(r)
CROSS APPLY r.nodes('/consumerTransactionList/consumerTransaction') nd(r);
-----------------------------------------------------------------------------------------
-- if the xml is coming from a table
-----------------------------------------------------------------------------------------
-- create and populate the sample table
IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable;
SELECT *
INTO #SomeTable
FROM
(
SELECT id = 100, SomeXML = CAST
('<consumerTransactionList>
<consumerTransaction>
<sourceSystem>
<id>1</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
<consumerTransaction>
<sourceSystem>
<id>2</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
</consumerTransactionList>' AS xml)
UNION ALL
SELECT id = 101, CAST
('<consumerTransactionList>
<consumerTransaction>
<sourceSystem>
<id>3</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
<consumerTransaction>
<sourceSystem>
<id>4</id>
<moreStuff>blah blah blah</moreStuff>
</sourceSystem>
</consumerTransaction>
</consumerTransactionList>' AS xml)
) x
GO
SELECT
id,
CAST
('<consumerTransactionList>'+
CAST(nd.r.query('.') AS varchar(8000))+
'</consumerTransactionList>'
AS XML)
FROM #SomeTable st
CROSS APPLY st.SomeXML.nodes('/consumerTransactionList/consumerTransaction') nd(r);
DROP TABLE #SomeTable;
A good links for learning XML: http://www.w3schools.com/xml/[/url]
An important understanding how my solution works:
https://msdn.microsoft.com/en-us/library/ms191474.aspx
A good SQL XML link: https://msdn.microsoft.com/en-us/library/ms187339.aspx
-- Itzik Ben-Gan 2001