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?
You can do this cleanly with XQuery and a recursive CTE (no
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.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key