https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type

 The nodes() method is useful when you want to shred an xml data type instance into relational data.

It allows you to identify nodes that will be mapped into a new row.

Every xml data type instance has an implicitly provided context node.

For the XML instance stored in a column or variable, this is the document node.

The document node is the implicit node at the top of every xml data type instance.

The result of the nodes() method is a rowset that contains logical copies of the original XML instances.

In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.

You can retrieve multiple values from the rowset.

For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance.

Note that the value() method, when applied to the XML instance, returns only one value.  

Syntax

nodes (XQuery) as Table(Column)  

Arguments

XQuery
Is a string literal, an XQuery expression.

If the query expression constructs nodes, these constructed nodes are exposed in the resulting rowset.

If the query expression results in an empty sequence, the rowset will be empty.

If the query expression statically results in a sequence that contains atomic values instead of nodes, a static error is raised.

Table(Column)
Is the table name and the column name for the resulting rowset.  

Example

 DECLARE @UsedRecords XML;SET @UsedRecords = ‘<Record ID="107" /><Record ID="116" /><Record ID="410" />‘;  SELECT Result.Id.value(                                                                         ‘@ID‘ ,                                                                         ‘int‘                                                                     )                                                  FROM   @UsedRecords.nodes(‘/Record‘) AS Result(Id)

nodes() Method (xml Data Type)