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

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

DECLARE @xml xmlSET @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 nameFROM Xml_CTEOPTION (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.

UDF for you.....

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[XMLTable](@x XML)  RETURNS TABLE AS RETURN WITH cte AS (  SELECT         1 AS lvl,          x.value('local-name(.)','NVARCHAR(MAX)') AS Name,          CAST(NULL AS NVARCHAR(MAX)) AS ParentName,         CAST(1 AS INT) AS ParentPosition,         CAST(N'Element' AS NVARCHAR(20)) AS NodeType,          x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,          x.value('local-name(.)','NVARCHAR(MAX)')          + N'['         + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)          + N']' AS XPath,          ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,         x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,          x.value('text()[1]','NVARCHAR(MAX)') AS Value,          x.query('.') AS this,                 x.query('*') AS t,          CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,          CAST(1 AS INT) AS ID  FROM @x.nodes('/*') a(x)  UNION ALL SELECT         p.lvl + 1 AS lvl,          c.value('local-name(.)','NVARCHAR(MAX)') AS Name,          CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,     CAST(p.Position AS INT) AS ParentPosition,         CAST(N'Element' AS NVARCHAR(20)) AS NodeType,          CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,          CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')         ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,          ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')        ORDER BY (SELECT 1)) AS Position,         CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,          CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,          c.query('*') AS t,          CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,          CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  FROM cte p  CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (                                              SELECT                                             lvl AS Depth,                                              Name AS NodeName,                                              ParentName,                                             ParentPosition,                                             NodeType,                                              FullPath,                                              XPath,                                              Position,                                             Tree AS TreeView,                                              Value,                                              this AS XMLData,                                              Sort, ID                                              FROM cte  UNION ALL SELECT         p.lvl,          x.value('local-name(.)','NVARCHAR(MAX)'),          p.Name,         p.Position,         CAST(N'Attribute' AS NVARCHAR(20)),          p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),          p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),          1,         SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)          + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),          x.value('.','NVARCHAR(MAX)'),          NULL,          p.Sort,          p.ID + 1  FROM cte p  CROSS APPLY this.nodes('/*/@*') a(x)  )  SELECT         ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,          ParentName, ParentPosition,Depth, NodeName, Position,           NodeType, FullPath, XPath, TreeView, Value, XMLData FROM cte2

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 XMLSET @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 PEXEC sp_xml_removedocument @idoc