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

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.