尝试将 XML 内容存储到 SQL Server 2005 失败(编码问题) [英] Trying to store XML content into SQL Server 2005 fails (encoding problem)

问题描述

各位，

我有一个以 ISO-8859-1 编码返回数据的网络服务 - 因为它不是我的，我不能改变它:-(

I have a webservice that returns data in ISO-8859-1 encoding - since it's not mine, I can't change that :-(

出于审计目的，我想将这些调用的结果 XML 存储到 SQL Server 2005 表中，其中有一个XML NULL"类型的字段.

For auditing purposes, I'd like to store the resulting XML from these calls into a SQL Server 2005 table, in which I have a field of type "XML NULL".

从我的 C# 代码中，我尝试使用参数化查询将此 XML 内容存储到 XML 字段中，例如

From my C# code, I try to store this XML content into the XML field using a parametrized query, something like

SqlCommand _cmd = new SqlCommand("INSERT INTO dbo.AuditTable(XmlField) VALUES(@XmlContents)", _connection);

_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);
_cmd.Parameters["@XmlContents"].Value = (my XML response);

_cmd.ExecuteNonQuery();

问题是 - 当我运行这段代码时，我得到一个错误:

Trouble is - when I run this code, I get back an error:

消息 9402，级别 16，状态 1，第 1 行
XML解析:第1行，字符xy，无法切换编码

Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character xy, unable to switch the encoding

??我试图弄清楚我可以在哪里以及如何切换"编码 - 到目前为止没有运气.这到底是什么意思?我无法在 SQL Server 2005 中使用 ISO-8859-1 编码存储 XML??或者有什么技巧可以 a) 告诉 SQL Server 2005 只接受这种编码，或者 b) 在存储到 SQL Server 之前自动将 web 服务响应转换为 UTF 编码?

?? I was trying to figure out where and how I could possibly "switch" the encoding - no luck so far. What does this really mean? I cannot store XML with ISO-8859-1 encoding in SQL Server 2005?? Or is there a trick to a) tell SQL Server 2005 to just accept this encoding, or b) to automagically convert the webservice response to UTF encoding before storing in SQL Server?

感谢您的任何提示、指示、提示！马克

Thanks for any hints, pointers, tips! Marc

推荐答案

需要转换成utf-16

即使我使用它，我也不是 SQL Server 中 XML 的专家，但我们去年遇到了同样的问题，与发送的 xml 相比，SQL 中声明的字符串数据类型不匹配.

I'm not an expert on XML in SQL Server even though I use it, but we had the same problem last year and it was mis-match of the string datatype declared in SQL compared to the xml being sent.

这篇关于尝试将 XML 内容存储到 SQL Server 2005 失败(编码问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！