如何解决“无法切换编码"将 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屋！