Query XML column in a table as record set in SQL Server 2005 and above

Since SQL Server 2005 running select queries on XML columns had become lot easier. SQL Server 2005 and 2008 introduced several XML functions which can be used to extract XML data as record set and even apply filters on them. Following are the four XML data type methods which takes XQuery expression to either return part of XML or value.

1. query() – Returns part of XML from XML data
2. value() – Returns value of XML element of attribute
3. exists() – Returns value 1 or 0 if a XQuery expression evaluates non-null node list
4. nodes() – Returns dataset result from the XQuery expression it takes. This method is used with value() and exist()

I’m going to use here nodes() and value() methods to query XML column within CustomerOrderHistory table. This table contains OrderHistory XML data type column which contains historical customer order details. Here is how the table structure looks like and data within this table

CREATE TABLE CustomerOrderHistory
    (
      CustomerId INT IDENTITY(1, 1)
                     PRIMARY KEY CLUSTERED,
      CustomerName VARCHAR(50),
      OrderHistory XML
    )
GO

INSERT  INTO CustomerOrderHistory
        (
          CustomerName,
          OrderHistory
        )
VALUES  (
          'William Strong',
          '<Orders>
				<Order Id="1010" OrderDate="11-05-2009" PaymentMethod="CC">
					<OrderDetails>
						<OrderItem ProductId="102030" Quantity="20" />
						<OrderItem ProductId="102230" Quantity="10" />
						<OrderItem ProductId="102045" Quantity="4" />
					</OrderDetails>
				</Order>
				<Order Id="2312" OrderDate="11-15-2009" PaymentMethod="DC">
					<OrderDetails>
						<OrderItem ProductId="102343" Quantity="25" />
						<OrderItem ProductId="102233" Quantity="15" />
						<OrderItem ProductId="102445" Quantity="42" />
					</OrderDetails>
				</Order>
			</Orders>' 
        ),
        (
          'Andy Clarke',
          '<Orders>
				<Order Id="1232" OrderDate="11-05-2009" PaymentMethod="CC">
					<OrderDetails>
						<OrderItem ProductId="123231" Quantity="34" />
						<OrderItem ProductId="123211" Quantity="12" />
						<OrderItem ProductId="454345" Quantity="64" />
					</OrderDetails>
				</Order>
				<Order Id="3424" OrderDate="11-15-2009" PaymentMethod="Cash">
					<OrderDetails>
						<OrderItem ProductId="542342" Quantity="15" />
						<OrderItem ProductId="234234" Quantity="65" />
						<OrderItem ProductId="234222" Quantity="62" />
					</OrderDetails>
				</Order>
			</Orders>' 
        )
GO
							
SELECT  CustomerId,
        CustomerName,
        OrderHistory
FROM    CustomerOrderHistory
GO

I’ve now created a table-valued function which takes XML data type parameter and returns all order history from XML as data set.

IF OBJECT_ID(N'dbo.FN00_GetOrderHistory', N'TF') IS NOT NULL
    DROP FUNCTION dbo.FN00_GetOrderHistory
GO

CREATE FUNCTION FN00_GetOrderHistory
(
	-- Add the parameters for the function here
	@OrderHistoryXML XML
)
RETURNS 
@OrderHistory TABLE 
(
	-- Add the column definitions for the TABLE variable here
	OrderId INT,
	OrderDate DATETIME,
	PaymentMethod VARCHAR(10),
	ProductId INT,
	Quantity INT
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	INSERT INTO @OrderHistory
	SELECT R.nref.value('@Id', 'INT') AS OrderId,
		   R.nref.value('@OrderDate', 'DateTime') AS OrderDate,
		   R.nref.value('@PaymentMethod', 'Varchar(10)') AS PaymentMethod,
		   T.sref.value('@ProductId', 'INT') AS ProductId,
		   T.sref.value('@Quantity', 'INT') AS Quantity		 
	FROM @OrderHistoryXML.nodes('//Orders/Order') R(nref)
	CROSS APPLY R.nref.nodes('//OrderDetails/OrderItem') T(sref)
	
	RETURN
END
GO

In this function we take XQuery expression from the root (//Orders/Order) and pass it to nodes(). We also want to get all order items and inner join it with the orders so I have used CROSS APPLY to get all order items elements from the current root.

This function can now simply used when selecting rows from CustomerOrderHistory table as follows:

SELECT  CustomerId,
        CustomerName,
        b.*
FROM    CustomerOrderHistory a
CROSS APPLY dbo.FN00_GetOrderHistory(OrderHistory) b

This query returns us results as expected: