Document versioning

Question

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. Let's say a add a new node. What I want to keep is the edited document (last version of the document) and the modifications, so I can revert any time I want to a previous version of the same document.

Where is the best place to compare the version I edited (which will be the last version if I save it) with the previous version (the last version stored in the DB before begining to edit it) in order to store the differences between these two? At the server side or can I do it somehow at the DB level? Can you provide some guidelines or best practices for this scenario?

Thanks!

Answer 1

The simplest way is to just store previous versions of the whole document rather than attempting to calculate deltas. Lots of systems do it that way even though the XML fields use a lot of space. It might be worth evaluating the data storage requirements for doing this before you go to whole lot of trouble to capture differences.

Answer 2

After each edit you need to insert a new record in a history file, together with a date and/or a version number field.

Answer 3

As the previous colleagues say, I believe that you need to either store in separate historical tables all the versions of the XML files or to enable Change Data Capture at the db level.

You might also enable FILESTREAM for storing files outside the db, but I'd guess that you'd better store them in place and enable data compression. Though I never tried if it works with XML columns :-).