Hi All,
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDA
TA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![C
DATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
Showing posts with label cdata. Show all posts
Showing posts with label cdata. Show all posts
Sunday, February 19, 2012
CDATA In .value() Method Result
Hi All,
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]
First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
I'm trying to select data out from an xml type. One of my elements is cdata
wrapped and when I select it I'm getting the resulting cdata wrapper rather
than the data by itself. I've included my sql and the results I see below.
Any help is appreciated.
Thanks
DECLARE @.xml xml
SELECT @.xml =
'<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
Declare @.XMLTable table (ID int, Name varchar (150))
INSERT INTO @.XMLTable (ID, Name)
SELECT T.c.query('ID').value('.','int')
, T.c.query('Name').value('.','varchar(150)')
FROM @.xml.nodes('/Udpates/Udpate') T(c)
SELECT * FROM @.XMLTable
Results:
123 ![CDATA[somename]]
456 ![CDATA[someothername]]
First off that's not a CDATA wrapper. Try using <![CDATA[ ... ]]> instead.
"JA" <buosc@.hotmail.com> wrote in message
news:uLkq3YlJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm trying to select data out from an xml type. One of my elements is
> cdata wrapped and when I select it I'm getting the resulting cdata wrapper
> rather than the data by itself. I've included my sql and the results I
> see below. Any help is appreciated.
> Thanks
>
> DECLARE @.xml xml
> SELECT @.xml =
> '<Udpates><Udpate><ID>123</ID><Name>![CDATA[somename]]</Name></Udpate><Udpate><ID>456</ID><Name>![CDATA[someothername]]</Name></Udpate></Udpates>'
> Declare @.XMLTable table (ID int, Name varchar (150))
> INSERT INTO @.XMLTable (ID, Name)
> SELECT T.c.query('ID').value('.','int')
> , T.c.query('Name').value('.','varchar(150)')
> FROM @.xml.nodes('/Udpates/Udpate') T(c)
> SELECT * FROM @.XMLTable
>
> Results:
> 123 ![CDATA[somename]]
> 456 ![CDATA[someothername]]
>
CDATA and SQL Server 2005
Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?
CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?
CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
CDATA and SQL Server 2005
Hi I am using the June CTP and I am wondering about Inserting XML Documents
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
with CDATA Sections.
I insert it this way.
UPDATE ExportTable
SET ConfigFile =
'<Export Assembly="Test.dll">
<![CDATA[<ExportData>"</ExportData>]]>
</Export>'
Here is what I see when I select ConfigFile
<Export Assembly="Test.dll"><ExportData>"</ExportData>
</Export>
Is there a way to set the value and preserve the CDATA section when I select
it?CDATA sections are a means to INPUT several characters that are reserved in
XML such as <>&'". CDATA sections are not preserved in the XML data model,
so we cannot roundtrip them.
However, the data that you see should contain the entitized < instead of
< etc. Your tool may show it de-entitized (but then probably in a different
color).
Best regards
Michael
"jones6" <jones6@.discussions.microsoft.com> wrote in message
news:13D5EFD4-93C0-40A2-9339-9BF54372E715@.microsoft.com...
> Hi I am using the June CTP and I am wondering about Inserting XML
> Documents
> with CDATA Sections.
> I insert it this way.
> UPDATE ExportTable
> SET ConfigFile =
> '<Export Assembly="Test.dll">
> <![CDATA[<ExportData>"</ExportData>]]>
> </Export>'
> Here is what I see when I select ConfigFile
> <Export Assembly="Test.dll"><ExportData>"</ExportData>
> </Export>
> Is there a way to set the value and preserve the CDATA section when I
> select
> it?
cdata all columns automatically
I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation :(And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegroups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
>
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation :(And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegroups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
>
cdata all columns automatically
> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
Did anyone have a solution for this? I need the cdata tags for doublebyte v
alues.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilitiesFOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities
cdata all columns automatically
I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation
And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegr oups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
>
|||
> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
Did anyone have a solution for this? I need the cdata tags for doublebyte values.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||FOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation
And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegr oups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
>
|||
> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
Did anyone have a solution for this? I need the cdata tags for doublebyte values.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||FOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities
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...
>
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...
>
Subscribe to:
Posts (Atom)