在 SQL Server 中，如何保證回傳的資料按 XML 列中的節點順序排序，CROSS APPLY在以下使用時？
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
uj5u.com熱心網友回復：
不幸的是，SQL Server 不允許您position()在.value呼叫中使用。
相反，您可以通過計算節點來破解它。
您可以在沒有 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<>小提琴
或者將其保留在中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<>小提琴
其作業方式如下：
- 將當前節點分配
.給$i - 獲取父節點
..并計算其所有i子節點... - ...
<<在$i節點之前 - 添加一個
uj5u.com熱心網友回復：
@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<>小提琴
uj5u.com熱心網友回復：
另一種方法：預先計算元素的位置
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分別為每一行創建一個計數 - 我們使用 as computed
TOP()-clause using the row's count of<i>-elements - 這將創建每行相關行的正確計數
- 現在我們可以使用
theIndex通過它的位置來獲取元素的值sql:column()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/489249.html
標籤：sql服务器 xml tsql sql-order-by sql-server-2014
上一篇：XSLT3.0：按方向劃分頁面