Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.