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

by

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("https://stackoverflow.com/" + 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 + "https://stackoverflow.com/" + 
            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.

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. Unpivot with column name
  3. How can I delete using INNER JOIN with SQL Server?
  4. What this query does to create comma delimited list SQL Server?
  5. SQL Server – Return value after INSERT
  6. SQL NVARCHAR and VARCHAR Limits
  7. Convert varchar into datetime in SQL Server
  8. sql query to return differences between two tables
  9. Check if a row exists, otherwise insert
  10. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  11. Convert Xml to Table SQL Server
  12. How to select the last record of a table in SQL?
  13. How to create table using select query in SQL Server?
  14. How do I remove redundant namespace in nested query when using FOR XML PATH
  15. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  16. How to write UPDATE SQL with Table alias in SQL Server 2008?
  17. SQL Server 2008- Get table constraints
  18. SQL SERVER: Get total days between two dates
  19. Restrict an SQL Server connection to a specific IP address
  20. Execute Dynamic Query with go in sql
  21. TransactSQL to run another TransactSQL script
  22. Store multiple bit values in a single table column
  23. SQL Server equivalent to MySQL enum data type?
  24. UPDATE and REPLACE part of a string
  25. Improve SQL Server query performance on large tables
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. How to execute a stored procedure inside a select query
  28. Using CASE Statement inside IN Clause
  29. Single or multiple databases
  30. SQL Unpivot multiple columns Data

Leave a Comment