question

rob.steven avatar image
rob.steven asked

XML Parsing, illegal XML Character

I have this query in SQL Server. When I run it, I am getting this error. Msg 9420, Level 16, State 1, Line 2 XML parsing: line 861527, character 67, illegal xml character Can anyone help me fix it? --QUERY: DECLARE @XmlDoc XML SET @XmlDoc = ( SELECT Cast(XmlData.BulkColumn AS XML) AS BulkColumn FROM OpenRowSet(BULK '\\pks-la-nasuni\Data Services Department\XML_Test_Import\Phan_Temp\prosight.xml', Single_CLOB) AS XmlData ) TRUNCATE TABLE AAA_tbl_Transaction_Staging INSERT AAA_tbl_Transaction_Staging (Xml_Data) SELECT T.c.query('.') AS Xml_Data FROM @XmlDoc.nodes('/Transactions/Transaction') T(c)
sql-server-2008sql serverxmlsql server 2008 r2parsing
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What's the contents of the 861527th line of the XML input?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@rob.steven It is clear that the file named prosight.xml is not a well-formed XML file. To confirm, just navigate to the file's location and double-click on it to open. You will see that the file is invalid. Normally, the problems like this are caused by whatever process is used to create the file which fails to take care of entitizing the special characters, such as ampersand, \<, etc.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.