OpenXml with Different level childnodes SQL

Question

1.I have XML data like below, Child3 may or may not have Child4.If Child4 is there, another column should get added.

    <Parent name="ABC">
         <Child name="123">
            <Child1 text="child123" />
            <Child1 text="Child345" />    
            <Child1 text="Status">
              <Child2 text="Pending">
                <Child3 text="Excise" />
                <Child3 text="Financial" />        
              </Child2 >
              <Child2 text="Over">
                <Child3 text="A1111" />
              </Child2 >
            </Child1 >
          </Child >
        </Parent `>

I want a resultset like:

        ABC 123 child123    null      null``
        ABC 123 Child345    null      null      
        ABC 123 Status      Pending   Excise
        ABC 123 Status      Pending   Financial
        ABC 123 Status      Over      A1111

I wrote a query like this:

        SELECT Parent ,Child, Child1, Child2, Child3
        FROM OPENXML(@hDoc, '/Parent /Child /Child1 /Child2 /Child3 ',2)
        WITH 
        (
        ReleaseInfo [varchar](max) '../../../../@name',
        L1Data [varchar](max) '../../../@name',
        L2Data [varchar](max) '../../@text',`
        L3Data [varchar](max) '../@text',
        L4Data [varchar](max) '@text')

i am not getting the first two records. Please suggest.

score 1 · Accepted Answer · answered Dec 10 '13 at 07:05

declare @XML xml = '
<Parent name="ABC">
  <Child name="123">
    <Child1 text="child123" />
    <Child1 text="Child345" />
    <Child1 text="Status">
      <Child2 text="Pending">
        <Child3 text="Excise" />
        <Child3 text="Financial" />
      </Child2>
      <Child2 text="Over">
        <Child3 text="A1111" />
      </Child2>
    </Child1>
  </Child>
</Parent>'

select P.X.value('@name', 'varchar(max)') as Parent,
       C.X.value('@name', 'varchar(max)') as Child,
       C1.X.value('@text', 'varchar(max)') as Child1,
       C2.X.value('@text', 'varchar(max)') as Child2,
       C3.X.value('@text', 'varchar(max)') as Child3
from @xml.nodes('/Parent') as P(X)
  outer apply P.X.nodes('Child') as C(X)
  outer apply C.X.nodes('Child1') as C1(X)
  outer apply C1.X.nodes('Child2') as C2(X)
  outer apply C2.X.nodes('Child3') as C3(X)

OpenXml with Different level childnodes SQL

1 Answers1