C# 尝试将 XML 内容存储到 SQL Server 2005 失败（编码问题）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/384974/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to store XML content into SQL Server 2005 fails (encoding problem)
提问by marc_s
Folks,
各位，
I have a webservice that returns data in ISO-8859-1 encoding - since it's not mine, I can't change that :-(
我有一个以 ISO-8859-1 编码返回数据的网络服务 - 因为它不是我的，我不能改变它:-(
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".
出于审计目的，我想将这些调用的结果 XML 存储到 SQL Server 2005 表中，其中有一个“XML NULL”类型的字段。
From my C# code, I try to store this XML content into the XML field using a parametrized query, something like
从我的 C# 代码中，我尝试使用参数化查询将此 XML 内容存储到 XML 字段中，例如
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:
问题是 - 当我运行这段代码时，我得到一个错误：
Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character xy, unable to switch the encoding
消息 9402，级别 16，状态 1，第 1 行
XML 解析：第 1 行，字符 xy，无法切换编码
?? 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?
?? 我试图弄清楚我可以在哪里以及如何“切换”编码 - 到目前为止没有运气。这到底是什么意思？我无法在 SQL Server 2005 中使用 ISO-8859-1 编码存储 XML？？或者有什么技巧可以 a) 告诉 SQL Server 2005 只接受这种编码，或者 b) 在存储在 SQL Server 中之前自动将 webservice 响应转换为 UTF 编码？
Thanks for any hints, pointers, tips! Marc
感谢您的任何提示，指针，提示！马克
采纳答案by gbn
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.
即使我使用它，我也不是 SQL Server 中 XML 的专家，但我们去年遇到了同样的问题，与发送的 xml 相比，SQL 中声明的字符串数据类型不匹配。
回答by Nathan Koop
I found this on google. http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/d40ef582-4ffe-4f4b-b6b8-03c6c0ba1a32/
我在谷歌上找到了这个。http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/d40ef582-4ffe-4f4b-b6b8-03c6c0ba1a32/
I think you can replace the line
我想你可以更换线路
_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);
with
和
_cmd.Parameters.Add("@XmlContents", System.Data.SqlTypes.SqlXml);
回答by Marc Gravell
Could you possibly re-write the xml as unicode (perhaps to a MemoryStream) and send that? Note: if you are just storingthe data, you can use varbinary(max)(and it will actually be quicker). This has no encoding difficulties, and will also allow you to audit any corrupt xml that you receive.
您能否将 xml 重写为 unicode（可能是 a MemoryStream）并发送它？注意：如果您只是存储数据，则可以使用varbinary(max)（实际上会更快）。这没有编码困难，并且还允许您审核收到的任何损坏的 xml。
If you are querying the data as xml inside the database server then xmlis obviously the way to go.
如果您在数据库服务器内以 xml 格式查询数据，那么xml显然是要走的路。
回答by gbn
回答by Chris S
Edit
I missed the ISO-8859-1 part of the question - the solution below is good for UTF8, but obviously doesn't solve Marc's problem as he can't alter the encoding.
编辑
我错过了问题的 ISO-8859-1 部分 - 下面的解决方案适用于 UTF8，但显然不能解决 Marc 的问题，因为他无法改变编码。
Here's the solution I use:
这是我使用的解决方案：
And a slightly modified version of the code from above (I've tested it with a UTF8 file using SQL 2005):
以及上面代码的稍微修改版本（我已经使用 SQL 2005 用 UTF8 文件对其进行了测试）：
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
...
using (SqlConnection connection = new SqlConnection("conn string"))
{
connection.Open();
string sql = "INSERT INTO mytable (xmlColumn) VALUES (@xmlData)";
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Swap round if the source file is unicode
string xml = File.ReadAllText(@"C:\myxml.xml");
//string xml = File.ReadAllText(@"C:\myxml.xml", Encoding.Unicode);
using (MemoryStream stream = new MemoryStream())
{
using (StreamWriter writer = new StreamWriter(stream, Encoding.Unicode))
{
writer.Write(xml);
writer.Flush();
stream.Position = 0;
SqlParameter parameter = new SqlParameter("@xmlData", SqlDbType.Text);
parameter.Value = new SqlXml(stream);
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
}
}
回答by Karthik D V
Even I faced similar issue while inserting xml content to db. For ex , input was like this:
即使我在将 xml 内容插入 db 时也遇到了类似的问题。对于 ex ，输入是这样的：
Insert Into TestData(Xml) Values ('<?xml version="1.0" encoding="UTF-8"?><Test/>')
This kind of statement used to fail and I was getting "unable to switch .." error. Later I simply prefixed N to xml string like this :
这种语句曾经失败，我收到“无法切换..”错误。后来我简单地在 xml 字符串前加上 N 前缀，如下所示：
Insert Into TestData(Xml) Values (N'<?xml version="1.0" encoding="UTF-8"?><Test/>')
After this it started working !!!
在此之后它开始工作！！！
