CODEWITHSUNDEEP
sql-serverxmlstored-procedures
koumides
koumides

Reputation: 2504

Stored Procedure doesn't like utf-16

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?

Upvotes: 3

Views: 1334

Answers (1)

Greg Beech
Greg Beech

Reputation: 136657

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

Upvotes: 3

Related Questions