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?
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With