Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
Is there any reason you have to store the versions specifically in sql server? If not, then there are several VCS, e.g. Git and SVN, that are perfectly suited for this kind of work. And they usually come with great diff-tools. –  Andreas Ågren Feb 9 '12 at 13:46
    
Yes, I want to know exactly each file to what user belongs. Each user cand have one or more documents. –  Catalin Feb 10 '12 at 12:23
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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 :-).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.