How to change encoding for XML Column

[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

 

[chanmm]

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

chanmm

 

[Bob Beauchemin]

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