如何从 SQL Server 中的 XML 值获取元素名称列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2266132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get a list of element names from an XML value in SQL Server
提问by d4nt
I have a table with an XML column in SQL Server 2k8. The following SQL retrieves some XML:
我在 SQL Server 2k8 中有一个带有 XML 列的表。以下 SQL 检索一些 XML：
SELECT TOP 1 my_xml_column FROM my_table
Let's say it returns me the following XML
假设它返回给我以下 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?
换句话说，如何让 SQL Server 告诉我 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?
也许如果有一个等效于“local-name()”的返回元素的整个路径的方法？
回答by Aaronaught
You can do this cleanly with XQuery and a recursive CTE (no OPENXML):
您可以使用 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)
It's not really doing much XQuery magic, but at least it's all inline, doesn't require any stored procedures, special permissions, etc.
它实际上并没有做多少 XQuery 魔法，但至少它都是内联的，不需要任何存储过程、特殊权限等。
回答by Joe
UDF for you.....
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
回答by Adam
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):
我怀疑是SQL Server的XQuery实现是达不到这个任务，但是这是做的另一种方式（灵感此，根据需要调整）：
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
