I recently ran into an error for a query that was casting XML to VARCHAR to search for a value. I have a bit of familiarity with the XML for these types of queries, so I was surprised when running the SELECT statement caused this error, which I had never seen before:
Msg 6355 “Conversion of one or more characters from XML to target collation impossible”
I figured something was strange with the XML data being queried, and I was kind of right. In the end, the “fix” wasn’t with the XML but was as simple as adding a letter to the query. But before checking out the fix, let’s take a look at a basic example demonstrating how to get around the error.
Something Changed
We’ll start with a test table called ErrorTest:
CREATE TABLE ErrorTest (
Id INT IDENTITY(1, 1) PRIMARY KEY,
Err_Msg XML
);
GO
Let’s insert a dummy record that we can try to query. For this example, I needed a unicode character in the XML value, so I checked here and decided to go with the sunny looking “Combining Cyrillic Millions Sign” which is U+0489:
INSERT INTO ErrorTest
VALUES ('<Example>' + NCHAR(0X489) + '</Example>');
Mission Impossible
Now, let’s see if we can cast our XML and search for a particular value:
SELECT Id from ErrorTest
WHERE CAST(Err_Msg AS VARCHAR(MAX)) like '%Example%';
GO
And there’s the error. I said above that to get the query to complete successfully, I needed to add a letter. Let’s do that by changing VARCHAR to NVARCHAR and see what we get:
SELECT Id from ErrorTest
WHERE CAST(Err_Msg AS NVARCHAR(MAX)) like '%Example%';
GO
We get a result successfully.
VARCHAR vs. NVARCHAR
Some unicode characters found their way into the XML, which was causing an error when casting the XML to VARCHAR. If we cast the XML to NVARCHAR instead, there was no error for an impossible conversion.
Keep in mind there are plenty of other ways to search XML in SQL Server, but this was similar to the scenario I ran into and a quick way to show the before and after of switching VARCHAR to NVARCHAR.
Thanks for reading!
Chad Callihan 