插入XML节点时是否保留文档中节点的顺序？

Question

如果我执行：

INSERT INTO dst
SELECT blah
FROM src
CROSS APPLY xmlcolumn.nodes('blah')

如果dst有一个标识列，是否可以确定该标识列的顺序与原始xml文档中节点的顺序相匹配？

我认为答案是否定的，没有保证。为了确保顺序能够保持不变，需要在枚举节点的同时从XML中提取一些顺序信息。

- Cade Roux

---

请参见 https://dev59.com/SGMm5IYBdhLWcg3wRdQz#17764715。 - Razvan Socol

Answer 1

虽然在执行计划中无法明确看到，但由nodes()方法返回的id列是一个varbinary(900)OrdPath，它封装了原始xml文档的顺序。

Mikael Eriksson在相关问题“`nodes()`方法是否保留文档顺序？”上提供的解决方案依赖于OrdPath来提供ORDER BY子句，以确定如何为INSERT分配身份值。

以下是稍微更紧凑的使用：

CREATE TABLE #T 
(
    ID integer IDENTITY, 
    Fruit nvarchar(10) NOT NULL
);

DECLARE @xml xml = 
    N'
    <Fruits>
      <Apple />
      <Banana />
      <Orange />
      <Pear />
    </Fruits>
    ';

INSERT #T 
    (Fruit)
SELECT 
    N.n.value('local-name(.)', 'nvarchar(10)') 
FROM @xml.nodes('/Fruits/*') AS N (n)
ORDER BY
    ROW_NUMBER() OVER (ORDER BY N.n);

SELECT 
    T.ID, 
    T.Fruit 
FROM #T AS T
ORDER BY
    T.ID;

db<>fiddle

的使用方法目前未有官方文档，但原理是可行的：

1. OrdPath 反映了文档顺序。
2. ROW_NUMBER 按 OrdPath* 计算序列值。
3. ORDER BY 子句使用行号序列。
4. 按照 ORDER BY 分配标识值给行。

需要明确的是，即使使用并行处理，这一点仍然成立。正如 Mikael 所说，使用 id 在 ROW_NUMBER 中存在疑问，因为文档未记录 id 作为 OrdPath。

---

计划中没有显示顺序，但使用TF 8607的优化器输出包含：

ScaOp_SeqFunc row_number order[CALC:QCOL: XML Reader with XPath filter.id ASC]

- Paul White

Answer 2

在当前实现的.nodes下，XML节点按文档顺序生成。结果总是使用嵌套循环与原始数据连接，也总是按顺序运行。

此外，插入通常是串行的（除非在非常特定的情况下它变为并行，通常是在有空表时，并且永远不会生成一个IDENTITY值）。

因此，服务器没有理由以与文档顺序不同的顺序返回行。您可以从这个代码片段中看到发生了什么。

话虽如此，不能保证.nodes实现不会改变，或者将来可能会并行插入，因为这两种情况都没有被记录在任何地方作为保证。因此，在没有明确的ORDER BY的情况下，您不应该依赖它，同时也没有可以排序的列。

使用ORDER BY可以保证其正确性。文档中提到：“使用SELECT和ORDER BY填充行的INSERT查询保证标识值的计算方式，但并不保证插入行的顺序。”

---

即使像一些人建议的那样使用ROW_NUMBER，也不能保证正确性。真正的解决方法是直接从XQuery获取文档顺序。

问题在于，SQL Server版本的XQuery不允许将position(.)用作结果，只能用作谓词。相反，您可以使用涉及<<位置运算符的Hack方法。

例如：

SELECT T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       T.X.value('let $i := . return count(../*[. << $i]) + 1', 'int') as RowNumber
FROM src
CROSS APPLY xmlcolumn.nodes('blah') as T(X);

这段代码的作用如下：

• 将当前节点.赋值给变量$i
• 获取父节点的所有子节点 ../*
• ...筛选出在$i之前的节点 [. << $i]
• 并统计它们的数量
• 最后加1使结果从1开始计数

- Charlieface