shreding xml column
- by csetzkorn
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
