Correct Use of XML datatype in SQL Server?

Question

I have an application that allows my users to make temporary changes to an ongoing booking. I have recenlty read about the new XML datatype in SQL server, and the corresponding SqlXml type.

I am thinking of using the new type to store the previous values of the object so that I can later revert to them.

Something like:

Booking Table:

BookingID int
SomeField1 String
SomeField2 String
SomeField3 String
RevertValues XML
RevertDateTime DateTime2

This is a cut down version of my table, but I plan to store the current values in the ReverValues column and then revert when the RevertDatetime is reached.

My application uses a SQL Server 2008 database, Linq-to-SQL model, with a MVC front end.

Any and all advice would be appreciated as I've never done anything like this before!

Thanks

Answer 1

Sounds like a good idea to me - I'm doing similar things in SQL Server auditing where I keep audit entries in a table, along with two XML fields - ValuesBefore and ValuesAfter - to document and track changes.

What you might want to do is split out the "RevertValues" into a separate table that has a foreign key relationship to your bookings, so that you could keep track of several revisions of the booking - something like a BookingHistory table:

BookingHistory

BookingID            INT   (FK to Booking table)
DateOfModification   DATETIME
BookingData          XML

or something along those lines.

Marc