CDATA & FOR XML PATH

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
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@.vebra.com> wrote in message
news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
> 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
>
|||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>
<description><![CDATA[text...]]></description>
</details>
</info>
How can I ensure the both the html and the element containing the cdata are
rendered correctly (eg. <element> rather than '<element>...')?...Is
there any alternative way to do this that I'm missing?
Thanks
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> 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@.vebra.com> wrote in message
> news:eRrOk2kdFHA.3452@.TK2MSFTNGP10.phx.gbl...
>
|||XSLT should not make a difference between the CDATA section and content that
just had been entitized.
Did you try to take your original output (with the TYPE directive) and pass
it through your XSLT style sheet?
Alternatively, if you need to preserve your CDATA section, you need to use a
top-level EXPLICIT mode query:
select 1 as Tag, NULL as Parent, pt_description as
[details!1!description!element],pt_description as
[details!1!description!cdata]
from property where pt_id = 8957627
for xml explicit, root('info')
Best regards
Michael
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:eHmIDS0dFHA.1612@.tk2msftngp13.phx.gbl...
> 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>
> <description><![CDATA[text...]]></description>
> </details>
> </info>
> How can I ensure the both the html and the element containing the cdata
> are rendered correctly (eg. <element> rather than
> '<element>...')?...Is there any alternative way to do this that I'm
> missing?
> Thanks
> Pete
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uxdEMepdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>