Splitting an XML value into multiple rows

Question

Splitting an XML value into multiple rows

shogunSQL

Say Hey Kid

Points: 689
More actions
June 16, 2016 at 12:10 pm

#311191

Hi, I really need your help with splitting an XML value into multiple rows by the top most element. Please HELP. Thank you so much!
<Parent>
<Child01>abc</Child01>
</Parent>
<Parent>
<Child02>edf</Child02>
</Parent>
<Parent>
<Child03>ghi</Child03>
</Parent>
<Parent>
<Child04>klm</Child04>
</Parent>
I have this one row in an XML colomn in my SQL table and I need to split each Parent into multiple rows. For example above, the result should come out as 4 rows as something like this:
Row 1 = <Parent><Child01>abc</Child01></Parent>
Row 2 = <Parent><Child02>edf</Child02></Parent>
Row 3 = <Parent><Child03>ghi</Child03></Parent>
Row 4 = <Parent><Child04>klm</Child04></Parent>

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Check the code and explanation for the delimited splitter in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Be aware that it's limited in length, so you need to be sure that you're not truncating anything.

Here's an example of its usage:

CREATE TABLE #Test(

SomeXml Xml);

INSERT INTO #Test VALUES('<Parent>

<Child01>abc</Child01>

</Parent>

<Parent>

<Child02>edf</Child02>

</Parent>

<Parent>

<Child03>ghi</Child03>

</Parent>

<Parent>

<Child04>klm</Child04>

</Parent>');

SELECT s.*

FROM #Test

CROSS APPLY( SELECT STUFF(REPLACE(CAST(SomeXml AS nvarchar(4000)), N'<Parent>', NCHAR(7) + '<Parent>'), 1, 1, ''))x(DelString)

CROSS APPLY dbo.DelimitedSplitN4K( x.DelString, NCHAR(7))s;

GO

DROP TABLE #Test;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

shogunSQL Say Hey Kid Points: 689 More actions · Answer 2

Thank you so much. You are very good. You reminded me about this function I used before. Iteeaked it a bit to nvarchar(max) because my xml file is really huge. It takes more than an hour to run and It hasn't complete yet. Will let you know how it goes. Crossing my fingers!!

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

Another way...

SELECT

ROW_NUMBER() OVER (ORDER BY x.value('.','varchar(100)')), -- only if you want a row number

x.query('.')

FROM #Test t

CROSS APPLY t.SomeXml.nodes('/Parent') a(x);

"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