如何删除 ISO 编码的 XML 文件中存在的损坏字符

Question

当我尝试将带有 UTF-16 编码的 xml 文件转换为 ISO-8859-1 时，我看到像 Â 这样的损坏字符。

您能否建议一些解决方案来删除损坏的字符？我想要 ISO 编码格式的 XML。

这是我的代码，

using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.AppSettings.Get("SqlConn")))
{
    sqlConnection.Open();
    using (SqlCommand sqlCommand = new SqlCommand())
    {
        sqlCommand.CommandTimeout = 0;
        sqlCommand.CommandText = commandText;
        sqlCommand.Connection = sqlConnection;
        // the data from database data is UTF encoded
        using (StreamWriter textwriterISO = new StreamWriter(path + "_out.XML", false, Encoding.GetEncoding("ISO-8859-1")))
        {                                  
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            Console.WriteLine("Writing results.This could take a very long time.");
            while (sqlDataReader.Read())
            {
                for (int i = 0; i < sqlDataReader.FieldCount; i++)
                {
                    byte[] arr = System.Text.Encoding.GetEncoding(28591).GetBytes(sqlDataReader[i].ToString());
                    string ascii = Encoding.GetEncoding("UTF-8").GetString(arr);
                    textwriter.WriteLine(sqlDataReader.GetName(i),ascii));
                }
                textwriter.Flush();
            }
        }
    }                         
}

Answer 1

您的代码滥用了StreamWriter类，并对数据库数据进行了错误的手动编码。 您将源 UTF-16 DB 数据转换为 CP28591，将CP28591字节解释为 UTF-8 以将其转换回 UTF-16，然后在写入文件时StreamWriter将现在格式错误的 UTF-16 转换为 ISO-8859-1。 这是完全错误的做法，更不用说浪费所有这些转换的开销了。 让StreamWriter直接处理源 UTF-16 DB 数据的编码，摆脱其他所有内容，例如：

using (StreamWriter textwriterISO = new StreamWriter(path + "_out.XML", false, Encoding.GetEncoding("ISO-8859-1")))
{                                  
    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
    Console.WriteLine("Writing results.This could take a very long time.");
    while (sqlDataReader.Read())
    {
        for (int i = 0; i < sqlDataReader.FieldCount; i++)
        {
            // you were originally calling the WriteLine(String, Object) overload.
            // Are you sure you want to call that? It interprets the first parameter
            // as a pattern to format the value of the second parameter. A DB column
            // name is not a formatting pattern!
            textwriterISO.WriteLine(sqlDataReader.GetName(i), sqlDataReader[i].ToString());
            // perhaps you meant to write the DB column name and field value separately?
            //
            // textwriterISO.WriteLine(sqlDataReader.GetName(i));
            // textwriterISO.WriteLine(sqlDataReader[i].ToString());
        }
        textwriterISO.Flush();
    }
}

话虽如此，您提到您希望以XML格式输出。 StreamWriter本身不会为您输出 XML。 请改用 XmlSerializer 或 XmlTextWriter 类将 DataReader 数据转换为 XML，然后将其写入StreamWriter。