How to convert nested xml hierarchy to sql table

Question

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-...  |

and etc.

Note. In this example, there can be many ChildElements for a ParentElement. I experimented with xquery, however I was not able to navigate the complex elements as such.

Basically, I'm trying to make the exact opposite of what FOR XML does for a table, only with a much more simplified dataset to work with.

Any ideas on where to go next or how to do it?

thank

Answer 1

( @input XML XML- - ):

SELECT
    Parent.Elm.value('(@ID)[1]', 'int') AS 'ID',
    Parent.Elm.value('(Title)[1]', 'varchar(100)') AS 'Title',
    Parent.Elm.value('(Description)[1]', 'varchar(100)') AS 'Description',
    Child.Elm.value('(@ID)[1]', 'int') AS 'ChildID',
    Child.Elm.value('(Title)[1]', 'varchar(100)') AS 'ChildTitle',
    Child.Elm.value('(StartDate)[1]', 'DATETIME') AS 'StartDate',
    Child.Elm.value('(EndDate)[1]', 'DATETIME') AS 'EndDate'
FROM
    @input.nodes('/ROOT/WrapperElement/ParentElement') AS Parent(Elm)
CROSS APPLY
    Parent.Elm.nodes('ChildElement') AS Child(Elm)

/ROOT/WrapperElement/ParentElemet ( Parent(Elm) -), CROSS APPLY , ParentElement, .

- !