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

Question

Dear All,

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

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 "<?xml version=\"1.0\" encoding=\"utf-16\"?>" into first above string that get error "unable to switch the encoding".

Note: Also i using this method 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.

Answer 1

I think you should be using UTF-8 encoding.

You can find out more about the encoding here:

http://www.ibm.com/developerworks/xml/library/x-utf8/

Also, you will find some more information here:

http://msdn.microsoft.com/en-us/library/ms131375.aspx

Answer 2

Why not do all of this directly in SQL Server ?? Could you try this:

UPDATE YourTable
SET XmlCol = CAST(N'<Person><Title>' + Title + 
                  N'</Title><Description>' + Description + 
                  N'</Description></Person>' AS XML)
WHERE ID = 12345

Does that work? If not, then maybe this one here?

UPDATE YourTable
SET XmlCol = CONVERT(XML, N'<Person><Title>' + Title + 
                          N'</Title><Description>' + Description + 
                          N'</Description></Person>', 2)
WHERE ID = 12345

The "2" for style will strip out things SQL Server doesn't like about XML - things like DTD and so forth. Maybe that'll help in your case, too?

I've had various troubles with SQL Server's XML support when importing XML from outside. Usually, one way or another, it ends up working, though :-)

Marc

Answer 3

After many research, I found article about my problem. Please see Tarun Ghosh post