CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-order-bysql-server-2014
freefaller
freefaller

Reputation: 19953

TSQL - ordering by XML sequence with cross apply

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

Upvotes: 0

Views: 671

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

One more approach: Pre-Computing the element's position

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

The idea in short:

  • We use CROSS APPLY to create a tally for each row separately
  • We use as computed TOP()-clause using the row's count of <i>-elements
  • This will create the correct count of related rows per row
  • Now we can use theIndex to fetch the element's value by its position using sql:column()

Upvotes: 1

freefaller
freefaller

Reputation: 19953

@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

Upvotes: 0

Charlieface
Charlieface

Reputation: 71253

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

Upvotes: 0

Related Questions