|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Get Position() Value in XML.QueryDECLARE @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 <markc***@hotmail.com> wrote in message Show quote news:1169684064.504759.156860@q2g2000cwa.googlegroups.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 <markc***@hotmail.com> wrote in message Show quote news:1169759470.715829.81540@s48g2000cws.googlegroups.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 Show quote "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 > > > <markc***@hotmail.com> wrote in message > news:1169759470.715829.81540@s48g2000cws.googlegroups.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 >> > > Michael,
Is there anyway to always get the result to start with a sequence of 1? Thanks, Matt Show quote "Michael Rys [MSFT]" <m***@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... >> 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 >> >> >> <markc***@hotmail.com> wrote in message >> news:1169759470.715829.81540@s48g2000cws.googlegroups.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 >>> >> >> > > 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 Show quote "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]" <m***@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... >>> 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 >>> >>> >>> <markc***@hotmail.com> wrote in message >>> news:1169759470.715829.81540@s48g2000cws.googlegroups.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 >>>> >>> >>> >> >> > > 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 Show quote "Michael Rys [MSFT]" <m***@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... >> Michael, >> >> Is there anyway to always get the result to start with a sequence of 1? >> >> Thanks, >> >> Matt >> >> >> "Michael Rys [MSFT]" <m***@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... >>>> 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 >>>> >>>> >>>> <markc***@hotmail.com> wrote in message >>>> news:1169759470.715829.81540@s48g2000cws.googlegroups.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 >>>>> >>>> >>>> >>> >>> >> >> > > Can you post the data that starts at the wrong number please?
Thanks Michael Show quote "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]" <m***@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... >>> Michael, >>> >>> Is there anyway to always get the result to start with a sequence of 1? >>> >>> Thanks, >>> >>> Matt >>> >>> >>> "Michael Rys [MSFT]" <m***@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... >>>>> 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 >>>>> >>>>> >>>>> <markc***@hotmail.com> wrote in message >>>>> news:1169759470.715829.81540@s48g2000cws.googlegroups.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 >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics