shreding xml column

Question

Hi,

I have a XML column which contains XML like this:

<Set>
    <Element>
        <ID>
            1
        </ID>
<List>
    <ListElement>
        <Part1>
            ListElement 1
        </Part1>
    </ListElement>
    <ListElement>
        <Part1>
            ListElement2
        </Part1>
    </ListElement>
</List>
    </Element>
    <Element>
        <ID>
            2
        </ID>
<List>
    <ListElement>
        <Part1>
            ListElement3
        </Part1>
    </ListElement>
    <ListElement>
        <Part1>
            ListElement4
        </Part1>
    </ListElement>
</List>
    </Element>
</Set>

I would like to shred this into a relation table containing this:

ID, ListElement
1, ListElement1
1, ListElement2
2, ListElement3
2, ListElement4

I am able to obtain the content of the Parts using something like this:

select      
    List.value('(Part1/text())[1]', 'varchar(max)') as test
from 
    Table CROSS APPLY 
      xml.nodes('// Element/List/ListElement') AS List(List)

but I have not yet achieved to keep the ‘foreign key’ (the ID value).

Thanks.

Best wishes,

Christian

Answer 1

Try this instead of your query:

SELECT      
    List.value('(../../ID)[1]', 'int') AS 'ID',
    List.value('(Part1/text())[1]', 'varchar(max)') as test
FROM
    dbo.Table 
CROSS APPLY 
    xml.nodes('/Set/Element/List/ListElement') AS List(List)

Updated again - now back to only one CROSS APPLY, reaching back up into the grand parent's "ID" element using ../../ID in XPath. Also eliminated the //Element XPath in favour of /Set/Element (the //Element xpaths are notoriously slow)

Answer 2

In case anyone wants to do this with XQuery:

<Table>
   {for $listElement in $table//ListElement
    return
      <Row>
        {$listElement/../../ID}
        {$listElement/Part1}
      </Row>    
   }
</Table>

where $table is the original XML

returns

<Table>
    <Row>
        <ID> 1 </ID>
        <Part1> ListElement 1 </Part1>
    </Row>
    <Row>
        <ID> 1 </ID>
        <Part1> ListElement2 </Part1>
    </Row>
    <Row>
        <ID> 2 </ID>
        <Part1> ListElement3 </Part1>
    </Row>
    <Row>
        <ID> 2 </ID>
        <Part1> ListElement4 </Part1>
    </Row>
</Table>