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:


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.


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


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"?>  

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


Thanks in advance.


Thanks very useful!

To get the root node name.



Add comment