How to solve "unable to switch the encoding" error when inserting XML into SQL Server

user_79444 0
Viewed 95440

I'm trying to insert into XML column (SQL SERVER 2008 R2), but the server's complaining:

System.Data.SqlClient.SqlException (0x80131904):
XML parsing: line 1, character 39, unable to switch the encoding

I found out that the XML column has to be UTF-16 in order for the insert to succeed.

The code I'm using is:

 XmlSerializer serializer = new XmlSerializer(typeof(MyMessage));
 StringWriter str = new StringWriter();
 serializer.Serialize(str, message);
 string messageToLog = str.ToString();

How can I serialize object to be in UTF-8 string?

EDIT: Ok, sorry for the mixup - the string needs to be in UTF-8. You were right - it's UTF-16 by default, and if I try to insert in UTF-8 it passes. So the question is how to serialize into UTF-8.

Example

This causes errors while trying to insert into SQL Server:

    <?xml version="1.0" encoding="utf-16"?>
    <MyMessage>Teno</MyMessage>

This doesn't:

    <?xml version="1.0" encoding="utf-8"?>
    <MyMessage>Teno</MyMessage>

Update

I figured out when the SQL Server 2008 for its Xml column type needs utf-8, and when utf-16 in encoding property of the xml specification you're trying to insert:

When you want to add utf-8, then add parameters to SQL command like this:

 sqlcmd.Parameters.Add("ParamName", SqlDbType.VarChar).Value = xmlValueToAdd;

If you try to add the xmlValueToAdd with encoding=utf-16 in the previous row it would produce errors in insert. Also, the VarChar means that national characters aren't recognized (they turn out as question marks).

To add utf-16 to db, either use SqlDbType.NVarChar or SqlDbType.Xml in previous example, or just don't specify type at all:

 sqlcmd.Parameters.Add(new SqlParameter("ParamName", xmlValueToAdd));
utf-16 utf-8 xml sql-server .net
8 Answers

@ziesemer's answer (above) is the only fully correct answer to this question and the linked duplicates of this question. However, it could still use a little more explanation and some clarification. Consider this as an extension of @ziesemer's answer.

Even if they produce the desired result, most answers to this question (including the duplicate question) are convoluted and go through many unnecessary steps. The main issue here is the overall lack of understanding regarding how the XML datatype actually works in SQL Server (not surprising given that it isn't well documented). The XML type:

  1. Is a highly optimized (for storage) type that converts the incoming XML into a binary format (which is documented somewhere in the msdn site). The optimizations include:
    1. Converting numbers and dates from string (as they are in the XML) into binary representations IF the element or attribute is tagged with the type info (this might require specifying an XML Schema Collection). Meaning, the number "1234567" is stored as a 4-byte "int" instead of a 14-byte UTF-16 string of 7 digits.
    2. Element and Attribute names are stored in a dictionary and given a numeric ID. That numeric ID is used in the XML tree structure. Meaning, "<ElementName>...</ElementName>" takes up 27 character (i.e. 54 bytes) in string form, but only 11 characters (i.e. 22 bytes) when stored in the XML type. And that is for a single instance of it. Multiple instances take up additional multiples of the 54 bytes. But in the XML type, each instance only takes up the space of that numeric ID, most likely a 4-byte int.
  2. Stores strings as UTF-16 Little Endian, always. This is most likely why the XML declaration is not stored: it is entirely unnecessary as it is always the same since the "Encoding" attribute cannot ever change.
  3. No XML declaration assumes the encoding to be UTF-16, not UTF-8.

  4. Can have 8-bit / non-UTF-16 data passed in. In this case, you need to make sure that the string is not an NVARCHAR string (i.e. not prefixed with an upper-case "N" for literals, not declared as NVARCHAR when dealing with T-SQL variables, and not declared as SqlDbType.NVarChar in .NET). AND, you need to make sure that you do have the XML declaration, and that it specifies the correct encoding.

    PRINT 'VARCHAR / UTF-8:';
DECLARE @XML_VC_8 XML;
SET @XML_VC_8 = '<?xml version="1.0" encoding="utf-8"?><test/>';
PRINT 'Success!'
-- Success!

GO
PRINT '';
PRINT 'NVARCHAR / UTF-8:';
DECLARE @XML_NVC_8 XML;
SET @XML_NVC_8 = N'<?xml version="1.0" encoding="utf-8"?><test/>';
PRINT 'Success!'
/*
Msg 9402, Level 16, State 1, Line XXXXX
XML parsing: line 1, character 38, unable to switch the encoding
*/

GO
PRINT '';
PRINT 'VARCHAR / UTF-16:';
DECLARE @XML_VC_16 XML;
SET @XML_VC_16 = '<?xml version="1.0" encoding="utf-16"?><test/>';
PRINT 'Success!'
/*
Msg 9402, Level 16, State 1, Line XXXXX
XML parsing: line 1, character 38, unable to switch the encoding
*/

GO
PRINT '';
PRINT 'NVARCHAR / UTF-16:';
DECLARE @XML_NVC_16 XML;
SET @XML_NVC_16 = N'<?xml version="1.0" encoding="utf-16"?><test/>';
PRINT 'Success!'
-- Success!

    As you can see, when the input string is NVARCHAR, then the XML declaration can be included, but it needs to be "UTF-16".

  5. When the input string is VARCHAR then the XML declaration can be included, but it cannot be "UTF-16". It can, however, be any valid 8-bit encoding, in which case the bytes for that encoding will be converted into UTF-16, as shown below:

    DECLARE @XML XML;
SET @XML = '<?xml version="1.0" encoding="utf-8"?><test attr="'
           + CHAR(0xF0) + CHAR(0x9F) + CHAR(0x98) + CHAR(0x8E) + '"/>';
SELECT @XML;
-- <test attr="" />


SET @XML = '<?xml version="1.0" encoding="Windows-1255"?><test attr="'
           + CONVERT(VARCHAR(10), 0xF9ECE5ED) + '"/>';
SELECT @XML AS [XML from Windows-1255],
       CONVERT(VARCHAR(10), 0xF9ECE5ED) AS [Latin1_General / Windows-1252];
/*
XML from Windows-1255    Latin1_General / Windows-1252
<test attr="שלום" />     ùìåí
*/

    The first example specifies the 4-byte UTF-8 sequence for Smiling Face with Sunglasses and it get converted correctly.
    The second example uses 4 bytes to represent 4 Hebrew letters making up the word "Shalom", which is converted correctly, and displayed correctly given that the "F9" byte, which is first, is the ש character, which is on the right-side of the word (since Hebrew is a right-to-left language). Yet those same 4 bytes display as ùìåí when selected directly since the default Collation for the current DB is Latin1_General_100_CS_AS_SC.

user_5777650
Related