I was successful with this method today. It's similar to the other answers in that it also converts the contents to XML, just using a different method. As I didn't see FOR XML PATH
mentioned amongst the answers, I thought I'd add it for completeness:
SELECT [COL_NVARCHAR_MAX]
FROM [SOME_TABLE]
FOR XML PATH(''), ROOT('ROOT')
This will deliver a valid XML containing the contents of all rows, nested in an outer <ROOT></ROOT>
element. The contents of the individual rows will each be contained within an element that, for this example, is called <COL_NVARCHAR_MAX>
. The name of that can be changed using an alias via AS
.
Special characters like &
, <
or >
or similar will be converted to their respective entities. So you may have to convert <
, >
and &
back to their original character, depending on what you need to do with the result.
EDIT
I just realized that CDATA
can be specified using FOR XML
too. I find it a bit cumbersome though. This would do it:
SELECT 1 as tag, 0 as parent, [COL_NVARCHAR_MAX] as [COL_NVARCHAR_MAX!1!!CDATA]
FROM [SOME_TABLE]
FOR XML EXPLICIT, ROOT('ROOT')