计算SQL Server中每个特定xml节点的级别数

Max 发表于 Dev

最高

我正在尝试确定SQL Server的xml文件中所有实体标签的节点级别：

<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>

我需要这样返回的数据：

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

Shnugo

我还建议一种递归方法，但是一种通用方法。这将遍历XML逐节点遍历（当然不是attribute @Name，这仅对您的XML有效）。

注意：有些事情可能需要处理名称空间：

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;

该Step会告诉你，你正在寻找的答案。当元素是最终叶节点时，子节点的计数将为零。

结果

+------+-------------------------------------------+---------+---------------+
| 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             |
+------+-------------------------------------------+---------+---------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。