Returning position() in the results

13 Jun 2009 10:22 PM

John Constant

I have an ordered list of elements in Xml and I need to return their
position in the xml, something along the lines of value('position()', 'int')

I have no problem in filtering the list by changing the xpath to
@Xml.nodes('//doc/MPR [position()=1]') but I've not found a way of returning
that value

Any help or suggestions would be great

Thanks in advance

John

declare @xml xml; set @xml='
<doc>
    <MPR N="This is a Test" P="22.0000" D="0.0000" />
    <MPR N="'' With Single Quote" P="22.0000" D="0.0000" />
    <MPR N="" With Double Quote" P="22.0000" D="0.0000" />
    <MPR N="< With Less Than" P="22.0000" />
</doc>'

Select
a.Customer.value('@N[1]', 'varchar(200)') as Name
, a.Customer.value('@P[1]', 'smallmoney') as [Price]
, a.Customer.value('@D[1]', 'smallmoney') as [Delivery]
-- , a.Customer.value('position()', 'int') as Position
From @Xml.nodes('//doc/MPR') as a(Customer)

14 Jun 2009 12:48 PM

Martin Honnen

John Constant wrote:

> Select
> a.Customer.value('@N[1]', 'varchar(200)') as Name
> , a.Customer.value('@P[1]', 'smallmoney') as [Price]
> , a.Customer.value('@D[1]', 'smallmoney') as [Delivery]
> -- , a.Customer.value('position()', 'int') as Position

With full XQuery support you could do
, a .Customer.value('count(preceding-sibling::MPR) + 1', 'int') as
Position but SQL Server 2005 tells me that preceding-sibling is not
supported when I try that.

You could however try to solve that with the ROW_NUMBER function:

Select
a.Customer.value('@N[1]', 'varchar(200)') as Name
, a.Customer.value('@P[1]', 'smallmoney') as [Price]
, a.Customer.value('@D[1]', 'smallmoney') as [Delivery]
, ROW_NUMBER() OVER (ORDER BY Customer) as Position
From @Xml.nodes('//doc/MPR') as a(Customer)

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

14 Jun 2009 7:42 PM

John Constant

Martin

ROW_NUMBER() works perfectly Thanks, I've no idea how long it would have
taken me to find it.

John

Show quoteHide quote

"Martin Honnen" <mahotr***@yahoo.de> wrote in message
news:%23gB755O7JHA.1424@TK2MSFTNGP02.phx.gbl...
> John Constant wrote:
>
>> Select
>> a.Customer.value('@N[1]', 'varchar(200)') as Name
>> , a.Customer.value('@P[1]', 'smallmoney') as [Price]
>> , a.Customer.value('@D[1]', 'smallmoney') as [Delivery]
>> -- , a.Customer.value('position()', 'int') as Position
>
> With full XQuery support you could do
> , a .Customer.value('count(preceding-sibling::MPR) + 1', 'int') as
> Position but SQL Server 2005 tells me that preceding-sibling is not
> supported when I try that.
>
> You could however try to solve that with the ROW_NUMBER function:
>
> Select
> a.Customer.value('@N[1]', 'varchar(200)') as Name
> , a.Customer.value('@P[1]', 'smallmoney') as [Price]
> , a.Customer.value('@D[1]', 'smallmoney') as [Delivery]
> , ROW_NUMBER() OVER (ORDER BY Customer) as Position
> From @Xml.nodes('//doc/MPR') as a(Customer)
>
> --
>
> Martin Honnen --- MVP XML
> http://msmvps.com/blogs/martin_honnen/