Get Position() Value in XML.Query

Given the following syntax in SQL Server 2005:
DECLARE @.xmlvar xml
set @.xmlvar = '
<Data>
<Student id="s1">
<Name>Bugs Bunny</Name>
<Class>FFA</Class>
</Student>
<Student id="s2">
<Name>Road Runner</Name>
<Class>Sports</Class>
</Student>
<Student id="s3">
<Name>Wile E. Coyote</Name>
<Class>Science</Class>
</Student>
<Student id="s4">
<Name>Foghorn Leghorn</Name>
<Class>Self Defense</Class>
</Student>
</Data>'
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class
--Data.Student.query('string(position())') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)
Which results with:
id name class
s1 Bugs Bunny FFA
s2 Road Runner Sports
s3 Wile E. Coyote Science
s4 Foghorn Leghorn Self Defense
How would I then return another column that gives me the sequence in the
list, such as position() would do in XSL?
id name class ndx
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
Thanks,
Matt
Maybe this?
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
Data.Student.value('for $a in . return count($a/../*[. << $a]) +
1','int') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)
|||Mark,
Thanks that definitly gets me going in the right direction. Can you explain
the statement count($a/../*[. << $a]) , other than count() I get lost after
the "[".
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169684064.504759.156860@.q2g2000cwa.googlegro ups.com...
> Maybe this?
>
> select Data.Student.value('@.id', 'varchar(6)') as id,
> Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
> Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
> Data.Student.value('for $a in . return count($a/../*[. << $a]) +
> 1','int') as ndx
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>
|||Matt
This is the count of the preceding siblings for a given node (plus one)
using the << operator which returns true if the
left operand node precedes the right operand node in document order.
This query may make things clearer.
select Data.Student.query('.') as node,
Data.Student.query('for $a in . return $a/../*[. << $a]') as
allprevsiblings
from @.xmlvar.nodes('/Data/Student') Data(Student)
Regards
Mark
|||Mark,
Thanks for the explanation but I am confused by something I am getting with
one of my queries. Using your recommended method works great on one part of
the XML I am working with but in another area the number starts at 7. If I
run different things I get different values. Any ideas? I can't post the
actual XML I am working with but I can send it to you if you would like to
see my issue with my query.
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169759470.715829.81540@.s48g2000cws.googlegro ups.com...
> Matt
> This is the count of the preceding siblings for a given node (plus one)
> using the << operator which returns true if the
> left operand node precedes the right operand node in document order.
> This query may make things clearer.
> select Data.Student.query('.') as node,
> Data.Student.query('for $a in . return $a/../*[. << $a]') as
> allprevsiblings
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>
> Regards
> Mark
>
|||That's probably because the element that you select in the nodes() method is
not the first but the 7th child of the parent node.
E.g.
declare @.x xml;
set @.x = N'<a>
<b/>
<b/>
<b/>
<b/>
<b/>
<b/>
<c>1</c>
<c>2</c>
</a>';
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
from @.x.nodes('/a/c') N(n)
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Mark,
> Thanks for the explanation but I am confused by something I am getting
> with one of my queries. Using your recommended method works great on one
> part of the XML I am working with but in another area the number starts at
> 7. If I run different things I get different values. Any ideas? I can't
> post the actual XML I am working with but I can send it to you if you
> would like to see my issue with my query.
> Thanks,
> Matt
>
> <markc600@.hotmail.com> wrote in message
> news:1169759470.715829.81540@.s48g2000cws.googlegro ups.com...
>
|||Michael,
Is there anyway to always get the result to start with a sequence of 1?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
> That's probably because the element that you select in the nodes() method
> is not the first but the 7th child of the parent node.
> E.g.
> declare @.x xml;
> set @.x = N'<a>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <c>1</c>
> <c>2</c>
> </a>';
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
> from @.x.nodes('/a/c') N(n)
>
> Best regards
> Michael
>
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
>
|||There are two ways, one if you know the name you are selecting (note the c
instead of *):
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n)
Or if you don't know the name apriori, you can normalize:
with T as
(select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n))
select c, pos - (select min(pos) from T)+1 from T
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
> Michael,
> Is there anyway to always get the result to start with a sequence of 1?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
>
|||Michael,
I have been unable to get your solution to work. Can you use my previous
sample and see if you can get this working?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
> There are two ways, one if you know the name you are selecting (note the c
> instead of *):
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n)
>
> Or if you don't know the name apriori, you can normalize:
> with T as
> (select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n))
> select c, pos - (select min(pos) from T)+1 from T
> Best regards
> Michael
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
>
|||Can you post the data that starts at the wrong number please?
Thanks
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23DY9sEUSHHA.2124@.TK2MSFTNGP06.phx.gbl...
> Michael,
> I have been unable to get your solution to work. Can you use my previous
> sample and see if you can get this working?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
>