Re: CDATA & FOR XML PATH

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Michael,

Thanks for your reply. My belief is that the use of the cdata directive is
necessary as I have html content stored in the database and need to be able
to be rendered as html when producing an xslt transformation. is there an
alternative way that you know of to do this in SQL Server 2005.

When I try using the same statement;

select
pt_description as 'description',

(select 1 as Tag, NULL as Parent, pt_description as [description!1!!cdata]
from property where pt_id = 8957627 for xml explicit)

from pt

where pt_id = 9999999

for xml path ('details'), root('info')

without using the 'TYPE' directive the result displays as follows;

<info>
<details>
<description>text....</description>
&lt;description&gt;&lt;![CDATA[text....]]&gt;&lt;/description&gt;
</details>
</info>

How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '&lt;element&gt;...')?...Is
there any alternative way to do this that I'm missing?

Thanks

Pete



"Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uxdEMepdFHA.3808@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Pete
>
> If you use a FOR XML expression with the TYPE directive, you get an XML
> datatype. And the XML datatype (since it is based on the XQuery Datamodel)
> does not preserve the CDATA section information.
>
> So the only way to preserve CDATA is by using EXPLICIT mode without TYPE
> directive at the top.
>
> Now, I would like to better understand why you want to generate a CDATA
> section in the first place. The only impact it has is to allow people to
> author certain XML content without having to explicitly entitize
> characters such as <, & etc.. Why would that be important during
> serializing a FOR XML result?
>
> Thanks
> Michael
>
> "Pete Roberts" <peter.roberts@xxxxxxxxx> wrote in message
> news:eRrOk2kdFHA.3452@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi,
>>
>> Using a nested FOR XML EXPLICIT statement within an FOR XML PATH query is
>> it possible to create a node creating cdata?
>>
>> For example, the statement;
>>
>> select
>>
>> pt_description as 'description',
>>
>> (select 1 as Tag, NULL as Parent, pt_description as
>> [description!1!!cdata] from property where pt_id = 8957627 for xml
>> explicit, type)
>>
>> from pt
>>
>> where pt_id = 9999999
>>
>> for xml path ('details'), root('info')
>>
>> Produces;
>> <info>
>>
>> <details>
>>
>> <description>text....</description>
>>
>> <description>text....</description>
>>
>> </details>
>>
>> </info>
>>
>> Using the nested for xml explicit I was hoping to be able to display the
>> second description node as;
>>
>> <description><![CDATA[text....]]></description>
>>
>> When the 'type' directive is specified it ignores the fact it should
>> contain cdata.
>>
>> Am I missing something?....Is this possible to do?
>>
>> Thanks
>>
>> Pete
>>
>>
>
>


.

Relevant Pages

  • Re: CDATA & FOR XML PATH
    ... XSLT should not make a difference between the CDATA section and content that ... it through your XSLT style sheet? ... for xml explicit, root ...
    (microsoft.public.sqlserver.xml)
  • Re: xml cdata
    ... Non-wellformed HTML will have to be entitized when you include it into XML. ... The CDATA section is only one of several syntactic ways of achieving that... ... FOR XML decided to entitize HTML data. ...
    (microsoft.public.sqlserver.xml)
  • Re: TAPI 3.0 call attached data
    ... The format of the Call Attached Data is XML. ... XML file with CallAttchedData represents one or multiple data lists ... Root element is CallAttachedData. ... version CDATA #FIXED "1.0" ...
    (microsoft.public.win32.programmer.tapi)
  • Need advice: What is the best way; CDATA or normal xml tag
    ... I've manage to create the HTML but i'm stock with the xml ... I found a solution using CDATA, but i'm not sure if it's an elegant ... i always try to create code that is ok and elegant. ...
    (comp.text.xml)
  • Re: Adding a complex string to config file
    ... I would provide some additional information for you to learn how to escape string embedded in xml file. ... The other way is using CDATA Sections. ... We welcome your comments and suggestions about how we can improve the support we provide to you. ... The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. ...
    (microsoft.public.dotnet.framework)