Msg 9420, Level 16, State 1, Line 1 XML parsing:

Question

Msg 9420, Level 16, State 1, Line 1 XML parsing:

Viewing 3 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

If you can't post the real data that's causing the problem you'd need to come up with some modified sample data that will show the same effect.

We need to be able to reproduce that effect on our side in order to be sure that the solution we might come up with will actually resolve your issue.

What you could do is to modify the data in line 13 before character 959 (e.g. replace "Smith" with "Smith1" and another test with "Smit") and check if the character that fails will move to 960 rsp. 958. In that case you'd need to get to that character and see what it is...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bill.bertovich Old Hand Points: 391 More actions · Answer 2

Unfortunately, I think I will have to open a Microsoft incident, aaarrrgghhh.

The unfiltered query returns 16 rows, out of 3,800, before erroring out, but it tells me the offending XML is on row 13.

SELECT

ReportId

, CAST(XmlReport AS XML) TrueXml

FROM TF_XMLReports

WHERE XmlReport IS NOT NULL

ORDER BY ReportId

OPTION (MAXDOP 1)

This is a truncated version of the data:

RowReportIdTrueXML

183228<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

294159<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

394160<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

494161<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

594208<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

694211<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

795028<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

895030<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

995031<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

1095032<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

11102123<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

12112287<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

13112299<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

14112301<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

15112313<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

16112390<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

When I run this query which returns only the offending row, the resultset is retruned without error:

SELECT

ReportId

, CAST(XmlReport AS XML) TrueXml

FROM TF_XMLReports

WHERE XmlReport IS NOT NULL

AND ReportId = 112299

ORDER BY ReportId

OPTION (MAXDOP 1)

RowReportIdTrueXML

1112299<Report xmlns:xsi="http://www.w3.org/2001/XMLSchem

bill.bertovich Old Hand Points: 391 More actions · Answer 3

Ok, I finally found out what was going on. The line number that was included with the message had no bearing on the real issue as we strip all whitespace from our XML which includes CR and LF. So, it was misleading. The real problem is the curly quotes that Word inserts through Autocorrect. For some reason, SQL Server doesn't like the curly quotes and considers them invalid XML, even though they validate in Visual Studio and online XML validators.