In SQL Server, how can I guarantee that returned data is ordered by the sequence of nodes in an XML column, when using CROSS APPLY in the following?

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

The best I can come up with (but doesn't feel like a great solution) is...

; 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

CodePudding user response：

Unfortunately, SQL Server does not allow you to use position() in a .value call.

Instead you can hack it by counting nodes.

You can do it without a CTE, by putting .value into the 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<>fiddle

Alternatively leave it in the SELECT and you can still ORDER BY that value

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<>fiddle

The way this works is as follows:

• Assign the current node . to $i
• Take the parent node .. and count all its i child nodes which...
• ... are << before the $i node
• Add one

CodePudding user response：

@Charlieface pointed me to the answer I really wanted... although I really appreciate their time/effort with their answer.

I never considered using ROW_NUMBER as the ORDER BY itself...

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<>fiddle