Count number of level of each specific xml node in SQL Server

Question

I am trying to determine the level of nodes for all entity tags in my xml file in SQL Server:

<root>
<Entities>
<Entity Name="E1">
    <Entity Name="E11">
        <Entity Name="E12">
            <Entity Name="E121"/>
            <Entity Name="E122"/>
            <Entity Name="E123"/>
        </Entity>
        <Entity Name="E13"/>
    </Entity>
</Entity>
<Entity Name="E2">
    <Entity Name="E22"/>
</Entity>
</Entities>
</root>

I need data returned like this:

Name  Level
-----------
E1    1
E11   2
E12   3
E121  4
E122  4
E121  4
E13   3
E2    1
E22   2

Answer 1

I'd also suggest a recursive approach, but a generic one. This will travers down the XML node-by-node generically (Of course not the attribute @Name, this is valid just for your XML).

Attention: Some things might need the handling of namespaces:

DECLARE @xml XML=
N'<root>
<Entities>
<Entity Name="E1">
    <Entity Name="E11">
        <Entity Name="E12">
            <Entity Name="E121"/>
            <Entity Name="E122"/>
            <Entity Name="E123"/>
        </Entity>
        <Entity Name="E13"/>
    </Entity>
</Entity>
<Entity Name="E2">
    <Entity Name="E22"/>
</Entity>
</Entities>
</root>';

WITH cte AS
(
    SELECT 1 AS Step 
          ,a.value('local-name(.)','nvarchar(max)') AS ElementPath
          ,a.value('@Name','nvarchar(max)') AS Content
          ,a.query('./*') AS TheNode
    FROM @xml.nodes('/*') A(a)
    UNION ALL
    SELECT cte.Step +1 
          ,cte.ElementPath + '/' + a.value('local-name(.)','nvarchar(max)') 
          ,a.value('@Name','nvarchar(max)')
          ,a.query('./*')
    FROM cte
    CROSS APPLY TheNode.nodes('*') A(a)
)
SELECT *
      ,TheNode.value('count(//*)','int') CountSubNodes
FROM cte
ORDER BY Content;

The Step will tell you the answer you are looking for. The count of sub-nodes will be zero when the element is a final leaf-node.

The result

+------+-------------------------------------------+---------+---------------+
| Step | ElementPath                               | Content | CountSubNodes |
+------+-------------------------------------------+---------+---------------+
| 1    | root                                      | NULL    | 10            |
+------+-------------------------------------------+---------+---------------+
| 2    | root/Entities                             | NULL    | 9             |
+------+-------------------------------------------+---------+---------------+
| 3    | root/Entities/Entity                      | E1      | 6             |
+------+-------------------------------------------+---------+---------------+
| 4    | root/Entities/Entity/Entity               | E11     | 5             |
+------+-------------------------------------------+---------+---------------+
| 5    | root/Entities/Entity/Entity/Entity        | E12     | 3             |
+------+-------------------------------------------+---------+---------------+
| 6    | root/Entities/Entity/Entity/Entity/Entity | E121    | 0             |
+------+-------------------------------------------+---------+---------------+
| 6    | root/Entities/Entity/Entity/Entity/Entity | E122    | 0             |
+------+-------------------------------------------+---------+---------------+
| 6    | root/Entities/Entity/Entity/Entity/Entity | E123    | 0             |
+------+-------------------------------------------+---------+---------------+
| 5    | root/Entities/Entity/Entity/Entity        | E13     | 0             |
+------+-------------------------------------------+---------+---------------+
| 3    | root/Entities/Entity                      | E2      | 1             |
+------+-------------------------------------------+---------+---------------+
| 4    | root/Entities/Entity/Entity               | E22     | 0             |
+------+-------------------------------------------+---------+---------------+