Preserve element order when shredding XML data into SQL rows

sql, sql-server-2005, xquery

Solution

You can use `row_number()` on the xml node.

CREATE VIEW OrderedList
AS
SELECT [Sequence] = ROW_NUMBER() OVER(ORDER BY b.b),
       [Name] = b.b.value('.', 'nvarchar(max)')
FROM
(
    SELECT a = CAST('<ol><li>Smith</li><li>Jones</li><li>Brown</li></ol>' AS xml)
) a
CROSS APPLY a.a.nodes('/ol/li') b (b)

Ref: Uniquely Identifying XML Nodes with DENSE_RANK by Adam Machanic.

Problem

How do I return the element sequence when shredding XML into rows in a SQL Server view? Sample Input: ``` <ol> <li>Smith</li> <li>Jones</li> <li>Brown</li> </ol> ``` Desired output: ``` Sequence Name -------- ----------- 1 Smith 2 Jones 3 Brown ``` Existing view: ``` CREATE VIEW OrderedList AS SELECT [Sequence] = CAST(NULL AS int) -- TODO: Get ordinal position [Name] = b.b.value('.', 'nvarchar(max)') FROM ( SELECT a = CAST('<ol><li>Smith</li><li>Jones</li><li>Brown</li></ol>' AS xml) ) a CROSS APPLY a.a.nodes('/ol/li') b (b) ```

Original source