September 27, 2017 at 7:16 am
Greetings,
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.
Example:
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"?>
<recordset recordCount="900"
<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>"/>
</recordset>
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.
My questions:
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.
September 27, 2017 at 8:39 am
You need to approach source system, these XML tags in the XMLdoc need to be replaced with escape character. You also check if your load process is changing these escape character back to actual tags and generating this issue..
Below are some examples of respective escape character< to <
> to >
& to &
September 28, 2017 at 4:26 am
Either the value has to be URL encoded as already mentioned or the tag has to be changed to CDATA
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply