my recent reads..

Generating CLOB/CDATA elements with XMLDB


Generating CDATA elements with Oracle XMLDB recently got a good airing in the XMLDB forums.

I won't reiterate the discussion there, but offer a summary and some sources.

It seems the current state of affairs is that if you need to generate large text elements with XMLDB you have two options:


  • use DBMS_LOB procedural code to manually construct a CDATA element, or
  • use XMLTYPE views to construct an XML-encoded element


In both cases you need to be careful not to do anything that casts or converts to varchar to avoid the inherent size limitations.

Note that the XML-encoding in XMLTYPE views is automatic, and I currently don't know how to tell it not to encode but rather quote as CDATA.

Some sources and examples:


  • clob-cdata.pl is a Perl script using DBI that demonstrates how to generate an XMLTYPE view over an arbitrary CLOB element, without using XMLSchema. In this case, the CLOB will be automatically XML-encoded [clob-cdata-nonschema.sql is just the plain SQL].
  • clob-cdata-schema.sql shows how you can do a similar thing, but using an XMLSchema definition.
  • clob-cdata-small.sql shows how you can create CDATA elements where the text size is small using the XMLCdata function