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:39 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

Post #1459273
Posted Tuesday, June 4, 2013 1:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
Duplicate post. Please direct all your replies here.
http://www.sqlservercentral.com/Forums/Topic1459272-3077-1.aspx



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1459576
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse