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?
3条答案
按热度按时间rjjhvcjd1#
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 datatypevarchar(8000).idfiyjo82#
The issue that you are running into has to do with attempting to convert the
XMLintoVARCHAR. I have run into a similar issue before when trying to convert anXMLstring that is much smaller than yours intoNVARCHAR. Switching from aCONVERTto aCASTshould solve your problem. As far as the size, you are better off just setting it toMAX.6jygbczu3#
I have never had this particular need, but another way I would try would be:
This will obviously truncate the content, so you should be aware of that if that's not appropriate for your use case.