How to change encoding for XML Column

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.
JohnWell I do search a bit and perhaps you want to remove that line before you
insert it in to the column.
chanmm
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>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
>|||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
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>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
>