Finding the Root Node Name with XQuery and SQL Server 2005

I recently had a case where i needed to test for the existence of a node in a query involving an XML. After researching I found a lot of posts indicating this as the solution:

DECLARE @x xml
SET @x = '
123 Main St.
'

DECLARE @query varchar(30)
SET @query = '/address/street'

SELECT @x.exist('sql:variable("@query")')

A key point to make is that the ".exist" is case sensitive; using ".Exist" causes an error. The result is "1", however when i would change @query to an invalid xpath, I would still get "1". Never the less, it was still a good start toward figuring out how to find the root node tag name.

Many posts I found used the 'sql:variable("@variable")' as a parameter in a xpath query. I am quite experienced w/ XPath, especially with Xsl, so I applied it to my situation and came up with this (simplified for example)...

DECLARE @tag varchar(30)
SET @tag = 'address'

SELECT ID, XmlField
FROM sometable
WHERE XmlField.exist('/*[1][local-name() = sql:variable("@tag")]')

The result of this one was 1. If a changed @tag to an invalid input, the result was 0. This is exactly what I was expecting and thus my solution.

Taking this further, the tag name can be returned by extending the previous example.

DECLARE @tag varchar(30)
SET @tag = 'address'

SELECT ID, XmlField.query('local-name(/*[1])'), XmlField
FROM sometable
WHERE XmlField.exist('/*[1][local-name() = sql:variable("@tag")]')

There you go, hope it helps.