Saving XML to SQL and the Dreaded Parse Error

I recently at work tried to save an XML string to SQL Server 2005 and I kept getting the following error...

XML parsing: line 1, character 38, unable to switch the encoding

Turns out the solution was to just change the paramter datatype that I'm passing to the stored proc to a varchar (even though the stored proc expects XML) and the problem goes away and data is saved fine. Example of the parameter I passed to the stored proc-


SqlParameter p_xml = new SqlParameter ();
p_xml.ParameterName = "@p_xml";
p_xml.SqlDbType = SqlDbType.VarChar;
p_xml.Value = "your xml string";


The issue has to do with my string XML being UTF-8 based, and SQL 2005 wanting UTF-16. The above hack fixes the issue, and you can still pass UTF-16 based XML and the varchar handles that as well.

Credit for the fix goes to Bob Beauchemin at this link-

http://bytes.com/forum/thread498342.html