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!