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:

Invoking WCF method Asynchronously and Signalling calling Thread

When calling WCF service method which takes longer to complete often you would require to have WCF client to be responsive while calling the method. Visual Studio allows developers to generate proxy for calling WCF service by adding service references. When generating service proxy Visual Studio also allows you to generate asynchronous method stubs to call WCF service method asynchronously.

Proxy creates two types of asynchronous methods stubs for clients to use. Consider following simple WCF calculator service:

	
[ServiceContract]
public interface ICalculatorService
{
	[OperationContract]
	float Add(float p1, float p2);

	[OperationContract]
	float Substract(float p1, float p2);
}

public class CalculatorService : ICalculatorService
{
	#region ICalculatorService Members

	public float Add(float p1, float p2)
	{
		Console.WriteLine("Adding {0} + {1}", p1, p2);

		return p1 + p2;
	}

	public float Substract(float p1, float p2)
	{
		Console.WriteLine("Substracting {0} - {1}", p1, p2);

		Thread.Sleep(5000);

		return p1 - p2;
	}

	#endregion
}

At the client side the proxy generates couple of types of asynchronous method stubs. For instance for Add() we get following stubs:

   
public System.IAsyncResult BeginAdd(float p1, float p2, System.AsyncCallback callback, object asyncState) 
        
public float EndAdd(System.IAsyncResult result) 

private void OnAddCompleted(object state) 
        
public void AddAsync(float p1, float p2) 
        
public void AddAsync(float p1, float p2, object userState) 

Many of us are familiar with BeginAdd() and EndAdd() methods. Same functionality can be achieved when using delegates. Similar methods are generated for Subtract(). Now the client can call any of these methods to asynchronously invoke WCF method. AddAsync() is something I found interesting. This method is used along with OnAddCompleted event handler which gets the return value from the method call. Here is how you can use this methods in client side.

        
static WaitHandle[] waits = new WaitHandle[] { new AutoResetEvent(false) };

static void Main(string[] args)
{
    try
    {
	using (CalculatorServiceClient client = new CalculatorServiceClient())
	{
	    Console.WriteLine(Thread.CurrentThread.ManagedThreadId);
	    Console.WriteLine("Calling service methods asynchronously method 2");
	    client.SubstractCompleted += new EventHandler<SubstractCompletedEventArgs>(client_SubstractCompleted);
	    client.SubstractAsync(6, 3, waits[0]);
	    WaitHandle.WaitAll(waits);
	}

	Console.WriteLine("Press <ENTER> to terminate client");
	Console.ReadLine();
    }
    catch (Exception e)
    {
	Console.WriteLine(e.Message);
    }
}

static void client_SubstractCompleted(object sender, SubstractCompletedEventArgs e)
{
    Console.WriteLine("Async Substraction result = {0}", e.Result);
    Console.WriteLine(Thread.CurrentThread.ManagedThreadId);
    ((AutoResetEvent)e.UserState).Set();
}

Now I had to send WaitHandle[] as useState parameter to SubstractAsync() and also call WaitHandle.WaitAll(waits) after that. If we don’t do that client_SubstractCompleted() handler will throw TargetInvocationException(). The reason being SubstractAsync() takes longer to complete and before it completes using block disposes CalculatorServiceClient variable. To handle this situation our calling code needs to wait until the thread where subtract operation is taking place signals main thread about its completion.

For this we have created an array of WaitHandles and sent first array element as userState parameter of SubstractAsync(). The calling code waits until AutoResetEvent to signal main thread by calling Set() inside client_SubstractCompleted() before using block is completed. One thing to note here that client_SubstractCompleted() also executes in a separate thread. Thread.CurrentThread.ManagedThreadId outputs different values for both main thread and thread where subtract operation takes place.