TSQL提取XML节点路径时子节点顺序异常问题排查

问题原因 #

你遇到的二级节点子节点顺序颠倒问题，核心是递归CTE未指定显式排序规则——SQL Server对递归CTE的结果集默认不保证遵循原始XML文档的节点顺序。

虽然nodes('/*')方法会按XML文档中的顺序返回Customer1、Customer2，但在递归遍历的下一层，SQL Server处理Xml_CTE中行的顺序没有强制约束，导致Customer2的子节点被优先遍历并输出。

解决方案 #

在递归CTE中添加两个辅助字段：记录节点层级的depth，以及节点在父节点中位置的position，最终通过这两个字段排序，就能严格匹配XML文档中的节点顺序。

修改后的完整代码：

DECLARE @xml xml = 
N'<General>
  <Customer1>
    <model_year1>2011</model_year1>
    <vehicle_id>1</vehicle_id>
    <mileage>34000</mileage>
  </Customer1>
  <Customer2>
    <model_year1>2012</model_year1>
    <vehicle_id>2</vehicle_id>
    <mileage>35000</mileage>
  </Customer2>
</General>';

WITH Xml_CTE AS
(
    SELECT
       CAST('/' + node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)) AS nodes_name,
       node.query('*') AS children,
       1 AS depth,
       1 AS position
    FROM @xml.nodes('/*') AS roots(node)
    UNION ALL
    SELECT
        CAST(x.nodes_name + '/' + node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)),
        node.query('*') AS children,
        x.depth + 1,
        node.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS position
    FROM Xml_CTE x
    CROSS APPLY x.children.nodes('*') AS child(node)
)
SELECT nodes_name
FROM Xml_CTE
ORDER BY depth, nodes_name, position
OPTION (MAXRECURSION 1000);

关键说明 #

• depth：标记节点的层级，确保根节点、一级子节点、二级子节点按层级顺序输出；
• position：通过XQuery的<<运算符比较节点顺序，计算当前节点在父节点中的位置（从1开始），保证同层级节点严格遵循XML文档中的顺序；
• ORDER BY子句：先按层级排序，同层级内结合节点路径和位置排序，彻底解决顺序颠倒问题。

内容的提问来源于stack exchange，提问作者Luke