I know the "XML parsing: semicolon expected" error is a data error, and not a SQL error. The first error I am trying to get rid of is the one about DTD subsets, so I am using CONVERT(xml, xml_data, 2), but then I get this:
Msg 9411, Level 16, State 1, Line 14
XML parsing: line 1, character 94, semicolon expected
I don't have access to modify the actual xml. I am querying. It is data in a table (with >1 million records, so I don't even know which record is throwing the error), received from a vendor as responses to API calls. I am trying to shred the xml in a stored procedure, in order to extract bits of data.
Is it possible to somehow bypass the records with the bad data? I am NOT processing row by row at the moment, nor do I want to.
Thanks!
-Marianne
Can you post an example of the XML that is failing?
String manipulation is most likely not the way to go, my suggestion would be to store the data as XML data type, then invalid XML would be rejected on the insert.
😎
Question, have you tried other format parameter values in the CONVERT?