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.