Preserving XML document order when using XQuery.nodes in relational table joins

If you are not taking advantage of the XML support in SQL Server 2005, then shame on you. Among other things, XML is the perfect data representation format for passing simple collections (i.e. arrays and lists) to stored procedures. Whether you want to easily optimize your CRUD operations, and/or take advantage of stored procedure parameter validation using typed XML. Furthermore, XQuery is great at shredding the XML into a relational format for use within your stored procedures.

Unlike the relational model, XML documents have an implied order. By default, the XQuery ordering mode in SQL Server 2005 is ordered. In other words, the node sequences returned by the path expressions are in XML document order. It is never safe to assume that the SQL Server Query Processor will preserve the XML document order when joining it with a relational table. In most of the testing that I conducted, the ordering was preserved; however, in the following circumstance it was not.

Consider the following DDL:

CREATE TABLE Test (
col1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),
col2 VARCHAR(100) NOT NULL
)
GO

CREATE PROCEDURE TestProc
@list XML
AS
BEGIN
SELECT Test.col1, Test.col2
FROM @list.nodes ( ‘/List/Value’ ) List(col1)
INNER JOIN Test ON Test.col1 = List.col1.value (‘@col1’, ‘UNIQUEIDENTIFIER’ )
END
GO

and the following Table data (in INSERT order):

col1 col2
------------------------------------ ---------------------------------
D443AD7A-9293-DC11-9042-00065B83FA16 One
D543AD7A-9293-DC11-9042-00065B83FA16 Two
D643AD7A-9293-DC11-9042-00065B83FA16 Three
D743AD7A-9293-DC11-9042-00065B83FA16 Four
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D943AD7A-9293-DC11-9042-00065B83FA16 Six
DA43AD7A-9293-DC11-9042-00065B83FA16 Seven
DB43AD7A-9293-DC11-9042-00065B83FA16 Eight
DC43AD7A-9293-DC11-9042-00065B83FA16 Nine
DD43AD7A-9293-DC11-9042-00065B83FA16 Ten

and finally the following batch:

DECLARE @list XML
SET @list = '<List>
<Value col1="D843AD7A-9293-DC11-9042-00065B83FA16" />
<Value col1="D943AD7A-9293-DC11-9042-00065B83FA16" />
<Value col1="D843AD7A-9293-DC11-9042-00065B83FA16" />
<Value col1="D943AD7A-9293-DC11-9042-00065B83FA16" />
<Value col1="D843AD7A-9293-DC11-9042-00065B83FA16" />
<Value col1="D943AD7A-9293-DC11-9042-00065B83FA16" />
</List>'

EXEC TestProc @list

SELECT t.col1, t.col2
FROM @list.nodes('/List/Value') List(col1)
INNER JOIN Test t ON t.col1 = List.col1.value('@col1','uniqueidentifier')

While the SELECT t.col1, t.col2… produced the desired resultset, EXEC TestProc @list produced a resultset not in XML order because the optimizer chose a plan that specified a Merge Join algorithm.

col1 col2
------------------------------------ ---------------------------------
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D943AD7A-9293-DC11-9042-00065B83FA16 Six
D943AD7A-9293-DC11-9042-00065B83FA16 Six
D943AD7A-9293-DC11-9042-00065B83FA16 Six

To preserve the XML document order within our stored procedure, we can simply use the SQL Server 2005 ranking functions. So TestProc now looks like this:

ALTER PROCEDURE TestProc
@list XML
AS
BEGIN
SELECT t.col1, t.col2
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY preserveCount) AS rowNumber, PreserveOrder.col1
FROM
(SELECT List.col1.value('@col1','uniqueidentifier') AS col1
, 0 AS preserveCount
FROM @list.nodes('/List/Value') List(col1)) PreserveOrder
) OrderedList
INNER JOIN Test t ON t.col1 = OrderedList.col1
ORDER BY OrderedList.rowNumber ASC;
END

EXEC TestProc @list will now produce the proper results.

col1 col2
------------------------------------ ---------------------------------
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D943AD7A-9293-DC11-9042-00065B83FA16 Six
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D943AD7A-9293-DC11-9042-00065B83FA16 Six
D843AD7A-9293-DC11-9042-00065B83FA16 Five
D943AD7A-9293-DC11-9042-00065B83FA16 Six

Posted at 12:48 PM  Permalink