SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML parsing: semicolon expected


XML parsing: semicolon expected

Author
Message
Marianne L Collins
Marianne L Collins
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 788
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
Matt Miller (4)
Matt Miller (4)
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113569 Visits: 22312

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
Marianne L Collins
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 788
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
Eirikur Eiriksson
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164875 Visits: 23921
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.
Cool
Question, have you tried other format parameter values in the CONVERT?

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98646 Visits: 7282
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)
Smile Smile Smile
Health & Nutrition
Marianne L Collins
Marianne L Collins
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 788
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search