single xml to multiple rows

Question

Post reply

single xml to multiple rows

squvi.87

Default port

Points: 1450
More actions
May 9, 2016 at 3:20 am

#326913

Hi There,
I require to convert single xml into multiple rows, as explained below
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What this task has to do with SQL?

_____________
Code for TallyGenerator

squvi.87 Default port Points: 1450 More actions · Answer 2

squvi.87

Default port

Points: 1450

May 9, 2016 at 6:25 am

#1877449

yes

drew.allen SSC Guru Points: 76985 More actions · Answer 3

You should be able to figure this out from the answers that you received to your post on XML to SQL tables

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

squvi.87 Default port Points: 1450 More actions · Answer 4

can anybody suggest me some links to learn processing the xml in sql ?

drew.allen SSC Guru Points: 76985 More actions · Answer 5

squvi.87 (5/9/2016)
can anybody suggest me some links to learn processing the xml in sql ?

SQL XML

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

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

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001