I am confused on xml and cdata.
I have a need to store HTML in XML elements for later display on a web page.

If I create something like
<htmlToDisplay>
![CDATA[ this is <b>bold</b>]]
</htmlToDisplay>

and then insert it into an html column in SQL.
How can I get the cdata out?
If I do a select on the table and then click on the xml link, the cdata is
gone and innards have been ESC'd

How do I go about storing html in xml and then displaying the non-esc stuff
on a web page?

First question, why do you have to store the HTML inside XML?

Second, would the following work? Note that a CDATA section is equivalent to
the escaped representation according to the XML spec.

declare @x xml;

set @x = '<htmlToDisplay>

<![CDATA[ this is <b>bold</b>]]>

</htmlToDisplay>';

select @x.value('/htmlToDisplay[1]','nvarchar(100)')

Best regards

Michael

Show quoteHide quote

Hi,
From your description, I understand that:
You would like to store HTML in XML elements for later display on a web
page; however you found that the content of CDATA failed to be displayed.
When you performed a query on the table by clicking on the xml link, the
cdata was gone and innards had been ESC'd.
If I have misunderstood, please let me know.

I notice that the demo xml script was not right since the correct format of
CDATA should be "<!CDATA[" and "]]>". Was this just a writing error?

Also, I would like to know what the 'ESC'd' means in your description.
I performed a test by the following steps:
1. CREATE TABLE [dbo].[TestXML](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [XMLContent] [xml] NULL,
CONSTRAINT [PK_TestXML] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

2. insert into TestXML values('<htmlToDisplay><![CDATA[ this is
<b>bold</b>]]></htmlToDisplay>')

3. SELECT * FROM TESTXML
    The result is:
     <htmlToDisplay> this is &lt;b&gt;bold&lt;/b&gt;</htmlToDisplay>
     (Did you mean this ESC'd ?)

SQL Server has parsed the CDATA content to store it in the xml column. The
result is well formed to be displayed in Windows Explorer.
You can click the link in SSMS, get a xml file, save it and open the file
in Windows Explorer, then you will see the right content.

Look forward to your response and feel free to let me know your current
concerns.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

The "data" I need to store is HTML.  I don't know if you're not suppose to
store HTML in XML, but that's what my data is, so that's what I need to store
and eventually display in a webpage.

I only need the CDATA if the html or text is not well formed ( e.g. "<x" ). 
But if it's not well formed it may blow up when displayed so, I am not going
to use the CDATA for the xml element.  Just catch any errors early I hope.

The way I am getting my html to display on a web page is via an xslt  by
doing a
   <xsl:copy-of select="prompt"  />
The
<xsl:value-of select="prompt" disable-output-escaping="yes"  />
<xsl:value-of select="prompt" disable-output-escaping="no"  />
<xsl:value-of select="prompt/text()"/>
<xsl:value-of select="prompt/text()" disable-output-escaping="yes"/>
don't let the HTML display.

I don't know if this is the correct way, but it seems to work.

Does my value method solution work for you?

Best regards
Michael

Show quoteHide quote

Not quite but I could see where the
declare @x xml;select @x.value('/htmlToDisplay[1]','nvarchar(100)')
could come in handy.

I think what is missing when you want create xml output from sql and you
have html in the database and the html data is NOT well formed (ie. must be
in a cdata section), the translation using FOR XML is not easy or may not be
even possible.

I think this scenario would typically occur when you have either markup
commands in your data or had old html in your database and want to
process/fix it using xlst.

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...
the XML "data model" is not making a distinction between a CDATA section or
the data directly entitized. Since CDATA sections have some wierd quirks,
FOR XML decided to entitize HTML data.

Best regards
Michael

Show quoteHide quote

Hi Chuck,
I am sorry that I misunderstood your issue.
I had thought that your concern was that SQL Server converted the marks in
CDATA  into escaped characters in a xml field.

From your response, I think that your concern is how you can display your
CDATA conent. I agree to your resolution. By using XSLT it is easy to
output the HTML content in the style that you want.
Also, you can use XSL to fetch the CDATA.   
The syntax is as following:
<xsl:template match="htmlDisplay">
      <xsl:apply-templates/>
  </xsl:template>

Anyway I am glad to hear that you have resolved this issue. If you have any
other questions or concerns, please feel free to let me know.

Have a great day!


Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Chuck P wrote:
Read the FAQ at http://xml.silmaril.ie/authors/cdata/

///Peter

thanks, good reference

Show quoteHide quote

Manuela Importing File Xml into table with Sql Server 2005
Schema Includes Another Schema
weird xquery error
new to xQuery -- problem
schema collection problem?