X-Men Code 127

One of the more interesting things about SQL Server 2005 is the support for the XML datatype. Instead of playing around with wierd and funky stored procedures to manipulate XML, you get validation and a bunch of other features just by specifying a field or a parameter as XML.
Now, as nice as it is to be able to serialize a .net object and store the entire thing in one field in the database, I have my feelings that there is significant overhead to querying into that XML on a more than ad-hoc basis. So, you *might* want to add some additional fields to the table containing the xml. For example if you had the following XML:

<OrderID>0a4fc0f8-6106-467f-ae10-45dcbfe4cefa</OrderID>
<CustomerNumber>1234</CustomerNumber>
<Status>Active</Status><Lines>
<OrderLine ItemId="123" Quantity="2" Taxable="false" Price="12.34"
DateChanged="2006-04-20T12:16:20.8942144-06:00"
IsReadOnly="false"></OrderLine>
<OrderLine ItemId="234" Quantity="2" Taxable="false" Price="56.78"
DateChanged="2006-04-20T12:16:20.8942144-06:00"
IsReadOnly="false"></OrderLine>
</Lines>
<DateChanged>2006-04-20T12:16:20.8942144-06:00</DateChanged></Order>

You *might* want to have the top-level elements like "DateChanged" "CustomerNumber" and "OrderID" in redundant fields. Your data table might look like this:

OrderID UniqueIdentifier
DateChanged DateTime
CustomerNumber Int
OrderXML xml

If you did this, you could create an Insert/Update trigger on the table that would automatically update the (non-key) fields (Thanks Scott!) that used the built-in XML features to update the duplicate fields whenever the xml (or any other field) was updated.

ALTER TRIGGER [OrderXML_InsUpd]
ON [dbo].[OrderXML]
AFTER INSERT,UPDATE AS BEGIN
SET NOCOUNT ON;
UPDATE [MWI].[dbo].[OrderXML]
SET
[CustomerNumber] = INSERTED.XML.value('(//CustomerNumber)[1]','Int'),
[DateChanged] = INSERTED.XML.value('xs:dateTime((//DateChanged)1])','DateTime'),
[Status] = INSERTED.XML.value('(//Status)[1]','nVarchar(50)')
FROM INSERTED
WHERE [OrderXML].OrderId = INSERTED.OrderId
END

Anyway, the whole thing is interesting, but pay close attention to the line that moves the date field into the DateTime column of the database table.

[DateChanged] = INSERTED.XML.value('xs:dateTime((//DateChanged)1])','DateTime')

The key to importing a .NET DateTime field into SQL server is to make sure to use the XML to cast the field as dateTime before SQL server gets it. Of course, this may not be necessary if you have a schema associated with the field that is already strongly typed in the XML.