Error in formating of XML string with CDATA

Question

I get this error "Start tag on line 1 does not match the end tag of 'document'".

    string rawXml = "<?xml version='1.0' ?>" +
        "<document>" +
            "<![CDATA[" +
                "<topic>" +
                   "My test" +
                "</topic>" +
            "]]>" +
        "</document>";

Error occurres when I try to execute a stored procedure which send this xml as a parameter.

    var xmlDoc = new XmlDocument();
    xmlDoc.LoadXml(rawXml);

    DataResultXElement drx = ss.xelem_Query(string.Format("exec Topic_Update '{0}', '{1}'", sessionId, xmlDoc.InnerXml));

If I remove it works, but I need CDATE to store data properly in the database.

Should I format the string differently? Thanks!

Answer 1

Couple of things:

• The parsing of your rawXml (i.e. constructing the XmlDocument instance) is completely superfluous.
• You must have made some typo in the raw XML. What you provided looks like a perfectly valid XML (actually passes W3C's validation).
• Don't construct a SQL query using String.Format. Use SQL query parameters instead. The reason is this way you end-up with an invalid SQL statement and also open the door for SQL-injection attacks.

Answer 2

1. Do not use string manipulation to construct XML documents.

2. Do not use string manipulation to construct SQL queries.

Do this instead:

StringBuilder sb = new StringBuilder();
using (StringWriter sw = new StringWriter(sb))
using (XmlWriter xw = XmlWriter.Create(sw))
{
    xw.WriteStartElement("document");
    xw.WriteCData("<topic>My test </topic>");
    xw.WriteEndElement();
}

XDocument result = new XDocument();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("Topic_Update", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("SessionID", sessionId);
    cmd.Parameters.AddWithValue("XmlText", sb.ToString());
    using (XmlReader xr = cmd.ExecuteXmlReader())
    {
        result.Load(xr);
    }
}