How to shred xml column into multiple rows of data

  • 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

  • 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/

Viewing 2 posts - 1 through 1 (of 1 total)

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