SQL Server使用CROSS APPLY时如何按XML节点顺序返回结果

核心问题 #

使用nodes()方法拆解XML列做表关联时，SQL Server不会默认按XML内节点的书写顺序返回结果，必须显式取节点的位置标识做排序，才能保证输出顺序和XML节点顺序完全一致。
你当前用CTE+ROW_NUMBER() OVER (ORDER BY CX.CX)的方案可以实现效果，但存在两个问题：

• 写法冗余，需要多包一层CTE
• 对XML节点引用CX.CX排序属于依赖SQL Server未文档化的内部实现，版本升级、特殊XML结构下可能出现顺序异常

推荐实现 #

直接用XQuery原生逻辑获取节点在XML中的位置，不需要额外CTE，是官方支持的标准写法，性能和稳定性都更好：

SELECT 
  MC.Id, 
  MC.Name
FROM MyParent MP
CROSS APPLY MP.ChildrenXml.nodes('/d/i') AS CX(CX)
INNER JOIN MyChildren MC
  ON MC.Id = CX.value('text()[1]','int')
-- 按XML节点原生顺序排序
ORDER BY CX.value('let $cur := . return count(/d/i[. << $cur]) + 1', 'int')

实现原理 #

排序逻辑里的XQuery语句作用是：

1. 把当前节点赋值给变量$cur
2. 统计所有排在当前节点之前的同级<i>节点数量
3. 数量+1就是当前节点从1开始的位置序号，完全匹配XML里的节点书写顺序
   如果要让逻辑更清晰，也可以把节点解析逻辑单独放在APPLY里，避免排序时重复写解析逻辑：

SELECT 
  MC.Id, 
  MC.Name
FROM MyParent MP
CROSS APPLY (
  SELECT
    ChildId = CX.value('text()[1]','int'),
    SortOrder = CX.value('let $cur := . return count(/d/i[. << $cur]) + 1', 'int')
  FROM MP.ChildrenXml.nodes('/d/i') AS CX(CX)
) AS XmlParsed
INNER JOIN MyChildren MC
  ON MC.Id = XmlParsed.ChildId
ORDER BY XmlParsed.SortOrder

注意事项 #

• 任何场景下都不要省略ORDER BY子句，SQL Server对无排序的查询不保证返回顺序，哪怕测试时顺序正确，后续数据量变化、索引调整、版本升级都可能导致顺序错乱
• 示例XML存在笔误：<i>3></i>多了一个闭合尖括号，实际使用时需要修正为<i>3</i>，否则XML解析会报错
• 上述XQuery写法支持SQL Server 2005及以上所有版本

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