Geek asks and answers

How to convert an XML field with more than 8000 characters to a string?

I have a SQL Server type column XMLcontaining some records with more than 8000 characters.

I would like to convert this column to varchar.

I'm not interested in truncation (the first 8000 characters are fine).

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

Target string size too small to represent XML instance

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

String or binary data will be truncated

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

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

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

Explicit conversion from xml data type to text is not allowed

Is there any workaround?

+5
3

varchar(max) . , , . , / varchar(8000).

+8

, , xml varchar. , XML-, NVARCHAR. CONVERT CAST . , MAX.

+3

I never had this special need, but I would try a different way:

SUBSTRING(CAST(Content AS VARCHAR), 1, 8000)
+1
source

More articles:


All Articles