Saturday, October 29, 2016

XML parsing: line 1, character 38, unable to switch the encoding

Hello all,
Given the task at hand that your going to insert XML data into an XML column in SQL Server and you encounter this error "XML parsing: line 1, character 38, unable to switch the encoding", it seems the insertion of XML data failed due to this line here: <?xml version="1.0" encoding="utf-8" ?>. After doing some research, I found a tutorial on how to avoid unicode issues when inserting XML data into an XML column in SQL Server which is the basis of the solution. I just change the Encoding of the stream to UTF8 to match the encoding of the XML file.
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
 SqlCommand command = new SqlCommand("Insert into XMLTable(name, xmlData) values (@name, @xmlData)", con);
 con.Open();

 string xmlFile = File.ReadAllText(location);

 using (MemoryStream stream = new MemoryStream())
 {
  using (StreamWriter writer = new StreamWriter(stream, Encoding.UTF8)) 
  {
   writer.Write(xmlFile);
   writer.Flush();
   stream.Position = 0;

   SqlParameter parameter = new SqlParameter("@xmlData", SqlDbType.Text);
   parameter.Value = new SqlXml(stream);
   command.Parameters.Add("@name", SqlDbType.VarChar).Value = "Products.xml";
   command.Parameters.Add(parameter);
   command.ExecuteNonQuery();
  }
 }
}
Reference: Avoiding Unicode issues when inserting XML into a SQL Database

0 comments:

Post a Comment