Can't Convert unicode Data into XML column in sql server 2008

admin

Administrator
Staff member
I already have a table with 3 column (Id bigint, Title nvarchar(450), Description nvarchar(MAX)) in sql 2008

I decide convert Title and Description column into one XML column. but when trying to update get many error like "illegal name character" or "illegal qualified name character" and etc.

to solve this problem i just create windows application with subsonic 2.1 with below code

Code:
MyTable tbl = new MyTable(1111);
tbl.myXMLcol = "<Person><Title><![CDATA[ " + HttpUtility.HtmlEncode(tbl.Title) + " ]]></Title><Description><![CDATA[ " + HttpUtility.HtmlEncode(tbl.Description) + " ]]></Description></Person>";
tbl.Save();

then try to add
Code:
"<?xml version=\"1.0\" encoding=\"utf-16\"?>"
into first above string that get error "unable to switch the encoding".

Note: Also i using <a href="http://seattlesoftware.wordpress.com/2008/09/11/hexadecimal-value-0-is-an-invalid-character/" rel="nofollow noreferrer">this method</a> to remove illegal character, but not solve my problem

Note2: I trying to update Japanese record that get this error, but for English work properly.

Could you please help me.

Thanks.