This article shows how to list all nodes from an XML column in SQL Server.
In SQL Server, you can use the built-in XML functions to query and manipulate XML data stored in XML columns. One of the common tasks when working with XML data is to list all the nodes in an XML document. Here's how you can do it:

Example

Assuming you have an XML column named xml_data in a table named my_table, you can use the nodes() method along with the XPath expression //* to list all the nodes in the XML document:

SELECT xml_node.value('local-name(.)', 'VARCHAR(MAX)') AS node_name
FROM my_table
CROSS APPLY xml_data.nodes('//*') AS xml_table(xml_node)

Let's break down the above query:

CROSS APPLY xml_data.nodes('//*') AS xml_table(xml_node) - This line uses the nodes() method to shred the XML data into a table named xml_table, where each row represents a node in the XML document. The //* XPath expression selects all nodes in the XML document.

xml_node.value('local-name(.)', 'VARCHAR(MAX)') - This line extracts the local name of each node using the value() method. The local-name() function returns the local name of the current node, which is the node name without the namespace prefix.

AS node_name - This line renames the extracted node names as node_name in the result set.

The output of the above query would be a list of all the node names in the XML document, one node name per row. Note that the query doesn't return the text or attribute values of the nodes, just their names. If you want to include the text or attribute values in the result set, you can modify the value() method accordingly.

Count XML nodes

To count the number of nodes in an XML document stored in an XML column in SQL Server, you can use the nodes() method along with the COUNT function. Here's how you can do it:

SELECT COUNT(*) AS node_count
FROM my_table
CROSS APPLY xml_data.nodes('//*') AS xml_table(xml_node)

COUNT(*) - This line counts the number of rows in the xml_table table, which is equal to the number of nodes in the XML document.

The output of the above query would be a single row with the number of nodes in the XML document.