SQL Server error: Conversion of XML characters

I hit an interesting error in SQL Server today. A user had somehow entered a unicode character that could not be converted to ASCII. In a stored procedure, the XML column was being converted to a varchar and this error occurred:

"Conversion of one or more characters from XML to target collation impossible"

You can recreate the scenario with the following TSQL code:

DECLARE @xml xml, @nvc nvarchar(64)
SELECT @nvc = 'Coptic letter: ' + nchar(997) + ''
SELECT @nvc as 'before conversion'
SELECT @xml = @nvc
SELECT convert(varchar, @xml) -- fails with conversion error

The conversion from XML -> varchar fails because the Unicode value could not be represented. There are a couple options here. You can use nvarchar instead of varchar. This would support the unicode value:

SELECT convert(nvarchar, @xml) as 'xml -> nvarchar conversion'

You can also convert it to nvarchar and then to varchar. This results in any non-mappable characters being changed to a question mark, like so:

SELECT convert(varchar, convert(nvarchar, @xml)) as 'xml -> nvarchar -> varchar conversion'

In my particular situation I added some code to clean up the data before it is sent to the database. I specified that the stream reader that was producing the data to be inserted into the XML column should use the ASCII encoding. This results in the non-mappable characters being translated to question marks before it goes to the database. That is just what I did in this particular case. It may not apply in your situation.