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

The example XML is not valid. Closing tags should use the / character, not the "\" character. e.g.: <D>...</D>. — AlwaysLearning
– AlwaysLearning, Commented Dec 22, 2020 at 7:44

lptr · Accepted Answer · 2020-12-22 08:19:06Z

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)

answered Dec 22, 2020 at 8:19

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zening.chen Over a year ago

Thank you, It works! And I am also wondering its performance. Will its performance better than the following query?

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

lptr Over a year ago

[email protected] .In general,navigating up the xml structure/hierarchy(from a child to parent node) using parent:: or the '../' abbreviation is more expensive/costly than retrieving the parent first and then the children.You could compare&check the differences of the two execution plans(going up the hierarchy requires 2 additional xpath filters). This does not mean that your suggestion will be slower. A more expensive query gets more memory granted and sometimes this could make the expensive query run faster than a cheaper one.Just test it with your environment and pick the one that suits you.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related