Using MSSQL 2008 and XQUERY
Consider the following XML stored in a table:
<ROOT>
<WrapperElement>
<ParentElement ID=1>
<Title>parent1</Title>
<Description />
<ChildElement ID="6">
<Title>Child 4</Title>
<Description />
<StartDate>2010-01-25T00:00:00</StartDate>
<EndDate>2010-01-25T00:00:00</EndDate>
</ChildElement>
<ChildElement ID="0">
<Title>Child1</Title>
<Description />
<StartDate>2010-01-25T00:00:00</StartDate>
<EndDate>2010-01-25T00:00:00</EndDate>
</ChildElement>
<ChildElement ID="8">
<Title>Child6</Title>
<Description />
<StartDate>2010-01-25T00:00:00</StartDate>
<EndDate>2010-01-25T00:00:00</EndDate>
</ChildElement>
</ParentElement>
</WrapperElement>
</Root>
I want to decompose this xml into something like
PE!ID | PE!Title | PE!Description | CE!ID | CE!Title | CE!StartDate |...
1 | parent1 | | 6 | child 4 | 2010-... |
1 | parent1 | | 0 | child1 | 2010-... |
etc.
Note: there may be many ChildElements per ParentElement, in this example. I've been experimenting with xquery however i've not been able to navigate through complex elements as such.
Basically, i'm trying to do the exact opposite of what FOR XML does to a table, only with a much more simplistic set of data to work with.
Any ideas on where to go next or how to accomplish this?
Thanks