DB2 Version 9.7 for Linux, UNIX, and Windows

REC2XML scalar function


>>-REC2XML--(--decimal-constant--,--format-string--------------->

                      .----------------.      
                      V                |      
>--,--row-tag-string----,--column-name-+--)--------------------><

The schema is SYSIBM.

The REC2XML function returns a string formatted with XML tags, containing column names and column data. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

decimal-constant

The expansion factor for replacing column data characters. The decimal value must be greater than 0.0 and less than or equal to 6.0. (SQLSTATE 42820).

The decimal-constant value is used to calculate the result length of the function. For every column with a character data type, the length attribute of the column is multiplied by this expansion factor before it is added in to the result length.

To specify no expansion, use a value of 1.0. Specifying a value less than 1.0 reduces the calculated result length. If the actual length of the result string is greater than the calculated result length of the function, then an error is raised (SQLSTATE 22001).

format-string

The string constant that specifies which format the function is to use during execution.

The format-string is case-sensitive, so the following values must be specified in uppercase to be recognized.

COLATTVAL or COLATTVAL_XML

These formats return a string with columns as attribute values.


>>-<--row-tag-string-->----------------------------------------->

   .----------------------------------------------------------------------.   
   V                                                                      |   
>----<--column-name--=--"column-name"--+->--column-value--</--column-->-+-+-->
                                       '-null="true"--/>----------------'     

>--</--row-tag-string-->---------------------------------------><

Column names may or may not be valid XML attribute values. For column names which are not valid XML attribute values, character replacement is performed on the column name before it is included in the result string.

Column values may or may not be valid XML element names. If the format-string COLATTVAL is specified, then for the column names which are not valid XML element values, character replacement is performed on the column value before it is included in the result string. If the format-string COLATTVAL_XML is specified, then character replacement is not performed on column values (although character replacement is still performed on column names).

row-tag-string

A string constant that specifies the tag used for each row. If an empty string is specified, then a value of 'row' is assumed.

If a string of one or more blank characters is specified, then no beginning row-tag-string or ending row-tag-string (including the angle bracket delimiters) will appear in the result string.

column-name

A qualified or unqualified name of a table column. The column must have one of the following data types (SQLSTATE 42815):

numeric (SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE)
character string (CHAR, VARCHAR; a character string with a subtype of BIT DATA is not allowed)
datetime (DATE, TIME, TIMESTAMP)
a user-defined type based on one of the above types

The same column name cannot be specified more than once (SQLSTATE 42734).

The result of the function is VARCHAR. The maximum length is 32 672 bytes (SQLSTATE 54006).

Consider the following invocation:

   REC2XML (dc, fs, rt, c₁, c₂, ..., c_n)

If the value of "fs" is either "COLATTVAL" or "COLATTVAL_XML", then the result is the same as this expression:

'<' CONCAT rt CONCAT '>' CONCAT y₁ CONCAT y₂
CONCAT ... CONCAT y_n CONCAT '</' CONCAT rt CONCAT '>'

where y_n is equivalent to:

'<column name="' CONCAT xvc_n CONCAT vn

and vn is equivalent to:

'">' CONCAT r_n CONCAT '</column>'

if the column is not null, and

'" null="true"/>'

if the column value is null.

xvc_n is equivalent to a string representation of the column name of c_n, where any characters appearing in Table 2 are replaced with the corresponding representation. This ensures that the resulting string is a valid XML attribute or element value token.

The r_n is equivalent to a string representation as indicated in Table 1

Table 1. Column Values String Result
Data type of c_n	r_n
CHAR, VARCHAR	The value is a string. If the format-string does not end in the characters "_XML", then each character in c_n is replaced with the corresponding replacement representation from Table 2, as indicated. The length attribute is: dc * the length attribute of c_n.
SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE	The value is LTRIM(RTRIM(CHAR(c_n))). The length attribute is the result length of CHAR(c_n). The decimal character is always the period ('.') character.
DATE	The value is CHAR(c_n,ISO). The length attribute is the result length of CHAR(c_n,ISO).
TIME	The value is CHAR(c_n,JIS). The length attribute is the result length of CHAR(c_n,JIS)
TIMESTAMP	The value is CHAR(c_n). The length attribute is the result length of CHAR(c_n).

Character replacement:

Depending on the value specified for the format-string, certain characters in column names and column values will be replaced to ensure that the column names form valid XML attribute values and the column values form valid XML element values.

Table 2. Character Replacements for XML Attribute Values and Element Values
Character	Replacement
<	<
>	>
"	"
&	&
'	'

Examples:

Note: REC2XML does not insert blank spaces or new line characters in the output. All example output has been formatted to enhance readability.

Using the DEPARTMENT table in the sample database, format the department table row, except the DEPTNAME and LOCATION columns, for department 'D01' into an XML string. Since the data does not contain any of the characters which require replacement, the expansion factor will be 1.0 (no expansion). Also note that the MGRNO value is null for this row.
```
   SELECT REC2XML (1.0, 'COLATTVAL', '', DEPTNO, MGRNO, ADMRDEPT)
     FROM DEPARTMENT
     WHERE DEPTNO = 'D01'
```
This example returns the following VARCHAR(117) string:
```
   <row>
   <column name="DEPTNO">D01</column>
   <column name="MGRNO" null="true"/>
   <column name="ADMRDEPT">A00</column>
   </row>
```
A 5-day university schedule introduces a class named '&43<FIE' to a table called CL_SCHED, with a new format for the CLASS_CODE column. Using the REC2XML function, this example formats an XML string with this new class data, except for the class end time.
The length attribute for the REC2XML call (see below) with an expansion factor of 1.0 would be 128 (11 for the '<row>' and '</row>' overhead, 21 for the column names, 75 for the '<column name=', '>', '</column>' and double quotes, 7 for the CLASS_CODE data, 6 for the DAY data, and 8 for the STARTING data). Since the '&' and '<' characters will be replaced, an expansion factor of 1.0 will not be sufficient. The length attribute of the function will need to support an increase from 7 to 14 bytes for the new format CLASS_CODE data.
However, since it is known that the DAY value will never be more than 1 digit long, an unused extra 5 units of length are added to the total. Therefore, the expansion only needs to handle an increase of 2. Since CLASS_CODE is the only character string column in the argument list, this is the only column data to which the expansion factor applies. To get an increase of 2 for the length, an expansion factor of 9/7 (approximately 1.2857) would be needed. An expansion factor of 1.3 will be used.
```
   SELECT REC2XML (1.3, 'COLATTVAL', 'record', CLASS_CODE, DAY, STARTING)
     FROM CL_SCHED
     WHERE CLASS_CODE = '&43<FIE'
```
This example returns the following VARCHAR(167) string:
```
   <record>
   <column name="CLASS_CODE">&amp;43&lt;FIE</column>
   <column name="DAY">5</column>
   <column name="STARTING">06:45:00</column>
   </record>
```
Assume that new rows have been added to the EMP_RESUME table in the sample database. The new rows store the resumes as strings of valid XML. The COLATTVAL_XML format-string is used so character replacement will not be carried out. None of the resumes are more than 3500 bytes in length. The following query is used to select the XML version of the resumes from the EMP_RESUME table and format it into an XML document fragment.
```
   SELECT REC2XML (1.0, 'COLATTVAL_XML', 'row', EMPNO, RESUME_XML)
     FROM (SELECT EMPNO, CAST(RESUME AS VARCHAR(3500)) AS RESUME_XML
       FROM EMP_RESUME
       WHERE RESUME_FORMAT = 'XML')
     AS EMP_RESUME_XML
```
This example returns a row for each employee who has a resume in XML format. Each returned row will be a string with the following format:
```
   <row>
   <column name="EMPNO">{employee number}</column>
   <column name="RESUME_XML">{resume in XML}</column>
   </row>
```
Where "{employee number}" is the actual EMPNO value for the column and "{resume in XML}" is the actual XML fragment string value that is the resume.