X-Men Code 127
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:
<OrderLine ItemId="123" Quantity="2" Taxable="false" Price="12.34"
<OrderLine ItemId="234" Quantity="2" Taxable="false" Price="56.78"
You *might* want to have the top-level elements like "DateChanged" "CustomerNumber" and "OrderID" in redundant fields. Your data table might look like this:
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]
AFTER INSERT,UPDATE AS BEGIN
SET NOCOUNT ON;
[CustomerNumber] = INSERTED.XML.value('(//CustomerNumber)','Int'),
[DateChanged] = INSERTED.XML.value('xs:dateTime((//DateChanged)1])','DateTime'),
[Status] = INSERTED.XML.value('(//Status)','nVarchar(50)')
WHERE [OrderXML].OrderId = INSERTED.OrderId
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.