Converting XML Nodes to Table in SQL Server

Recently I’ve faced with a task to extract client names and phone numbers from XML document and return a table like a standard SELECT query. This can be made with a nodes() method.

This method shreds the XML document and extracts nodes from it. The description is available on MSDN. Let’s look at the example how it works.

The nodes() method extracts Client node from XML and makes 2 rows from which we can get values.
Simple XML shred
We can also extract phone numbers from Phone node. In this case we should use common table expression (CTE) to extract Client nodes, then we can shred the Phone nodes.

The result is shown below.
Nested XML shred

Leave a Reply

Your email address will not be published. Required fields are marked *