XML parsing error: why is semicolon expected?
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>
Answer
<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 &.