My Instagram feed

My Instagram feed

2008-04-25

.NET 2.0 transaction model

How to handle transactions using ASP.NET 2.0 and SqlClient. Nice article here.

2008-04-07

Serialization of IDictionary objects

By design, objects that implement IDictionary (Hashtable, SortedList, ListDictionary, or HybridDictionary) cannot be serialized. This Q&A describes a way of making these objects serializable by using (a "hidden hook"), and implementing IXmlSerializable: http://msdn2.microsoft.com/en-us/magazine/cc164135.aspx

2008-04-03

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.