How should I handle "&" in StringBuilder as later this is being used as XML and parsing that XML throws error in SQL Server database

Question

While I am appending StringBuilder, there are so many values which contain &. Later I am using this StringBuilder to database as XML where it is being parsed and throws an error.

So is there anyway why which when I am appending this in StringBuilder & it should be converted to &.

Code snippet:

System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("<DATASET>");

for (int ctr = 0; ctr < countOBX; ctr++)
{
    if (arPID.GetValue(rowno, 0) == arOBR.GetValue(ctr, 5))
    {
        sb.Append("<RECORD>");
        sb.Append("<FacilityNumber>" + arMSH.GetValue(rowno, 0) + "</FacilityNumber>");
        sb.Append("<MRN>" + arPID.GetValue(rowno, 0) + "</MRN>");
    }
}

sb.Append("</DATASET>");

string HL7spectra = sb.ToString();

OpenDBConnection();

using (SqlCommand cmd = new SqlCommand("procImportLabOrderSpectra", m_objConn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@HL7spectra", HL7spectra);
    cmd.CommandTimeout = 0;

    cmd.ExecuteNonQuery();

    CloseDBConnection();
}

Answer 1

It's not only &: having < or > in your strings will also create invalid XML. There are five characters in total that need to be replaced, see the following question for details.

• What characters do I need to escape in XML documents?

The solution is to XML-encode any string input that you put into your XML. In C#, the idiomatic way is to not create your XML with string concatenation but to use one of the XML classes includes in the base class library instead:

• How can I build XML in C#?