Failure Details: xmlset

Showing posts with label xmlset. Show all posts

Monday, March 19, 2012

Get XML root name in Sql Server

declare @.x xml
set @.x =
'<Chicago>
<Area>A1</Area>
<Group>5</Group>
<Question>Q1</Question>
</Chicago>'

How to get the XML root name 'Chicago' in Sql Server 2005?

Since you are using the XQuery or XPath query you should know the exact path of the xml. Otherwise you can't utilize this new feature in SQL Server...

There is no functions available in XQuery to fetch the XML Root element name, but you can utilize the Stringmanipulation to find what is your root..

Code Snippet

declare @.x xml

set @.x =

'<Chicago>

</Chicago>'

Select

Substring(XML,Charindex('<', XML)+1, Charindex('>', XML) - Charindex('<', XML)-1)

From

(

Select

Cast(@.X as Varchar(max)) as XML

) as Data

Friday, February 24, 2012

Get Position() Value in XML.Query

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.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>






<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

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.googlegroups.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>
> 
> 
> 
> 
> 
> 
> <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...
>

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>






<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>
> 
> 
> 
> 
> 
> 
> <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...
>

Monday, March 19, 2012

Get XML root name in Sql Server

Friday, February 24, 2012

Get Position() Value in XML.Query

Get Position() Value in XML.Query

Failure Details

Blog Archive

About Me