ROW_NUMBER() OVER (ORDER BY xml.node)的行为是否有明确定义？

问题背景 #

在研究按原始元素顺序提取XML节点的方案时，发现不少技术社区的答案使用ROW_NUMBER() OVER (ORDER BY xml.node)表达式，声称能按XML文档原始顺序分配行号。但微软T-SQL的OVER子句官方文档并未专门说明XML节点在ORDER BY中的行为，找不到明确的文档定义这种写法的可靠性。

示例代码 #

DECLARE @xml XML = '<root>
<node>One</node>
<node>Two</node>
<node>Three</node>
<node>Four</node>
</root>'

SELECT
    ROW_NUMBER() OVER(ORDER BY xml.node) AS rn,
    xml.node.value('./text()[1]', 'varchar(255)') AS value
FROM @xml.nodes('*/node') xml(node)
ORDER BY
    ROW_NUMBER() OVER(ORDER BY xml.node)

执行结果 #

rn | value
----------
1  | One
2  | Two
3  | Three
4  | Four

核心疑问 #

上述按XML原始节点顺序返回的结果是否有官方文档保障？属于被认可的未公开可靠行为，还是类似ORDER BY (SELECT NULL)仅在小数据集有效、大规模场景可能失效的不可靠写法？

补充异常情况 #

在普通SELECT的ORDER BY子句中直接使用ORDER BY xml.node会触发错误：

Msg 493 Level 16 State 1 Line 7
从nodes()方法返回的列'node'不能直接使用，仅可与exist()、nodes()、query()、value()这四种XML数据类型方法，或IS NULL、IS NOT NULL检查配合使用。

解答 #

核心结论 #

ROW_NUMBER() OVER (ORDER BY xml.node)的写法无官方文档明确声明保障，但它是SQL Server中存在多年、被广泛验证的稳定行为，并非仅小数据集有效的临时方案。

详细说明 #

1. 行为本质：SQL Server对XML节点的内部存储保留了文档顺序的上下文，当在窗口函数的OVER子句ORDER BY中直接引用nodes()返回的XML节点时，引擎会隐式以节点的文档顺序作为排序依据。该行为从SQL Server 2005引入XML类型以来一直稳定存在。
2. 文档缺失原因：微软官方文档未专门针对该场景做说明，但这属于XML类型实现的隐含特性，而非未定义的随机行为，大量生产环境实践证明其在各种数据规模下都能稳定工作。
3. 与普通ORDER BY的差异：普通SELECT的ORDER BY子句不允许直接引用nodes()返回的节点（如上述错误提示），但窗口函数的OVER子句属于独立上下文，引擎对XML节点的处理逻辑不同，因此可以合法使用。
4. 有文档保障的替代方案：如果追求完全符合官方文档定义的写法，可以利用XML官方定义的节点比较运算符<<（用于判断节点的文档顺序），显式计算节点的位置：

DECLARE @xml XML = '<root>
<node>One</node>
<node>Two</node>
<node>Three</node>
<node>Four</node>
</root>'

SELECT
    ROW_NUMBER() OVER(ORDER BY node_position) AS rn,
    xml.node.value('./text()[1]', 'varchar(255)') AS value
FROM @xml.nodes('*/node') xml(node)
CROSS APPLY (
    SELECT xml.node.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS node_position
) AS pos
ORDER BY rn

该写法通过计算当前节点之前的同级节点数量得到文档顺序位置，完全基于官方文档定义的特性，可靠性有明确保障。

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