So today I was at client's site providing Technical Support and finalizing Enhancement Project that was done for the past few months. One of the task was for me to develop a Windows Service, which will grab the user's updated profile from a table in SQL Server 2000 and submit them to a Web Service developed by another vendor. I am using SOAP in my Windows Service to communicate with the Web Service on the other side. Since SOAP is using an XML format in their request and response, I had to grab the data from the SQL Server and build a SOAP Request XML before sending it to the Web Service. Previously it ran just fine, until it hit an error that says:-

An error occurred while parsing EntityName. Line X, position Y.

No matter how many retries, it keeps on hitting the same error and bail out of my Windows Service. After googling around for a bit, it turns out that one of the content in the user's profile column (address field) contains an ampersand (&). The content of the column looks something like this:-

Mailbox 3 & 4

Referring to this website, I found out that I have to replace the ampersand (&) sign into an equivalent HTML entity (&). The reason? I'm not so sure, but I think it has something to do on how .NET interprets the XML string that was built.

I was so lazy to go and recompile my Windows Service just to fix that tiny bug. So, what I did was to change my Stored Procedure in SQL Server 2000 so that it returns the data with '&' replaced with '&'. This is how I did it in my Stored Procedure (this is just an example):-

SELECT REPLACE(column_name, '&', '&') AS column_name FROM table_name WHERE some_conditions

That fixed my problem without having to recompile my Windows Service again. If I were to recompile again, then my code (in C#) would be something like this (refer to this website):-

public string EscapeXml (string input) {
  string output = string.Empty;
 
  output = input.Replace("&", "&");
  output = output.Replace("'", "'");
  output = output.Replace("\"", """);
  output = output.Replace(">", ">");
  output = output.Replace("<", "&lt;");
 
  return output;
}

Hope that helps.