Designing table to store XML data in SQL Server 2008 R2

Question

I am planing to use SQL Server to store XML BLOBs for my application. I am struggling with a design decision, and looking for any guidelines or advice from someone experienced in this topic.

The data that needs to be stored as XML has about 100 simple data points. They can easily be categorized into groups of maybe 20 data points each. In future versions of application, we plan to increase the scope of the data by adding new data points, some of which will be hierarchical (lists, dictionaries, etc).

We are not anticipating needing to perform queries on the XML data. At most they will be very simple queries and we can promote any of the data points to a relational column if need be.

I am not sure whether I should just create one giant XML BLOB to hold all of this data, or if should break it down into multiple XML columns. Are there any best practices or guidelines for dealing with XML data type in SQL Server 2008 R2 that can help me make the best decision? Does it even matter?

EDIT: I am already set on using XML as a datatype, I am trying to make a decision on whether I should use one large BLOB or break it up into multiple XML columns.

Answer 1

Yes it matters! When you store a large XML blob as XML datatype inside SQL Server, then it's not stored as a textual blob - it's "parsed" and "tokenized" and stored in a significantly more efficient manner than if you're using just varchar(max) to store the textual representation.

If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype!

Update: if you only intend to store and retrieve the XML as a whole - I don't see any benefit in breaking it up into chunks. The XML datatype in SQL Server can hold up to 2 GByte of data (just like varchar(max)) and you won't see any performance gains from storing (and retrieving) multiple smaller XML fragments.