rank over shredded xml

Question

Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case).

Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with using the rank, but with no success.

declare @x xml
set @x = '
    <root>
        <category>
            <item value="north"/>
            <item value="south"/>
            <item value="east"/>
            <item value="west"/>
        </category>
        <category>
            <item value="red"/>
            <item value="green"/>
            <item value="blue"/>
        </category>
    </root>'

select c.value('./@value', 'varchar(10)') as "ItemValue", 
       'rank()' as "CategoryNumber"
from @x.nodes('//item') as t(c)

Answer 1

Jacob Sebastian also has an interesting solution presented in his blog post:

XQuery Lab 23 - Retrieving values and position of elements

With Jacob's suggestion, I can rewrite your query to be:

SELECT
    x.value('@value','VARCHAR(10)') AS 'ItemValue',        
    p.number as 'CategoryNumber'
FROM
    master..spt_values p
CROSS APPLY 
    @x.nodes('/root/category[position()=sql:column("number")]/item') n(x) 
WHERE
    p.type = 'p'

and I get the desired output:

ItemValue   CategoryNumber
---------   --------------
north           1
south           1
east            1
west            1
red             2
green           2
blue            2

Unfortunately, none of the more obvious solutions like the position() or fn:id() functions seem to a) work in SQL Server or b) be supported in SQL Server at all :-(

Hope this helps

Marc