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

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


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.


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);


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.