I have an XML document I am trying to parse and load into my SQL 2012 database that is generated by another product. There are some elements that have what looks like HTML code bits inside the double quotes.
description="<p>Description text for this element.</p><p></p>"
Of course I get this message:
XML parsing: line 9, character 304, well formed check: no '<' in attribute value
converting to XML using this code:
DECLARE @XMLDoc XML = 'The XML document'
Here's the basic structure of the XML doc, with the problem characters:
<?xml version="1.0" encoding="UTF-8"?>
<object object_id="1111" objectname = "OName1" description="object 1 description text"/>
<object object_id="2222" objectname = "OName2" description="object 2 description text"/>
<object object_id="3333" objectname = "OName3" description="<p>object 3 description text.</p><p></p>"/>
My .NET web developer is talking trash to me because his XML parser ignores those problem characters when they are within the double quotes. Changing the document at the source is not an option.
This seems basic to TSQL and XML. It's not really getting to the parsing step. Is there another way to do this in TSQL that also ignores the items within the quotes and instead treats it as data?
Might there be a trick to replacing those items with the corresponding escaped character sequence if found inside the quotes, so the conversion can complete successfully (and I can go on with life)?
Am I stuck?
Thanks for your help.