如何从 SQL Server 中的 XML 值获取元素名称列表 [英] How can I get a list of element names from an XML value in SQL Server

问题描述

我在 SQL Server 2k8 中有一个带有 XML 列的表.以下 SQL 检索一些 XML:

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

假设它返回给我以下 XML

Let's say it returns me the following XML

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

我想要的是

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

换句话说，如何让 SQL Server 告诉我 XML 的结构?

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 )

也许是否有一个等效于local-name()"的返回元素的完整路径的方法?

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

推荐答案

您可以使用 XQuery 和递归 CTE(无 OPENXML)干净利落地做到这一点:

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)

它并没有真正发挥 XQuery 的魔力，但至少它都是内联的，不需要任何存储过程、特殊权限等.

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

这篇关于如何从 SQL Server 中的 XML 值获取元素名称列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！