SQL Server XML Conversion Error: “Conversion of one or more characters from XML to target collation impossible”

A quick and short post this time regarding a sql server error being thrown when you try to convert xml characters. XML column has an unicode character that can not be converted into ASCII, thus our engine is not happy about it.

Let’s say you have a “Table1” which stores a “XMLColumn” and you’d like to query on that column with LIKE operator. Hence, you convert it to varchar data type first.

SELECT XMLColumn FROM Table1
WHERE Convert(varchar(max), XMLColumn) IS LIKE %ARollingStone%

Then, you’ll get below error:

Msg 6355, Level 16, State 1, Line 2
Conversion of one or more characters from XML to target collation impossible

To fix this, you need to simply change varchar to nvarchar, that’s it.

SELECT XMLColumn FROM Table1
WHERE Convert(nvarchar(max), XMLColumn) IS LIKE %ARollingStone%