In SQL how to form an XML output with one of attribute value as XML without <,> encoded

Question

I am trying for a query which returns and XML output which has one of the attribute value as XML without <, /> encoded.

Example:

<Event EventLogId="124018" EventCategoryCode="ABC" 
       EventTypeCode="ERROR" 
       xmlObject="<attributes><attribute>A1</attribute></attributes>" />

<Event EventLogId="124019" EventCategoryCode="DEF" 
       EventTypeCode="Warning" 
       xmlObject="<attributes><attribute>A2</attribute></attributes>" />

I tried this query

SELECT  
    EventLogId  AS EventLogId 
    ,EventCategoryCode AS EventCategoryCode        
    ,EventTypeCode AS EventTypeCode      
    ,CAST(Event.xmlObject AS NVARCHAR(MAX)) as xmlObject
FROM 
    EventLog Event (NOLOCK) 
FOR XML AUTO

But I am getting this output

<Event EventLogId="124018" EventCategoryCode="ABC" 
       EventTypeCode="ERROR" 
       xmlObject="&lt;attributes&gt;&lt;attribute&gt;A1&lt;/attribute&gt;&lt;/attributes&gt;" />

<Event EventLogId="124019" EventCategoryCode="DEF" 
       EventTypeCode="Warning" 
       xmlObject="&lt;attributes&gt;&lt;attribute&gt;A2&lt;/attribute&lt;&lt;/attributes&gt;" />

I want <, /> in place of < and >

And any XML reader should be able to translate/de-entitize it back for you. — RBarryYoung
– RBarryYoung, Commented Sep 10, 2015 at 14:39

Hogan · Accepted Answer · 2015-09-10 14:35:46Z

2

It is not valid to have non-escaped (i.e. not entity encoded) characters in attributes or anywhere in XML.

To be 100% accurate, before I get lots of comments, you can use the special DATA syntax, but that is not legal in an attribute. Also it is rare to see this feature used.

You can read the standard here (http://www.w3.org/TR/xml/) The base XML standard is actually quite short and should take you less than an hour to read.

edited Sep 10, 2015 at 14:35

answered Sep 10, 2015 at 13:38

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In SQL how to form an XML output with one of attribute value as XML without <,> encoded

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related