Stored Procedure doesn't like utf-16

Question

All,

I have a stored procedure on SQL Server 2005 that accepts an XML argument. When I execute:

exec PutResultsOnDb '<?xml version="1.0" encoding="utf-16"?><loads of Xml data>'

I get the error: XML parsing: line 1, character 39, unable to switch the encoding

However when I do

exec PutResultsOnDb '<?xml version="1.0" encoding="utf-8"?><loads of Xml data>'

It works perfectly fine.

Any ideas?

Answer 1

The first case fails because you're declaring that you have UTF-16 encoding XML in an ASCII string. The second case most likely works because you don't have any characters above 127 and so UTF-8 is indistinguishable from ASCII.

If you want to declare the XML as UTF-16, you need to declare the string as UCS-2 (which is mostly compatible) by using an N prefix, e.g. the following should work:

exec PutResultsOnDb N'<?xml version="1.0" encoding="utf-16"?><loads of Xml data>'