SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Element Attribute Data Issues


XML Element Attribute Data Issues

Author
Message
Craigmeister
Craigmeister
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 586
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.


Avi1
Avi1
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 581
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 &lt;
> to &gt;
& to &amp;



Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159392 Visits: 23329
Either the value has to be URL encoded as already mentioned or the tag has to be changed to CDATA
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search