CODEWITHSUNDEEP
sqlsql-server-2008xquery
Beta033
Beta033

Reputation: 2013

How to convert nested hierarchy of xml to sql table

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

Upvotes: 4

Views: 3704

Answers (1)

marc_s
marc_s

Reputation: 754538

How about this (I declared @input to be a XML datatype variable with your XML content - replace accordingly):

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)

You basically iterate over all the /ROOT/WrapperElement/ParentElemet nodes (as Parent(Elm) pseudo table), and for each of those entries, you then do a CROSS APPLY for the child elements contained inside that ParentElement and pluck out the necessary information.

Should work - I hope!

Upvotes: 7

Related Questions