There are several ways of storing XML documents in SQL Server table.
- One way is to use a table with datatype varchar(max) or nvarchar(max).
- another option is to use XML data type column
- or you can use varbinary(max)
- if you are using SQL 2012, then there is yet another option: use filetable and filestream
Let’s look quickly at these options:
The char datatypes are not great for XML because they do not give any protection against faulty XML and even worse – there can be some problems with the encoding.
The char datatypes inherit the encoding page from the collation (none are UTF-8, for example) and this may conflict with the encoding of the XML on a document level (windows-1252, UTF-8 etc). In the case of nvarchar(max) there is UTF-16 encoding, but this is still not good enough.
The XML datatype is used when we would like to enforce XML consistency on the database side and when we expect to work with the XML data later on.
If we are just storing the XML in a database and we do not expect to run queries very often and if we do not insist on 100% consisten XML, then the best way to store the XML is varbinary(max). It does not have encoding and this way the XML is not in conflict with the collation.
The great new thing in SQL 2012 is the filetable functionality. It really performs great, Full-text search can be introduced to search through the XML documents, it does not get affected by collation.
If you would like to read more about Filetable in 2012, check out my article on Simple-talk.