Talk to the Experts

In the last post, I discussed XMLNAMESPACES in the XQuery for the Non-Expert series.  With this post, we’ll be looking into the nodes() method.

Where XMLNAMESPACES is the map through the XML document, the nodes() method is the browser that will help you navigate through the XML document.

Since the data in an XML document isn’t exactly visually appealing, the nodes() method will help you shred your XML documents.  By doing this, elements nested into the XML document can be extracted and returned as rows.

Nodes Power

Let’s quickly demonstrate what can be done with the nodes() method.  Suppose you had the following XML document:

<Building type="skyscraper">Sears Tower
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Floor level="3.1" />
</Floor>
</Building>

This XML document could be shredded to return two different result sets based on the Floor element.  First, we could look for all Floor elements that are under the Building element.  This would look like this:

Alternatively, the XML document could be shredded to return all Floor elements from the XML document.  This output would look like so:

Nodes Syntax

Now that we’ve seen how the nodes() method can affect an XML document, we should look at how to leverage this.  First, here is the syntax for the nodes() method.

nodes (XQuery) as Table_Alias(Column_Alias)

Seeing the syntax doesn’t explain how to use it so we’ll dissect it a little here:

• nodes – The name of the method.  This should always be lower case.  One of the first things I look at when people have bad query is this because it’s a common issue.
• XQuery – The XQuery statement that directs the nodes method how to shred the XML document.  There are a few ways to handle this; which will be covered in some demonstrations below.  The key thing to remember here is that XQuery statement is very much like a UNC path.  If you look at the elements in an XML document as though they are folders in a file system, the XQuery statement will be the path for browsing the document.
• Table_Alias – The table alias for the XML fragment shredded out of the XML document.  There isn’t a lot of use for this alias, unless your column alias matches another column in a table that is included in the query.
• Column_Alias - The column alias for the XML fragment shredded out of the XML document.  When the XML fragment is shredded the information can be referenced from this column.

The nodes() method is called as a method off of the column that has the XML data.  When it is called, it will often be called using an APPLY operator so that multiple XML documents can be queried at once.

Nodes Setup

With the syntax above, let’s build a couple examples to demonstrate how to get the outputs from the first section.  To start, we’ll build a table and place our sample XML document into it.

IF OBJECT_ID('tempdb..#NodesExample') IS NOT NULL
DROP TABLE #NodesExample
GO
CREATE TABLE #NodesExample
(
XMLDocument xml
)
INSERT INTO #NodesExample
VALUES ('<Building type="skyscraper">Sears Tower
<Floor level="1" />
<Floor level="2" />
<Floor level="3">
<Floor level="3.1" />
</Floor>
</Building>')
SELECT XMLDocument FROM #NodesExample

Before we begin shredding the XML document, let’s expand on the UNC and directory analogy above.  If the sample XML document was a folder structure it would look something like the following:

To browse through to this folder structure to the Floor folder, the UNC path would look like this “\\XMLDocument\Building\Floor”.  Since the XMLDocument is the column name, that will be dropped and the UNC path is changed to “\Building\Floor”

For simplicity, let’s make the Table_Alias and Column_Alias values t and c, respectively.  These aliases can be anything that makes sense for the query.  Since these will just be some examples, we’ll just keep it simple.

Nodes Example

At the beginning, two sample result sets were displayed.  Both of these could be returned from the XML document in the sample script above.  In the next couple scripts, we’ll demonstrate how to do this.

For the first result set we need to shred the XML document to return every Floor element off of every Building element.  Use the UNC path from above, with forward instead of back slashes, for the XQuery statement.  We’ll start the XQuery statement with a single forward slash; this tells the nodes() method to begin at the start of the XML document or fragment.  Then add in the alias components and the XQuery should be the following:

SELECT c.query('.')
FROM #NodesExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

Just ignore the query() method for now, that will be covered later.  Executing this query will return the result set defined above.  I won’t include the picture of the result set again, you can run it and see for yourself if it’s the same.

For the second result set, we need to find all of the Floor elements in the XML document.  Using a concept similar to the UNC pathing above, the nodes() method can be used to search an XML document for all “folder”, or elements named Floor.

To accomplish this, prefix the Floor element name with two forward slashes.  This tells the nodes method to return all elements with this element name.  The query for this will look like the following:

SELECT c.query('.')
FROM #NodesExample
CROSS APPLY XMLDocument.nodes('//Floor') as t(c)

Execute this query and the results will match the second result set above.  Go ahead, run it now so you can verify the results.

Nodes Wrap-Up

There is one last thing to mention about the nodes() method.  The XQuery pathing isn’t limit to a single folder or two or three folders.  Use this to dig as deep as you need in your XML documents.

This post didn’t go into CROSS APPLY and OUTER APPLY operators.  As I mentioned, you will need to be familiar with these when writing XQueries.  Take some time to learn how to use them if you haven’t had experience with them before.

If anything in this post needs further clarity, please comment below.  I expect that as time goes on I will update and expand this post as I learn more about how to leverage the nodes() method.

Related posts:

1. XQuery for the Non-Expert – Terminology
2. XQuery for the Non-Expert – XMLNAMESPACE
3. Finding Missing Values from an Array