Jonas Stawski

Everything .NET and More

How to Get the Node Name of a Node from and XML Variable in SQL Server 2005

I was talking with my co-worker and data expert John Papa regarding the XML functions within SQL Server 2005. I basically couldn't find a way to get the node name of a specific node for an XML Variable. I hit up google and started searching for a way to get the syntax for getting the node name. I was searching terms like "SQL Server 2005 Xml get node name" thinking there would be a SQL Server function to provide me this information. I was not getting any good results. That's when John told me that SQL Server 2005 uses XPath as much as possible to work with the XML Data Type. I knew that because I use XPath to query the XML already, but it never occurred to me to use the XPath functions to get the name of the node. Here's how to do it:

@xml.query('local-name((/Root/Node2/*)[1])')

In the above example i'm getting the node name of the first node within the Root/Node2 node.

As a matter of fact you can use any of the XPath functions. Here's a list of those functions.

Happy Programming!

Comments (4) -

Great Article. This is exactly what i was looking for. Thanks for sharing this info.

Reply

Really great job...
Thanks for the code.

is it possible to give links to get further system defined functions (like local_names)

Thanks in Advance
M.Umashankar

Reply

Thanks For your post, its really helpful.

How to get all the node names after the "Root/Node2" in xml, currently we are getting first node name using this @xml.query('local-name((/Root/Node2/*)[1])').

I have xml like this

<?xml version="1.0"?>  
<Root>
<Type>aa</Type>
<PagePath>abc</PagePath>
<Rating>1</Rating>
<Reference>good</Reference>
</Root>

And I want to get all xml node names after root node as individual rows as following.

Type
PagePath
Rating
Reference


Thanks in advance.

Reply

Thanks very useful!

To get the root node name.

@xml.query('local-name((/*)[1])')

Reply

Add comment

biuquote
Loading