XML column updates in embedded SQL applications

When you update or insert data into XML columns of a Db2 table, the input data must be in the textual XML format.

The encoding of XML data can be derived from the data itself, which is known as internally encoded data, or from external sources, which is known as externally encoded data. XML data that is sent to the database server as binary data is treated as internally encoded data. XML data that is sent to the database server as character data is treated as externally encoded data.

Externally encoded data can have internal encoding. That is, the data might be sent to the database server as character data, but the data contains encoding information. Db2 does not enforce consistency of the internal and external encoding. When the internal and external encoding information differs, the external encoding takes precedence. However, if there is a difference between the external and internal encoding, intervening character conversion might have occurred on the data, and there might be data loss.

Character data in XML columns is stored in UTF-8 encoding. The database server handles conversion of the data from its internal or external encoding to UTF-8.

The following examples demonstrate how to update XML columns in assembler, C, COBOL, and PL/I applications. The examples use a table named MYCUSTOMER, which is a copy of the sample CUSTOMER table.

Example

The following example shows an assembler program that inserts data from XML AS BLOB, XML AS CLOB, and CLOB host variables into an XML column. The XML AS BLOB data is inserted as binary data, so the database server honors the internal encoding. The XML AS CLOB and CLOB data is inserted as character data, so the database server honors the external encoding.

**********************************************************************
* UPDATE AN XML COLUMN WITH DATA IN AN XML AS CLOB HOST VARIABLE     *
**********************************************************************
         EXEC SQL                                                      +
               UPDATE MYCUSTOMER                                       +
               SET INFO = :XMLBUF                                      +
               WHERE CID = 1000
**********************************************************************
* UPDATE AN XML COLUMN WITH DATA IN AN XML AS BLOB HOST VARIABLE     *
**********************************************************************
         EXEC SQL                                                      +
               UPDATE MYCUSTOMER                                       +
               SET INFO = :XMLBLOB                                     +
               WHERE CID = 1000
**********************************************************************
* UPDATE AN XML COLUMN WITH DATA IN A CLOB HOST VARIABLE. USE        *
* THE XMLPARSE FUNCTION TO CONVERT THE DATA TO THE XML TYPE.         *
**********************************************************************
         EXEC SQL                                                      +
               UPDATE MYCUSTOMER                                       +
               SET INFO = XMLPARSE(DOCUMENT :CLOBBUF)                  +
               WHERE CID = 1000
…
          LTORG
******************************
* HOST VARIABLE DECLARATIONS *
******************************
XMLBUF   SQL TYPE IS XML AS CLOB 10K
XMLBLOB  SQL TYPE IS XML AS BLOB 10K
CLOBBUF  SQL TYPE IS CLOB 10K

Example

The following example shows a C language program that inserts data from XML AS BLOB, XML AS CLOB, and CLOB host variables into an XML column. The XML AS BLOB data is inserted as binary data, so the database server honors the internal encoding. The XML AS CLOB and CLOB data is inserted as character data, so the database server honors the external encoding.

/******************************/
/* Host variable declarations */
/******************************/
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS XML AS CLOB( 10K ) xmlBuf;
SQL TYPE IS XML AS BLOB( 10K ) xmlblob;
SQL TYPE IS CLOB( 10K ) clobBuf;
EXEC SQL END DECLARE SECTION;
/******************************************************************/
/* Update an XML column with data in an XML AS CLOB host variable */
/******************************************************************/
EXEC SQL UPDATE MYCUSTOMER SET INFO = :xmlBuf where CID = 1000;
/******************************************************************/
/* Update an XML column with data in an XML AS BLOB host variable */
/******************************************************************/
 EXEC SQL UPDATE MYCUSTOMER SET INFO = :xmlblob where CID = 1000;
/******************************************************************/
/* Update an XML column with data in a CLOB host variable. Use    */
/* the XMLPARSE function to convert the data to the XML type.     */
/******************************************************************/
EXEC SQL UPDATE MYCUSTOMER SET INFO = XMLPARSE(DOCUMENT :clobBuf) where CID = 1000;

Example

The following example shows a COBOL program that inserts data from XML AS BLOB, XML AS CLOB, and CLOB host variables into an XML column. The XML AS BLOB data is inserted as binary data, so the database server honors the internal encoding. The XML AS CLOB and CLOB data is inserted as character data, so the database server honors the external encoding.

******************************
* Host variable declarations *
******************************
 01 XMLBUF USAGE IS SQL TYPE IS XML as CLOB(10K).
 01 XMLBLOB  USAGE IS SQL TYPE IS XML AS BLOB(10K).
 01 CLOBBUF USAGE IS SQL TYPE IS CLOB(10K). 
*******************************************************************
* Update an XML column with data in an XML AS CLOB host variable  *
*******************************************************************
 EXEC SQL UPDATE MYCUSTOMER SET INFO = :XMLBUF where CID = 1000.
*******************************************************************
* Update an XML column with data in an XML AS BLOB host variable  *
*******************************************************************
 EXEC SQL UPDATE MYCUSTOMER SET INFO = :XMLBLOB where CID = 1000.
*******************************************************************
* Update an XML column with data in a CLOB host variable. Use     *
* the XMLPARSE function to convert the data to the XML type.      *
*******************************************************************
 EXEC SQL UPDATE MYCUSTOMER SET INFO = XMLPARSE(DOCUMENT :CLOBBUF) where CID = 1000.

Example

The following example shows a PL/I program that inserts data from XML AS BLOB, XML AS CLOB, and CLOB host variables into an XML column. The XML AS BLOB data is inserted as binary data, so the database server honors the internal encoding. The XML AS CLOB and CLOB data is inserted as character data, so the database server honors the external encoding.

/******************************/
/* Host variable declarations */
/******************************/
 DCL
  XMLBUF SQL TYPE IS XML AS CLOB(10K),
  XMLBLOB SQL TYPE IS XML AS BLOB(10K),
  CLOBBUF SQL TYPE IS CLOB(10K);
/*******************************************************************/
/* Update an XML column with data in an XML AS CLOB host variable  */
/*******************************************************************/
 EXEC SQL UPDATE MYCUSTOMER SET INFO = :XMLBUF where CID = 1000;
/*******************************************************************/
/* Update an XML column with data in an XML AS BLOB host variable  */
/*******************************************************************/
 EXEC SQL UPDATE MYCUSTOMER SET INFO = :XMLBLOB where CID = 1000;
/*******************************************************************/
/* Update an XML column with data in a CLOB host variable. Use     */
/* the XMLPARSE function to convert the data to the XML type.      */
/*******************************************************************/
 EXEC SQL UPDATE MYCUSTOMER SET INFO = XMLPARSE(DOCUMENT :CLOBBUF) where CID = 1000;