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

i suspect that sql server's xquery implementation is not up to this task, but this is another way of doing it (inspired by this, adapt as required):

DECLARE @idoc INT, @xml XML
SET @xml = (SELECT TOP 1 my_xml_column FROM my_table)
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;

WITH
    E AS (SELECT * FROM OPENXML(@idoc,'/',3)),
    P AS
    (
    -- anchor member
    SELECT id, parentid, localname AS [Path]
    FROM E WHERE parentid IS NULL
    UNION ALL
    -- recursive member
    SELECT E.id, E.parentid, P.[Path] + '/' + localname AS [Path]
    FROM P INNER JOIN E ON E.parentid = P.id
    )
SELECT [Path] FROM P

EXEC sp_xml_removedocument @idoc

Answer 2

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.