Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to shred xml column into multiple rows of data Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 4:24 AM
Points: 9, Visits: 70
I have a table with thousands of rows and a large xml blob containing lots of elements. I need to shred all the elements into a table. However, I have multiple elements in the same root like so:


ID xml
1 <root>
<name1 row = 1 value ="1" />
<name2 row = 1 value ="2" />
<name1 row = 2 value ="3" />
<name2 row = 2 value ="4" />
<name1 row = 3 value ="5" />
<name2 row = 3 value ="6" />
<name1 row = 4 value ="7" />
<name2 row = 4 value ="8"/>
<name1 row = 5 value ="9"/>
<name2 row = 5 value ="10"/>
</root>


So I want to shred all the rows like so:

ID Row Name1 Name2
1 1 1 2
1 2 3 4
1 3 5 6
1 4 7 8
1 5 9 10

CREATE TABLE #TEMP (ID int, XMLStuff xml)
INSERT INTO #TEMP
SELECT 1, '<dynoformdata><incidentdate row="0" value="2013/01/13" /><totalretail row="0" value="27.25"/><incidentdate row="1" value="2013/01/14" /><totalretail row="1" value="10.75"/></dynoformdata>'
UNION
SELECT 2, '<dynoformdata><incidentdate row="0" value="2013/01/14" /><totalretail row="0" value="17.05"/><incidentdate row="1" value="2013/02/15" /><totalretail row="1" value="11.75"/></dynoformdata>'
UNION
SELECT 3,'<dynoformdata><incidentdate row="0" value="2013/01/15" /><totalretail row="0" value="17.05"/><incidentdate row="1" value="2013/02/16" /><totalretail row="1" value="8.75"/></dynoformdata>'

SELECT *
FROM #TEMP

I want the results to look something like this:
Post #1459272
Posted Monday, June 3, 2013 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 1,683, Visits: 19,617
Try this


DECLARE @t TABLE(ID INT, X XML)
INSERT INTO @T(ID,X)
VALUES(1,'<root>
<name1 row = "1" value ="1" />
<name2 row = "1" value ="2" />
<name1 row = "2" value ="3" />
<name2 row = "2" value ="4" />
<name1 row = "3" value ="5" />
<name2 row = "3" value ="6" />
<name1 row = "4" value ="7" />
<name2 row = "4" value ="8"/>
<name1 row = "5" value ="9"/>
<name2 row = "5" value ="10"/>
</root>');

WITH CTE AS (
SELECT ID,
x.r.value('local-name(.)','VARCHAR(10)') AS name,
x.r.value('@row','INT') AS Row,
x.r.value('@value','INT') AS value
FROM @t
CROSS APPLY X.nodes('/root/*') AS x(r))
SELECT ID,
Row,
MAX(CASE WHEN name='name1' THEN value END) AS Name1,
MAX(CASE WHEN name='name2' THEN value END) AS Name2
FROM CTE
GROUP BY ID,Row
ORDER BY ID,Row;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1459277
Posted Monday, June 3, 2013 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 4:24 AM
Points: 9, Visits: 70
Thank you very much
Post #1459321
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse