Preserve element order when shredding XML data into SQL rows

Question

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)

Mikael Eriksson · Accepted Answer · 2012-04-16 12:04:17Z

11

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.

answered Apr 16, 2012 at 12:04

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Preserve element order when shredding XML data into SQL rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related