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

marc_s · Accepted Answer · 2009-10-15 15:12:41Z

2

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

answered Oct 15, 2009 at 15:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

littlechris Over a year ago

Thanks Marc. How to you store/extract your object in the column? Serialise/Deserialise methods? Maybe put xml attributes in my partial class?

marc_s Over a year ago

In my case, I simply serialize the object in question to XML and store that. Works fine. I wouldn't store that in your object - but provide a method on the class to render the class contents as XML (return a string)

Collectives™ on Stack Overflow

Correct Use of XML datatype in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related