How to change encoding for XML Column

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am updating an xml column in SQLServer 2005, but it always gives me
an error
"XML parsing: line 1, character 38, unable to switch the encoding"

my csharp is like the following

cmd.Parameters.Add("@xml", SqlDbType.Xml);
cmd.Parameters["@xml"].Value =xmldata;

My xml have the following line

"<?xml version="1.0" encoding="UTF-8"?>..."

If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.

Please advice.
Thanks in advance.
John
 
Well I do search a bit and perhaps you want to remove that line before you
insert it in to the column.

chanmm
 
Try using SqlDbType.VarChar (even if your parameter in the proc is really
XML). This worked for me. Reason is likely that you're using a System.String
(Unicode) and the XML data type is internally UTF-16, so telling it UTF-8 is
switching encodings, after its "decided" you wanted UTF-16. There is
automatic string conversion if the encoding matches the SQL varchar data
type.

Bear in mind, you'll always get the data out as UTF-16.

Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
 
Back
Top