How to iterate an XML field in SQL Server using the value() function

Question

I have the following XML field():

<A>
  <B name="B1">
    <C>
      <D name="D1">
        <E name="E1"/>
        <E name="E2"/>
        <E name="E3"/>
      </D>
      <D name="D2">
        <E name="E4"/>
        <E name="E5"/>
        <E name="E6"/>
      </D>
    </C>
  </B>
  <B name="B3">
    <C>
      <D name="D11">
        <E name="E11"/>
        <E name="E22"/>
        <E name="E33"/>
      </D>
      <D name="D22">
        <E name="E44"/>
        <E name="E55"/>
        <E name="E66"/>
      </D>
    </C>
  </B>
</A>

If I want to use nodes() and value() to get the data from XML, what should I do, the result should be like:

B	D	E
B1	D1	E1
B1	D1	E2
B1	D1	E3
B2	D2	E4
..
B3	D22	E66

I have tried the following query:

SELECT NodePath.value('@name', 'varchar(100)') B,
    NodePath.value('(./C/D/@name)[1]', 'varchar(64)') D,
    NodePath.value('(./C/D/E/@name)[1]', 'varchar(100)') E
FROM XmlTable xt
cross apply xt.XmlField.nodes('/A/B') Node(NodePath)

but I seems like that it only takes one E from different B

Answer 1

SELECT NodePath.value('@name', 'varchar(100)') B,
    --NodePath.value('(./C/D/@name)[1]', 'varchar(64)') D,
    --NodePath.value('(./C/D/E/@name)[1]', 'varchar(100)') E,
    d.d.value('@name', 'varchar(64)') AS D,
    e.e.value('@name', 'varchar(100)') AS E
FROM XmlTable xt
CROSS APPLY xt.XmlField.nodes('/A/B') AS Node(NodePath)
CROSS APPLY Node.NodePath.nodes('C/D') AS d(d)
CROSS APPLY d.d.nodes('E') AS e(e)