XML data retrieval in embedded SQL applications

In an embedded SQL application, if you retrieve the data into a character host variable, Db2 converts the data from the UTF-8 encoding scheme to the application encoding scheme. If you retrieve the data into binary host variable, Db2 does not convert the data to another encoding scheme.

The output data is in the textual XML format.

Db2 might add an XML encoding specification to the retrieved data, depending on whether you call the XMLSERIALIZE function when you retrieve the data. If you do not call the XMLSERIALIZE function, Db2 adds the correct XML encoding specification to the retrieved data. If you call the XMLSERIALIZE function, Db2 adds an internal XML encoding declaration for UTF-8 encoding if you specify INCLUDING XMLDECLARATION in the function call. When you use INCLUDING XMLDECLARATION, you need to ensure that the retrieved data is not converted from UTF-8 encoding to another encoding.

The following examples demonstrate how to retrieve data from 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 retrieves data from an XML column into XML AS BLOB, XML AS CLOB, and CLOB host variables. The data that is retrieved into an XML AS BLOB host variable is retrieved as binary data, so the database server generates an XML declaration with UTF-8 encoding. The data that is retrieved into an XML AS CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration that is consistent with the external encoding. The data that is retrieved into a CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration. That declaration might not be consistent with the external encoding.

**********************************************************************
* RETRIEVE XML COLUMN DATA INTO AN XML AS CLOB HOST VARIABLE         *
**********************************************************************
         EXEC SQL                                                      +
               SELECT INFO                                             +
               INTO :XMLBUF                                            +
               FROM MYCUSTOMER                                         +
               WHERE CID = 1000
**********************************************************************
* RETRIEVE XML COLUMN DATA INTO AN XML AS BLOB HOST VARIABLE         *
**********************************************************************
         EXEC SQL                                                      +
               SELECT INFO                                             +
               INTO :XMLBLOB                                           +
               FROM MYCUSTOMER                                         +
               WHERE CID = 1000
**********************************************************************
* RETRIEVE DATA FROM AN XML COLUMN INTO A CLOB HOST VARIABLE.        *
* BEFORE SENDING THE DATA TO THE APPLICATION, INVOKE THE             * 
* XMLSERIALIZE FUNCTION TO CONVERT THE DATA FROM THE XML             *
* TYPE TO THE CLOB TYPE.                                             *
**********************************************************************
         EXEC SQL                                                      +
               SELECT XMLSERIALIZE(INFO AS CLOB(10K))                  +
               INTO :CLOBBUF                                           +
               FROM MYCUSTOMER                                         +
               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 retrieves data from an XML column into XML AS BLOB, XML AS CLOB, and CLOB host variables. The data that is retrieved into an XML AS BLOB host variable is retrieved as binary data, so the database server generates an XML declaration with UTF-8 encoding. The data that is retrieved into an XML AS CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration that is consistent with the external encoding. The data that is retrieved into a CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration. That declaration might not be consistent with 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;
/**********************************************************************/
/* Retrieve data from an XML column into an XML AS CLOB host variable */
/**********************************************************************/
EXEC SQL SELECT INFO   INTO :xmlBuf from myTable where CID = 1000;
/**********************************************************************/
/* Retrieve data from an XML column into an XML AS BLOB host variable */
/**********************************************************************/
EXEC SQL SELECT INFO   INTO :xmlBlob from myTable where CID = 1000;
/**********************************************************************/
/* RETRIEVE DATA FROM AN XML COLUMN INTO A CLOB HOST VARIABLE.        */
/* BEFORE SENDING THE DATA TO THE APPLICATION, INVOKE THE             */
/* XMLSERIALIZE FUNCTION TO CONVERT THE DATA FROM THE XML             */
/* TYPE TO THE CLOB TYPE.                                             */
/**********************************************************************/
EXEC SQL SELECT XMLSERIALIZE(INFO AS CLOB(10K)) 
 INTO :clobBuf from myTable where CID = 1000;

Example: The following example shows a COBOL program that retrieves data from an XML column into XML AS BLOB, XML AS CLOB, and CLOB host variables. The data that is retrieved into an XML AS BLOB host variable is retrieved as binary data, so the database server generates an XML declaration with UTF-8 encoding. The data that is retrieved into an XML AS CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration that is consistent with the external encoding. The data that is retrieved into a CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration. That declaration might not be consistent with 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). 
**********************************************************************
* Retrieve data from an XML column into an XML AS CLOB host variable *
**********************************************************************
 EXEC SQL SELECT INFO
   INTO :XMLBUF
   FROM MYTABLE 
   WHERE CID = 1000
 END-EXEC.
**********************************************************************
* Retrieve data from an XML column into an XML AS BLOB host variable *
**********************************************************************
 EXEC SQL SELECT INFO
   INTO :XMLBLOB 
   FROM MYTABLE 
   WHERE CID = 1000
 END-EXEC.
**********************************************************************
* RETRIEVE DATA FROM AN XML COLUMN INTO A CLOB HOST VARIABLE.        *
* BEFORE SENDING THE DATA TO THE APPLICATION, INVOKE THE             *
* XMLSERIALIZE FUNCTION TO CONVERT THE DATA FROM THE XML             *
* TYPE TO THE CLOB TYPE.                                             *
**********************************************************************
 EXEC SQL SELECT XMLSERIALIZE(INFO AS CLOB(10K)) 
  INTO :CLOBBUF 
  FROM MYTABLE 
  WHERE CID = 1000
 END-EXEC.

Example: The following example shows a PL/I program that retrieves data from an XML column into XML AS BLOB, XML AS CLOB, and CLOB host variables. The data that is retrieved into an XML AS BLOB host variable is retrieved as binary data, so the database server generates an XML declaration with UTF-8 encoding. The data that is retrieved into an XML AS CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration that is consistent with the external encoding. The data that is retrieved into a CLOB host variable is retrieved as character data, so the database server generates an XML declaration with an internal encoding declaration. That declaration might not be consistent with 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);
/**********************************************************************/
/* Retrieve data from an XML column into an XML AS CLOB host variable */
/**********************************************************************/
 EXEC SQL SELECT INFO   INTO :XMLBUF FROM MYTABLE WHERE CID = 1000;
/**********************************************************************/
/* Retrieve data from an XML column into an XML AS BLOB host variable */
/**********************************************************************/
 EXEC SQL SELECT INFO   INTO :XMLBLOB FROM MYTABLE WHERE CID = 1000;
/**********************************************************************/
/* RETRIEVE DATA FROM AN XML COLUMN INTO A CLOB HOST VARIABLE.        */
/* BEFORE SENDING THE DATA TO THE APPLICATION, INVOKE THE             */
/* XMLSERIALIZE FUNCTION TO CONVERT THE DATA FROM THE XML             */
/* TYPE TO THE CLOB TYPE.                                             */
/**********************************************************************/
 EXEC SQL SELECT XMLSERIALIZE(INFO AS CLOB(10K)) 
  INTO :CLOBBUF FROM MYTABLE WHERE CID = 1000;