如何解决“无法切换编码"将 XML 插入 SQL Server 时出错 [英] How to solve "unable to switch the encoding" error when inserting XML into SQL Server
问题描述
我正在尝试插入 XML 列 (SQL SERVER 2008 R2),但服务器抱怨:
I'm trying to insert into XML column (SQL SERVER 2008 R2), but the server's complaining:
System.Data.SqlClient.SqlException (0x80131904):
XML解析:第1行,第39个字符,无法切换编码
System.Data.SqlClient.SqlException (0x80131904):
XML parsing: line 1, character 39, unable to switch the encoding
我发现 XML 列必须是 UTF-16 才能成功插入.
I found out that the XML column has to be UTF-16 in order for the insert to succeed.
我使用的代码是:
XmlSerializer serializer = new XmlSerializer(typeof(MyMessage));
StringWriter str = new StringWriter();
serializer.Serialize(str, message);
string messageToLog = str.ToString();
如何将对象序列化为 UTF-8 字符串?
How can I serialize object to be in UTF-8 string?
EDIT:好的,抱歉搞混了 - 字符串必须是 UTF-8.你是对的 - 默认情况下它是 UTF-16,如果我尝试插入 UTF-8,它就会通过.那么问题来了,如何序列化成UTF-8.
EDIT: Ok, sorry for the mixup - the string needs to be in UTF-8. You were right - it's UTF-16 by default, and if I try to insert in UTF-8 it passes. So the question is how to serialize into UTF-8.
示例
这会在尝试插入 SQL Server 时导致错误:
This causes errors while trying to insert into SQL Server:
<?xml version="1.0" encoding="utf-16"?>
<MyMessage>Teno</MyMessage>
这不会:
<?xml version="1.0" encoding="utf-8"?>
<MyMessage>Teno</MyMessage>
更新
我发现什么时候 SQL Server 2008 的 Xml
列类型需要 utf-8,当 utf-16 在 xml 规范的 encoding
属性中时试图插入:
I figured out when the SQL Server 2008 for its Xml
column type needs utf-8, and when utf-16 in encoding
property of the xml specification you're trying to insert:
当你想添加utf-8
时,那么像这样在SQL命令中添加参数:
When you want to add utf-8
, then add parameters to SQL command like this:
sqlcmd.Parameters.Add("ParamName", SqlDbType.VarChar).Value = xmlValueToAdd;
如果您尝试在前一行中添加带有 encoding=utf-16
的 xmlValueToAdd,则会在插入时产生错误.此外,VarChar
意味着无法识别国家字符(它们变成问号).
If you try to add the xmlValueToAdd with encoding=utf-16
in the previous row it would produce errors in insert. Also, the VarChar
means that national characters aren't recognized (they turn out as question marks).
要将 utf-16 添加到 db,请在前面的示例中使用 SqlDbType.NVarChar
或 SqlDbType.Xml
,或者根本不指定类型:>
To add utf-16 to db, either use SqlDbType.NVarChar
or SqlDbType.Xml
in previous example, or just don't specify type at all:
sqlcmd.Parameters.Add(new SqlParameter("ParamName", xmlValueToAdd));
推荐答案
虽然 .net 字符串总是 UTF-16
你需要使用 UTF-16
序列化对象> 编码.应该是这样的:
Although a .net string is always UTF-16
you need to serialize the object using UTF-16
encoding.
That sould be something like this:
public static string ToString(object source, Type type, Encoding encoding)
{
// The string to hold the object content
String content;
// Create a memoryStream into which the data can be written and readed
using (var stream = new MemoryStream())
{
// Create the xml serializer, the serializer needs to know the type
// of the object that will be serialized
var xmlSerializer = new XmlSerializer(type);
// Create a XmlTextWriter to write the xml object source, we are going
// to define the encoding in the constructor
using (var writer = new XmlTextWriter(stream, encoding))
{
// Save the state of the object into the stream
xmlSerializer.Serialize(writer, source);
// Flush the stream
writer.Flush();
// Read the stream into a string
using (var reader = new StreamReader(stream, encoding))
{
// Set the stream position to the begin
stream.Position = 0;
// Read the stream into a string
content = reader.ReadToEnd();
}
}
}
// Return the xml string with the object content
return content;
}
通过将编码设置为 Encoding.Unicode,不仅字符串将是 UTF-16
,而且您还应该将 xml 字符串设为 UTF-16
.
By setting the encoding to Encoding.Unicode not only the string will be UTF-16
but you should also get the xml string as UTF-16
.
<?xml version="1.0" encoding="utf-16"?>
这篇关于如何解决“无法切换编码"将 XML 插入 SQL Server 时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!