如何从SQL Server中的XML值获取元素名称列表

Question

我有一个SQL Server 2k8中的XML列的表。下面的SQL检索一些XML：

SELECT TOP 1 my_xml_column FROM my_table 

比方说，它返回我下面的XML

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

我想获得的

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

换句话说，我怎么能让SQL Server告诉我我的XML的结构？

我可以做以下，以获得个人elemtns的所有名称：

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

也许，如果有一个相当于“本地名（）”返回的元素的全路径会诀窍吗？

Answer 1

您可以使用XQuery干净做到这一点和递归CTE（无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) 

它并不真正做太多的XQuery法宝，但至少它是所有在线，不需要任何存储过程，特殊权限等

Answer 2

我怀疑是SQL Server的XQuery实现是达不到这个任务，但是这是做的另一种方式（通过this的启发，根据需要调整）：

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 3

UDF你.....

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE 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