Column names

The XML generated for a SQL result set specifies the column names of the result set in the ResultSetMetaData section and in the ResultSetData section.

The following SQL select statement specifies a result set:

select 1 as 'A>2', 2 as 'B  & 3', 3 as 'A<<b', 4 as
 	'D ''or'' e'

The result set has a single row, whose column values are 1, 2, 3, and 4. The column names of those columns are quoted identifiers that contain XML markup characters.

Since the ResultSetXml document for such a result set specifies the column names in XML attributes, the quotation marks and XML markup characters in these names must be replaced with XML entity symbols.

This problem cannot be handled with CDATA sections, since you cannot use CDATA sections in attribute values.

The following example illustrates how to replace the quotation marks and XML markup characters in the column names with XML entity symbols. A SQL script generates the ResultSetXml document for the result set, then generates the SQL script for that ResultSetXml document.

First, SQL statements generate the XML ResultSet document and store it in the variable rsx:

create variable rsx jcs.xml.resultsets.ResultSetXml
set rsx = new jcs.xml.resultsets.ResultSetXml(
'select 1 as ''A > 2'', 2 as ''b  & 3'', 
              3 as ''a<<b'', 4 as ''d ''''or'''' e'' ', 
                'none', 'yes', 'external', '' )

Then SQL statements generate the SQL script for the XML ResultSet:

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

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

select rsx>>toString()

The XML ResultSet document for this example is:

<?xml version="1.0"?>

<!DOCTYPE ResultSet SYSTEM 'ResultSet.dtd'>

<ResultSet>
   <ResultSetMetaData getColumnCount="4">
     <ColumnMetaData getColumnDisplaySize="11" getColumnLabel="A > 2" getColumnName="A > 2" getColumnType="4" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="true" />
     <ColumnMetaData getColumnDisplaySize="11" getColumnLabel="b & 3" getColumnName="b & 3" getColumnType="4" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="true" />
     <ColumnMetaData getColumnDisplaySize="11" getColumnLabel="a<<b" getColumnName="a<<b" getColumnType="4" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="true" />
     <ColumnMetaData getColumnDisplaySize="11" getColumnLabel="d 'or' e" getColumnName="d 'or' e" getColumnType="4" getPrecision="0" getScale="0" isAutoIncrement="false" isCurrency="false" isDefinitelyWritable="false" isNullable="false" isSigned="true" />
   </ResultSetMetaData>
   <ResultSetData>
     <Row>
       <Column name="A > 2">1</Column>
       <Column name="b & 3">2</Column>
       <Column name="a<<b">3</Column>
       <Column name="d 'or' e">4</Column>
     </Row>
   </ResultSetData>
 </ResultSet>

The following is the output SQL script for this example:

set quoted_identifier on 
 create table markup_col_names ( 
	'A > 2' integer not null , 
	'b  & 3' integer not null , 
	'a<<b' integer not null , 
	'd ''or'' e' integer not null