How can I get a list of element names from an XML value in SQL Server

Question

I have a table with an XML column in SQL Server 2k8. The following SQL retrieves some XML:

SELECT TOP 1 my_xml_column FROM my_table

Let's say it returns me the following XML

<a>
  <b />
  <c>
    <d />
    <d />
    <d />
  </c>
</a>

What I would like to get is

/a
/a/b
/a/c
/a/c/d
/a/e

In other words, how can I get SQL Server to tell me the structure of my XML?

I can do the following to get all the names of the individual elemtns:

SELECT  C1.query('fn:local-name(.)')
FROM    my_table
CROSS APPLY my_xml_column.nodes('//*') AS T ( C1 )

Perhaps if there was an equivalent to "local-name()" that returned the whole path of the element that would do the trick?

Answer 1

You can do this cleanly with XQuery and a recursive CTE (no OPENXML):

DECLARE @xml xml
SET @xml = '<a><b /><c><d /><d /><d /></c></a>';

WITH Xml_CTE AS
(
    SELECT
        CAST('/' + node.value('fn:local-name(.)',
            'varchar(100)') AS varchar(100)) AS name,
        node.query('*') AS children
    FROM @xml.nodes('/*') AS roots(node)

    UNION ALL

    SELECT
        CAST(x.name + '/' + 
            node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)),
        node.query('*') AS children
    FROM Xml_CTE x
    CROSS APPLY x.children.nodes('*') AS child(node)
)
SELECT DISTINCT name
FROM Xml_CTE
OPTION (MAXRECURSION 1000)

It's not really doing much XQuery magic, but at least it's all inline, doesn't require any stored procedures, special permissions, etc.