SQL Server: variable length XML storage

Question

I'm in the process of writing an application that interacts with a third party application.

The third party application will be passing my application several raw XML requests. I would like to save each of these requests in a communications log in my DB.

What's the most efficient way to store this variable-length data? VARCHAR(MAX)? NVARCHAR(MAX)?

If one is a better choice than the other (or there is another option I'm missing), please explain why it's the best choice.

Answer 1

Since you're using SQL Server 2K5 the best data type to store XML data is xml.

This provides parsing and schema validation features. It also allows you to index the XML data later if need be.

Answer 2

XML seams to be the obvious data type of choice when dealing with XML but not always.

Have a look at this article by Robert Sheldon. Working with the XML Data Type in SQL

In some cases, you shouldn’t use the XML data type, but instead use large object storage—VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX). For example, if you simply store your XML documents in the database and retrieve and update those documents as a whole—that is, if you never need to query or modify the individual XML components—you should consider using one of the large object data types. The same goes for XML files that you want to preserve in their original form, such as legal documents. If you need to retain an exact textual copy, use large object storage.