XML parsing: semicolon expected

Question

XML parsing: semicolon expected

Marianne L Collins

SSCommitted

Points: 1895
More actions
March 6, 2018 at 7:22 am

#333842

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You want to check for reserved characters in the column you want to convert into XML. The one that jumps out is & with is used to "escape" other restricted characters out of XML kind of like URL encoding.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Marianne L Collins SSCommitted Points: 1895 More actions · Answer 2

I tried this in my select:
CONVERT(xml, replace(cast(xml_data as varchar(max)),'&','&'), 2) as converted_xml
but got the same error

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

Marianne L Collins - Tuesday, March 6, 2018 7:22 AM
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?

sgmunson SSC Guru Points: 110639 More actions · Answer 4

SELECT PrimaryKeyColumn, TRY_CONVERT(xml, VarcharColumn) AS XMLValue, VarcharColumn
FROM YourTable
WHERE TRY_CONVERT(xml, VarcharColumn) IS NULL
AND VarcharColumn IS NOT NULL;

This should identify the problem rows.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Marianne L Collins SSCommitted Points: 1895 More actions · Answer 5

sgmunson - Friday, March 9, 2018 11:34 AM
SELECT PrimaryKeyColumn, TRY_CONVERT(xml, VarcharColumn) AS XMLValue, VarcharColumn
FROM YourTable
WHERE TRY_CONVERT(xml, VarcharColumn) IS NULL
AND VarcharColumn IS NOT NULL;
This should identify the problem rows.

Thanks- that's exactly what I ended up doing. I'll use the resulting NULLs to pull the problem rows into an error table to be addressed later.