question

I would like to add a word of caution regarding importing the contents of xml documents into varchar(max) field. If you do have some sort of front end code which uses XmlReader to read the contents of a given document, then you will be inevitably subjected to ask the reader to ReadOuterXml() and feed the resulting string as a value of the respective procedure parameter. If the size of your document goes to 100s MB then XmlReader might throw **out of memory exception**. In a mean time, if you instead opt to use the path to the xml file then there is no limit, and the speed of execution is considerably faster, i.e.

declare @xml xml;

select 
    @xml = t.BulkColumn
    from openrowset(bulk 'some_unc_path', single_blob) t;

update dbo.YourTable
set
    YourColumn = @xml -- cast(@xml as varchar(max))
    where some_condition_is_met;

Microsoft strongly recommends using single_blob when populating xml variables (it is much more efficient as it matches how xml is stored internally). Using xml instead of varchar(max) will allow you to better utilize a full text indexing if needed. It also goes without saying that as William already noted, native xml is easy to process.

Excellent point Oleg +1 - I was hoping you would add something to this question, you seem to be the resident XML doctor, and as usual have provided a great explanation.