XML parsing error: why is semicolon expected? [duplicate]

Question

I'm a newbie struggling with trying to import XML from an external third-party into SQL Server. I tried this SQL with the XML below and it pukes with "XML parsing: line 10, character 81, semicolon expected" in the middle of the Name tag - WHY?? Why would it expect a semicolon in the middle of a value? Any suggestions?? Thanks in advance!

CREATE TABLE XMLD (Id INT IDENTITY PRIMARY KEY, XMLData XML)

INSERT INTO XMLD(XMLData) SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM OPENROWSET(BULK 'D:\Details-WaUSA-1027.xml', SINGLE_BLOB) AS x;

ERROR: XML parsing: line 10, character 81, semicolon expected

<OnlineReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="1.0" From="10/27/2016" To="10/27/2016" xsi:noNamespaceSchemaLocation="Responses.xsd"> <Site ID="130421" Name="Prod-B2B-PC-Server"> <Responses/> </Site> <Site ID="130739" Name="Prod-B2B-PC-Item"> <Responses> <Response> <ID>86947636057</ID> <Name> https://store.shop4e.com/node/Shop4e/index.php?route=product/product&product_id=18922&userId=150802 </Name> <URL/> <Monitoring-Date-Time>10/27/2016 12:45:39 PM</Monitoring-Date-Time> <ResponseStartTime>2016-10-27 12:45:39.170</ResponseStartTime> <Duration>7954</Duration> <Status>S</Status> <Monitoring-Location>WA, USA</Monitoring-Location> </Site> </OnlineReport>

Dissolute

Wasn't there an ampersand & somewhere before the position? – Klina

Nope - I pasted the first few lines of the generated XML exactly the way it was generated except changed one name. You think it's the ampersands in the URL? – Dissolute

How was this XML generated? It is not valid... This happens often if people construct the XML on string-level... – Spondaic

Answer 1

<Name> https://store.shop4e.com/node/Shop4e/index.php?route=product/product&product_id=18922&userId=150802 </Name>

You have an invalid XML entity in there &product_id...... It is missing the terminating semicolon.

You need to either replace the raw & with & or wrap the text in a CDATA section.

The special characters to keep an eye out for are <, >, and &.

Quadrivalent

That was it! Thank you!! :) – Dissolute

Replace('yourstring','&','&') worked for me. Thanks. – Naturism