无法将sic server 2008中的unicode数据转换为XML列

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

在sql 2008中我已经有一个包含3列(Id bigint,Title nvarchar(450),Description nvarchar(MAX))的表

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.

我决定将Title和Description列转换为一个XML列。但在尝试更新时会收到许多错误,例如“非法名字”或“非法合格名字”等。

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

要解决这个问题,我只需使用下面的代码创建带有亚音速2.1的Windows应用程序

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.

注2:我试图更新日文记录,但是因为英文工作正常。

Could you please help me.

请你帮助我好吗。

Thanks.

3 个解决方案

#1

I think you should be using UTF-8 encoding.

我认为你应该使用UTF-8编码。

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

#2

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

为什么不直接在SQL Server中完成所有这些?你能试试这个:

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?

样式的“2”将删除SQL Server不喜欢XML的东西 - 比如DTD等等。也许这对你的情况也有帮助吗?

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 :-)

从外部导入XML时,我遇到了SQL Server XML支持的各种麻烦。通常,无论如何,它最终会起作用,但是:-)

Marc

#3

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

经过多次研究,我找到了关于我的问题的文章。请参阅Tarun Ghosh的帖子

#1