Trying to store XML content into SQL Server 2005 fails (encoding problem)

Question

Folks,

I have a webservice that returns data in ISO-8859-1 encoding - since it's not mine, I can't change that :-(

For auditing purposes, I'd like to store the resulting XML from these calls into a SQL Server 2005 table, in which I have a field of type "XML NULL".

From my C# code, I try to store this XML content into the XML field using a parametrized query, something like

SqlCommand _cmd = new SqlCommand("INSERT INTO dbo.AuditTable(XmlField) VALUES(@XmlContents)", _connection);

_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);
_cmd.Parameters["@XmlContents"].Value = (my XML response);

_cmd.ExecuteNonQuery();

Trouble is - when I run this code, I get back an error:

Msg 9402, Level 16, State 1, Line 1 XML parsing: line 1, character xy, unable to switch the encoding

?? I was trying to figure out where and how I could possibly "switch" the encoding - no luck so far. What does this really mean? I cannot store XML with ISO-8859-1 encoding in SQL Server 2005?? Or is there a trick to a) tell SQL Server 2005 to just accept this encoding, or b) to automagically convert the webservice response to UTF encoding before storing in SQL Server?

Thanks for any hints, pointers, tips! Marc

Answer 1

Hello Marc,

I found this on google. http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/d40ef582-4ffe-4f4b-b6b8-03c6c0ba1a32/

I think you can replace the line

_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);

with

_cmd.Parameters.Add("@XmlContents", System.Data.SqlTypes.SqlXml);

Answer 2

Could you possibly re-write the xml as unicode (perhaps to a MemoryStream) and send that? Note: if you are just storing the data, you can use varbinary(max) (and it will actually be quicker). This has no encoding difficulties, and will also allow you to audit any corrupt xml that you receive.

If you are querying the data as xml inside the database server then xml is obviously the way to go.

Answer 3

You need to convert to utf-16

I'm not an expert on XML in SQL Server even though I use it, but we had the same problem last year and it was mis-match of the string datatype declared in SQL compared to the xml being sent.

Answer 4

I posted some info:

http://mrboratech.blogspot.com/2009/02/how-to-store-xmldocument-mssql-2005-as.html

Answer 5

Here's the solution I use:

• Inserting XML into a SQL Database (the correct way)

And a slightly modified version of the code from above (I've tested it with a UTF8 file using SQL 2005):

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

...
using (SqlConnection connection = new SqlConnection("conn string"))
{
    connection.Open();
    string sql = "INSERT INTO mytable (xmlColumn) VALUES (@xmlData)";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
     // Swap round if the source file is unicode      
     string xml = File.ReadAllText(@"C:\myxml.xml");
     //string xml = File.ReadAllText(@"C:\myxml.xml", Encoding.Unicode);

     using (MemoryStream stream = new MemoryStream())
     {
      using (StreamWriter writer = new StreamWriter(stream, Encoding.Unicode))
      {
       writer.Write(xml);
       writer.Flush();
       stream.Position = 0;

       SqlParameter parameter = new SqlParameter("@xmlData", SqlDbType.Text);
       parameter.Value = new SqlXml(stream);
       command.Parameters.Add(parameter);
       command.ExecuteNonQuery();
      }
     }
    }
}