June 3, 2013 at 7:39 am
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
June 4, 2013 at 1:23 am
Duplicate post. Please direct all your replies here.
http://www.sqlservercentral.com/Forums/Topic1459272-3077-1.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply