TSQL-通过XML序列订购交叉序列

Question

在SQL Server中，当使用 Cross Applion 中时，我如何保证返回数据是由XML列中节点的顺序排序的？

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

-- MyParent table...
ChildrenXML
<d><i>1</i><i>3></i><i>2</i></d>

-- MyChildren table...
Id    Name
1     Tom
2     Richard
3     Harry

-- Expected output...
Id    Name
1     Tom
3     Harry
2     Richard

我能想到的最好的（但感觉不好的解决方案）是...

; WITH ChildIds AS (
   SELECT CX.value('text()[1]','int') AS Id,
     ROW_NUMBER() OVER (ORDER BY CX.CX) AS RowNum
   FROM MyParent MP
     CROSS APPLY MP.ChildrenXml.nodes('/d/i') AS CX(CX)
)
SELECT MC.Id, MC.Name
FROM ChildIds CI
  INNER JOIN MyChildren MC
    ON MC.Id = CI.Id
ORDER BY CI.RowNum

Answer 1

预先计算元素的位置

DECLARE @tbl TABLE(theXml XML);
INSERT INTO @tbl(TheXml) VALUES(N'<d><i>1</i><i>2</i><i>3</i></d>')
                              ,(N'<d><i>10</i><i>20</i><i>30</i><i>40</i></d>')
                              ,(N'<d><i>5</i><i>3</i><i>3</i><i>-1</i></d>')
                              ,(N'<d><i>0</i><i>1</i></d>')
                               ;

SELECT A.theIndex
      ,t.theXml.value('(/d/i[sql:column("A.theIndex")]/text())[1]','int')
FROM @tbl t
CROSS APPLY(SELECT TOP(t.theXml.value(N'count(/d/i)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) A(theIndex);

而

• 简
• 另一种方法是： ） - 使用＆lt; i＆gt; - 元素的行计数，
• 将创建每个行相关行的正确计数
• 这 元素的值通过其位置使用 sql：column（）

Answer 2

不幸的是，SQL Server不允许您在 .value 调用中使用 position（）。

相反，您可以通过计算节点来入侵它。

您可以通过将 .value 放入

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

db＆lt;＆gt;＆gt; fiddle

另外

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

> db＆lt;

”

• “ https://dbfiddle.uk/?rdbms = sqlServer_2019&； fiddle = 93d22596c8888d340ce84cac44430d3b99c“ rel =“ nofollow noreflowl noreferrer” noreferrer“ noreferrer 到 $ i
• 以父节点 .. 来计数其所有 i 儿童节点
• ...代码>＆lt;＆lt; $ i 节点
• 添加一个

Answer 3

@Charlieface向我指出了我真正想要的答案...尽管我真的很感谢他们的回答时间/努力。

我从未考虑过使用 row_number 作为订单本身...

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')
ORDER BY ROW_NUMBER() OVER (ORDER BY CX.CX)

db＆lt;＆gt; fiddle