TSQL -通过交叉应用的XML序列排序

Question

在SQL Server中，当在下面使用CROSS APPLY时，我如何保证返回的数据是按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

遗憾的是，SQL Server不允许在.value调用中使用position()。

相反，你可以通过计算节点来破解它。

您可以不使用CTE，通过将.value放入ORDER BY

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的在小提琴

或者把它留在SELECT中，你仍然可以ORDER BY这个值

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的在小提琴

工作方式如下:

• 将当前节点.分配给$i
• 取父节点..并计算其所有i子节点，其中…
• …$i节点前<<
• 添加一个

Answer 2

另一种方法:预计算元素的位置

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

简而言之:

• 我们使用CROSS APPLY分别为每一行创建一个计数
• 我们使用计算TOP()-子句使用<i>-元素的行计数
• 这将创建每行相关行的正确计数
现在我们可以使用theIndex通过sql:column()获取元素的位置值

Answer 3

@Charlieface给我指出了我真正想要的答案…尽管我真的很感谢他们花时间/精力来回答问题。

我从未考虑过使用ROW_NUMBER作为ORDER BY本身…

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的在小提琴