How to encode special characters in XML

Question

My string XML contains a whole series of special characters:

&
egrave;
&
rsquo;
&
rsquo;
&
rsquo;
&
ldquo;
&
rdquo;
&
rsquo
&
agrave;
&
agrave;

I need replace this special characters in insert string in DB and I tried use System.Net.WebUtility.HtmlEncode without success, can you help me?

string sql = "insert into rss (title, description, link, pubdate) values (?,?,?, " +
             " STR_TO_DATE(?, '%a, %d %b %Y %H:%i:%s GMT'));";

OdbcCommand command;
OdbcDataAdapter adpter = new OdbcDataAdapter();
connection.Open();
command = new OdbcCommand(sql, connection);
command.Parameters.AddWithValue("param1", System.Net.WebUtility.HtmlEncode(xmlTitle.InnerText.ToString()));
command.Parameters.AddWithValue("param2", System.Net.WebUtility.HtmlEncode(xmlDescription.InnerText.ToString()));
command.Parameters.AddWithValue("param3", System.Net.WebUtility.HtmlEncode(xmlLink.InnerText.ToString()));
command.Parameters.AddWithValue("param4", System.Net.WebUtility.HtmlEncode(xmlPubDate.InnerText.ToString()));
adpter.InsertCommand = command;
adpter.InsertCommand.ExecuteNonQuery();
connection.Close();

Answer 1

You can use a native .NET method for escaping special characters in text. Sure, there's only like 5 special characters, and 5 Replace() calls would probably do the trick, but I'm sure there's got to be something built-in.

Example of converting "&" to "&"

To much relief, I've discovered a native method, hidden away in the bowels of the SecurityElement class. Yes, that's right - SecurityElement.Escape(string s) will escape your string and make it XML safe.

This is important, since if we are copying or writing data to Infopath Text fields, it needs to be first Escaped to non-Entity character like "&".

invalid XML Character to Replaced With

"<" to "&lt;"

">" to ">"

"\"" to """

"'" to "'"

"&" to "&"

Namespace is "System.Security". Refer : http://msdn2.microsoft.com/en-us/library/system.security.securityelement.escape(VS.80).aspx

The Other Option is to Customise code for

public static string EscapeXml( this string s )
{
  string toxml = s;
  if ( !string.IsNullOrEmpty( toxml ) )
  {
    // replace literal values with entities
    toxml = toxml.Replace( "&", "&" );
    toxml = toxml.Replace( "'", "'" );
    toxml = toxml.Replace( "\"", """ );
    toxml = toxml.Replace( ">", ">" );
    toxml = toxml.Replace( "<", "&lt;" );
  }
  return toxml;
}

public static string UnescapeXml( this string s )
{
  string unxml = s;
  if ( !string.IsNullOrEmpty( unxml ) )
  {
    // replace entities with literal values
    unxml = unxml.Replace( "&apos;", "'" );
    unxml = unxml.Replace( "&quot;", "\"" );
    unxml = unxml.Replace( "&gt;", ">" );
    unxml = unxml.Replace( "&lt;", "<" );
    unxml = unxml.Replace( "&amp;", "&" );
  }
  return unxml;
}

Answer 2

You can use HttpUtility.HtmlDecode or with .NET 4.0+ you can also use WebUtility.HtmlDecode