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

by

This question is a near-duplicate of 2 others, and surprisingly – while this one is the most recent – I believe it is missing the best answer.

The duplicates, and what I believe to be their best answers, are:

In the end, it doesn’t matter what encoding is declared or used, as long as the XmlReader can parse it locally within the application server.

As was confirmed in Most efficient way to read XML in ADO.net from XML type column in SQL server?, SQL Server stores XML in an efficient binary format. By using the SqlXml class, ADO.net can communicate with SQL Server in this binary format, and not require the database server to do any serialization or de-serialization of XML. This should also be more efficient for transport across the network.

By using SqlXml, XML will be sent pre-parsed to the database, and then the DB doesn’t need to know anything about character encodings – UTF-16 or otherwise. In particular, note that the XML declarations aren’t even persisted with the data in the database, regardless of which method is used to insert it.

Please refer to the above-linked answers for methods that look very similar to this, but this example is mine:

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;

static class XmlDemo {
    static void Main(string[] args) {
        using(SqlConnection conn = new SqlConnection()) {
            conn.ConnectionString = "...";
            conn.Open();

            using(SqlCommand cmd = new SqlCommand("Insert Into TestData(Xml) Values (@Xml)", conn)) {

                cmd.Parameters.Add(new SqlParameter("@Xml", SqlDbType.Xml) {
                    // Works.
                    // Value = "<Test/>"

                    // Works.  XML Declaration is not persisted!
                    // Value = "<?xml version=\"1.0\"?><Test/>"

                    // Works.  XML Declaration is not persisted!
                    // Value = "<?xml version=\"1.0\" encoding=\"UTF-16\"?><Test/>"

                    // Error ("unable to switch the encoding" SqlException).
                    // Value = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Test/>"

                    // Works.  XML Declaration is not persisted!
                    Value = new SqlXml(XmlReader.Create(new StringReader("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Test/>")))
                });

                cmd.ExecuteNonQuery();
            }
        }
    }
}

Note that I would not consider the last (non-commented) example to be “production-ready”, but left it as-is to be concise and readable. If done properly, both the StringReader and the created XmlReader should be initialized within using statements to ensure that their Close() methods are called when complete.

From what I’ve seen, the XML declarations are never persisted when using an XML column. Even without using .NET and just using this direct SQL insert statement, for example, the XML declaration is not saved into the database with the XML:

Insert Into TestData(Xml) Values ('<?xml version="1.0" encoding="UTF-8"?><Test/>');

Now in terms of the OP’s question, the object to be serialized still needs to be converted into an XML structure from the MyMessage object, and XmlSerializer is still needed for this. However, at worst, instead of serializing to a String, the message could instead be serialized to an XmlDocument – which can then be passed to SqlXml through a new XmlNodeReader – avoiding a de-serialization/serialization trip to a string. (See http://blogs.msdn.com/b/jongallant/archive/2007/01/30/how-to-convert-xmldocument-to-xmlreader-for-sqlxml-data-type.aspx for details and an example.)

Everything here was developed against and tested with .NET 4.0 and SQL Server 2008 R2.

Please don’t make waste by running XML through extra conversions (de-deserializations and serializations – to DOM, strings, or otherwise), as shown in other answers here and elsewhere.

More Related Contents:

  1. How does SQLParameter prevent SQL Injection?
  2. Can I use a Regex in an XPath expression?
  3. How can I solve a connection pool problem between ASP.NET and SQL Server?
  4. Best way to get InnerXml of an XElement?
  5. XML – Data At Root Level is Invalid
  6. Login failed for user ‘DOMAIN\MACHINENAME$’
  7. Simplest way to have a configuration file in a Windows Forms C# application
  8. Deserializing empty xml attribute value into nullable int property using XmlSerializer
  9. What’s the best method to pass parameters to SQLCommand?
  10. Outlook autocleaning my line breaks and screwing up my email format
  11. What’s a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR?
  12. WebClient.DownloadString results in mangled characters due to encoding issues, but the browser is OK
  13. CLR Strict Security on SQL Server 2017
  14. Procedure expects parameter which was not supplied
  15. Does End Using close an open SQL Connection
  16. TSQL md5 hash different to C# .NET md5
  17. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  18. How to use XPath with XElement or LINQ?
  19. Does .Net 4.5 support XML 1.1 yet (for characters invalid in XML 1.0)?
  20. Why “Data at the root level is invalid. Line 1, position 1.” for XML Document?
  21. How can you name the Dataset’s Tables you return in a stored proc?
  22. “Type not expected”, using DataContractSerializer – but it’s just a simple class, no funny stuff?
  23. How to prevent XPath/XML injection in .NET
  24. SQL Server – defining an XML type column with UTF-8 encoding
  25. How do I specify XML serialization attributes to support namespace prefixes during deserialization in .NET?
  26. Implementing transactions over multiple databases
  27. What’s the best way to export UTF8 data into Excel?
  28. Performance: XDocument versus XmlDocument
  29. Using .Net what limitations (if any) are there in using the XmlSerializer?
  30. How to serialize a large graph of .NET objects into a SQL Server BLOB without creating a large buffer?

Leave a Comment