MS SQL XML - How to retrieve xml element name in sql server 2005?

Author

How to retrieve xml element name in sql server 2005?

jamesagnew

2005-09-05, 8:25 pm

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

Han

2005-09-06, 3:24 am

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

jamesagnew

2005-09-06, 3:24 am

That's it! I've got it working as follows:

select @xmlNode.value('local-name((//*[@id="1"])& #91;1])','nvarchar(5
5)')

Han, you're a great asset to this list and your help is much
appreciated.

Thanks again, James

Sponsored Links