CODEWITHSUNDEEP
sqlxml
Arjun Palwai
Arjun Palwai

Reputation: 29

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

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 &lt; and &gt;

Upvotes: 2

Views: 43

Answers (1)

Hogan
Hogan

Reputation: 70513

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.

Upvotes: 2

Related Questions