CODEWITHSUNDEEP
xmlxsltsqlxml
Robert
Robert

Reputation: 724

System.Xml.XmlException: An error occurred while parsing EntityName

I am attempting to transform some XML [returned by a sql query e.g. SELECT ... FOR XML RAW('EmailData'),ROOT('Emails'),type] but for whatever reason just this row is giving me problems:

<Emails>
  <EmailData ID="370" C_ID="93" Co_ID="019" MemberName="Twin Cities Orthopedics – Savage" Commit_Program_ID="92" SubmittedBy="9175" AccountExecInfo="Jessica Cooley, [email protected]" PortfolioExecInfo="Seth Bull, 000.581.5006, [email protected]" SupplierRepInfo="Matt Matts, 000.000.4236 Ext-37, [email protected]" MemberInfoLink="https://nf.CCDD.com/asp/memberinfo.asp?memid=7893&amp;co=019" DocumentLink="https://nf.CCDD.com/asp/maintanance.asp?doc_id=630" TermsAndCondLink="https://nf.CCDD.com/TermsAndConditions.asp?CPID=92" Doc_Source_ID="BTM" SubmittedByInfo="Automated Tier Update" />
</Emails>

The cuplrit seems to be MemberName attribute value ... because when I return an empty string instead of the above value I do not get the exception. Do you see any invalid charecters? Is '-' an invalid charecter for xml?

My XSL Transformation code:

public static SqlXml Transform(SqlXml inputDataXML, SqlXml inputTransformXML)
        {
            try
            {
                MemoryStream memoryXml = new System.IO.MemoryStream();
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader output = null;

                xslt.Load(inputTransformXML.CreateReader());

                // Output the newly constructed XML
                XmlTextWriter outputWriter = new XmlTextWriter(memoryXml, System.Text.Encoding.Default);
                xslt.Transform(inputDataXML.CreateReader(), null, outputWriter, null);
                memoryXml.Seek(0, System.IO.SeekOrigin.Begin);
                output = new XmlTextReader(memoryXml);

                return new SqlXml(output);
            }
            catch(Exception ex)
            {
                byte[] byteArray = Encoding.ASCII.GetBytes( "<error>" + ex.ToString() + "</error>");
                    MemoryStream stream = new MemoryStream( byteArray );
                    XmlReader reader = new XmlTextReader(stream);
                SqlXml x = new SqlXml(reader);

                return x;
            }
        }

Upvotes: 1

Views: 4897

Answers (1)

Tim C
Tim C

Reputation: 70618

It's possible that the hyphen in the member name value is actually an 'em-dash' or an 'en-dash' instead of a normal hypen, which could happen if the data was copy-and-pasted from Microsoft Word, for example, when entering into the database.

Try finding out the ASCII value of the character in SQL. It would be either 150 or 151.

SELECT ASCII(SUBSTRING(MemberName, 23, 1)) FROM Member WHERE ....

If so, they may need to be encoded in the XML as &#x2013; and `—' respectively.

It very much depends on the encoding of the XML. As an example, try opening the following files (which contains both an en-dash and em-dash) in IE and see how differently they are handled

<?xml version="1.0" encoding="WINDOWS-1252"?>
<Emails> <EmailData MemberName="Twin Cities Orthopedics –— Savage"/> </Emails>

The above file should work, but the following shouldn't.

<?xml version="1.0" encoding="utf-8"?>
<Emails> <EmailData MemberName="Twin Cities Orthopedics –— Savage"/> </Emails>

Encoding them as &#x2013; and &#x2014; would work in both cases though.

Upvotes: 2

Related Questions