How do I convert an XML field with more than 8000 characters into a string?

Question

I have a SQL Server column of type XML containing some records with more than 8000 characters.

I would like to convert this column into a varchar.

I am not concerned about truncation (the first 8000 characters is fine).

However, whenever I try CONVERT(varchar(8000), Content) I get an error:

Target string size is too small to represent the XML instance

When I try CONVERT(varchar(MAX), Content) I get an error:

String or binary data would be truncated

When I try CONVERT(varchar(20000), Content) I get an error:

The size (20000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000)

When I try CONVERT(text, Content) I get an error:

Explicit conversion from data type xml to text is not allowed

Is there a workaround?

Answer 1

Cast to varchar(max) should work just fine. You probably have an issue elsewhere. You would get that error if you try to insert/update a column with datatype varchar(8000).

Answer 2

The issue that you are running into has to do with attempting to convert the xml into varchar. I have run into a similar issue before when trying to convert an XML string that is much smaller than yours into NVARCHAR. Switching from a CONVERT to a CAST should solve your problem. As far as the size you are better off just setting it to MAX.