Using CDATA sections

The cdata parameter of the ResultSetXml constructor indicates which (if any) columns of the SQL result set contain character data to be bracketed as CDATA sections in the output XML. The cdata parameter can be “all,” “none,” or a string of zero or one characters, where a “1” in the n^th position indicates that the n^th column should be bracketed as a CDATA section.

For example, create the table cdata in which data values in columns 2, 3, and 4 contain XML markup characters that must be bracketed as CDATA section in the output:

create table cdata (
 	id int, 
 	a varchar(250), 
 	b varchar(250), 
 	c varchar(250)
    )
 go

insert into cdata values (
 	1, 
 	'<p>some samples:</p><ol><li>first</li>
 		<li>second</li></ol>', 
 	'x > y || w  & z', 
 	'x > y || w & z'
 	)

The following SQL statement generates an XML ResultSet document for this table, specifying a value “0111” for the cdata parameter.

create variable rsx jcs.xml.resultset.ResultSetXml
set rsx = new jcs.xml.resultset.ResultSetXml
("select * from cdata", '0111', 'yes', 'external', '')

The following SQL statement generates a SQL script for that XML ResultSet:

create variable script java.lang.String
go
set script =
(select rsx>>toSqlScript('markup_col_names',
'col_', 'both', 'yes'))

The following select statement returns the contents of the XML ResultSet:

select rsx>>toString()

This is the XML ResultSet:

<?xml version="1.0"?>

<!DOCTYPE ResultSet SYSTEM 'ResultSet.dtd'>

<ResultSet>
   <ResultSetMetaData getColumnCount="4">
     <ColumnMetaData getColumnDisplaySize="11" getColumnLabel="id" getColumnName="id" getColumnType="4" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="true" />
     <ColumnMetaData getColumnDisplaySize="250" getColumnLabel="a" getColumnName="a" getColumnType="12" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="false" />
     <ColumnMetaData getColumnDisplaySize="250" getColumnLabel="b" getColumnName="b" getColumnType="12" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="false" />
     <ColumnMetaData getColumnDisplaySize="250" getColumnLabel="c" getColumnName="c" getColumnType="12" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="false" />
   </ResultSetMetaData>
   <ResultSetData>
     <Row>
       <Column name="id">1</Column>
       <Column name="a">
 		<![CDATA[<p>some samples:
 			</p><ol><li>first</li><li>second</li></ol>]]>
       </Column>
       <Column name="b">
 		<![CDATA[x > y || w  & z]]>
       </Column>
       <Column name="c">
 		<![CDATA[x > y || w & z]]>
       </Column>
     </Row>
   </ResultSetData>
 </ResultSet>

This is the SQL script:

set quoted_identifier on 
 create table markup_col_names ( 
    id integer not null , 
    a varchar (250) not null , 
    b varchar (250) not null , 
    c varchar (250) not null 
 )
 insert into markup_col_names values (
    1, 
    '<p>some samples:</p><ol><li>first</li><li>second</li></ol>', 
    'x > y || w  & z', 
    'x > y || w & z'
    )