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?
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>'
