How to retrieve xml element name in sql server 2005?

Hi
Here's a simple example of what I'm trying to achieve:
declare @.xmlNode xml
set @.xmlNode='
<a id="1">
<b id="2" />
<c id="3" />
</a>'
select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
function not allowed
I need to be able to return the name of an element for a particular id,
so the above example should return the value 'a'. If the search were
for id="3" the the result should be 'c'.
However, it seems that the name() function is not (yet?) supported in
sql server 2005. I've looked into local-name() but that's only
supported in a predicate/selector so I can't retrieve an actual value
from it.
I've also looked into the possibility of using the mp:localname
metaproperty in openxml but it doesn't seem like the right solution
since it should be possible to accomplish from within xquery.
It's probably staring me in the face, but I just can't seem to find the
solution.
Any help very much appreciated.
Regards, JamesCorrect syntax of name() and local-name() is,
name(xpath)
not,
xpath/name()
Yes, name() is not yet supported. Use local-name().
Additionally, xpath2.0 or xquery1.0 is occurence sensitive. So called
singleton is required for the local-name(). This is an error.
local-name(//*[@.id="1"])
Do instead,
local-name((//*[@.id="1"])[1])
Pohwan Han. Seoul. Have a nice day.
"jamesagnew" <jamesagnew@.hotmail.com> wrote in message
news:1125962687.996958.18470@.o13g2000cwo.googlegroups.com...
> Hi
> Here's a simple example of what I'm trying to achieve:
> --
> declare @.xmlNode xml
> set @.xmlNode='
> <a id="1">
> <b id="2" />
> <c id="3" />
> </a>'
> select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
> function not allowed
> --
> I need to be able to return the name of an element for a particular id,
> so the above example should return the value 'a'. If the search were
> for id="3" the the result should be 'c'.
> However, it seems that the name() function is not (yet?) supported in
> sql server 2005. I've looked into local-name() but that's only
> supported in a predicate/selector so I can't retrieve an actual value
> from it.
> I've also looked into the possibility of using the mp:localname
> metaproperty in openxml but it doesn't seem like the right solution
> since it should be possible to accomplish from within xquery.
> It's probably staring me in the face, but I just can't seem to find the
> solution.
> Any help very much appreciated.
> Regards, James
>|||That's it! I've got it working as follows:
select @.xmlNode.value('local-name((//*[@.id="1"])[1])','nvarchar(55)')
Han, you're a great asset to this list and your help is much
appreciated.
Thanks again, James

How to retrieve xml element name in sql server 2005?

Hi
Here's a simple example of what I'm trying to achieve:
declare @.xmlNode xml
set @.xmlNode='
<a id="1">
<b id="2" />
<c id="3" />
</a>'
select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
function not allowed
I need to be able to return the name of an element for a particular id,
so the above example should return the value 'a'. If the search were
for id="3" the the result should be 'c'.
However, it seems that the name() function is not (yet?) supported in
sql server 2005. I've looked into local-name() but that's only
supported in a predicate/selector so I can't retrieve an actual value
from it.
I've also looked into the possibility of using the mp:localname
metaproperty in openxml but it doesn't seem like the right solution
since it should be possible to accomplish from within xquery.
It's probably staring me in the face, but I just can't seem to find the
solution.
Any help very much appreciated.
Regards, James
Correct syntax of name() and local-name() is,
name(xpath)
not,
xpath/name()
Yes, name() is not yet supported. Use local-name().
Additionally, xpath2.0 or xquery1.0 is occurence sensitive. So called
singleton is required for the local-name(). This is an error.
local-name(//*[@.id="1"])
Do instead,
local-name((//*[@.id="1"])[1])
Pohwan Han. Seoul. Have a nice day.
"jamesagnew" <jamesagnew@.hotmail.com> wrote in message
news:1125962687.996958.18470@.o13g2000cwo.googlegro ups.com...
> Hi
> Here's a simple example of what I'm trying to achieve:
> --
> declare @.xmlNode xml
> set @.xmlNode='
> <a id="1">
> <b id="2" />
> <c id="3" />
> </a>'
> select @.xmlNode.query('//*[@.id="1"]/name()') -- fails since name()
> function not allowed
> --
> I need to be able to return the name of an element for a particular id,
> so the above example should return the value 'a'. If the search were
> for id="3" the the result should be 'c'.
> However, it seems that the name() function is not (yet?) supported in
> sql server 2005. I've looked into local-name() but that's only
> supported in a predicate/selector so I can't retrieve an actual value
> from it.
> I've also looked into the possibility of using the mp:localname
> metaproperty in openxml but it doesn't seem like the right solution
> since it should be possible to accomplish from within xquery.
> It's probably staring me in the face, but I just can't seem to find the
> solution.
> Any help very much appreciated.
> Regards, James
>
|||That's it! I've got it working as follows:
select @.xmlNode.value('local-name((//*[@.id="1"])[1])','nvarchar(55)')
Han, you're a great asset to this list and your help is much
appreciated.
Thanks again, James