Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 201
d4nt Avatar asked Feb 15 '10 13:02

d4nt


People also ask

How do I get data from XML format in SQL Server?

SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.

How do I select a specific XML node in SQL Server?

You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.

Which technique can you use to read XML from SQL Server?

Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.


1 Answers

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.

like image 86
Aaronaught Avatar answered Oct 13 '22 01:10

Aaronaught