XML data retrieval in ODBC applications

When you select data from XML columns in a Db2 table, the output data is in textual format or Extensible Dynamic Binary XML Db2 Client/Server Binary XML Format (binary XML format).

For XML data, when you use SQLBindCol() to bind columns in a query result set to application variables, you can specify the data type of the application variables (fCType) as one of the following types:
  • SQL_C_BINARY
  • SQL_C_BINARYXML
  • SQL_C_CHAR
  • SQL_C_DBCHAR
  • SQL_C_WCHAR.

The data is returned as internally encoded data.

ODBC determines the encoding of the data as follows:
  • If the fCType value is SQL_C_BINARY, ODBC returns the data in the UTF-8 encoding scheme.
  • If the fCType value is SQL_C_BINARYXML, ODBC returns the data in binary XML format.
  • If the fCType value is SQL_C_CHAR or SQL_C_DBCHAR, ODBC returns the data in the application encoding scheme.
  • If the fCType value is SQL_C_WCHAR, ODBC returns the data in the UCS-2 encoding scheme.

Db2 performs an implicit XMLSERIALIZE on the data before returning it to your application.

For applications that use the SQL_C_BINARYXML data type, set LIMITEDBLOCKFETCH to 0. Otherwise, if you attempt to use the SQLGetData() function to retrieve XML data and have LIMITEDBLOCKFETCH set to 1, the function call fails.

Examples

Example of retrieving XML data from an XML column
The following example shows how to retrieve XML data from an XML column into application variables with various C data types. 
/* Variables for output XML data                   */
SQLCHAR     HVBINARY[32768];
SQLCHAR     HVCHAR[32768];
SQLDBCHAR   HVDBCHAR[32768];
SQLWCHAR    HVWCHAR[32768];
/* Variables for output XML data lengths           */
SQLINTEGER  LEN_HVBINARY;
SQLINTEGER  LEN_HVCHAR; 
SQLINTEGER  LEN_HVDBCHAR; 
SQLINTEGER  LEN_HVWCHAR;
/* SQL statement buffer                            */
SQLCHAR     sqlstmt[250];
/* Return code for ODBC calls                      */
SQLRETURN   rc = SQL_SUCCESS;
/* Prepare an SELECT statement for retrieving      */
/* data from XML columns.                          */
strcpy((char *)sqlstmt, 
  "SELECT XMLCOL1, XMLCOL2, XMLCOL3, XMLCOL4 ");
strcat((char *)sqlstmt,
  "FROM MYTABLE");
/* Bind data for first XML column as SQL_C_BINARY. */
/* This data will be retrieved as internally       */
/* encoded, in the UTF-8 encoding scheme.          */
rc = SQLBindCol(hstmt, 1, SQL_C_BINARY, HVBINARY, sizeof(HVBINARY), &LEN_HVBINARY);
/* Bind data for second XML column as              */
/* SQL_C_CHAR. This data will be retrieved as      */
/* internally encoded, in the application encoding */
/* scheme.                                         */
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, HVCHAR, sizeof(HVCHAR), &LEN_HVCHAR);
/* Bind data for third XML column as SQL_C_DBCHAR. */
/* This data will be retrieved as internally       */
/* encoded, in the application encoding scheme.    */
rc = SQLBindCol(hstmt, 3, SQL_C_DBCHAR, HVDBCHAR, sizeof(HVDBCHAR), &LEN_HVDBCHAR);
/* Bind data for fourth XML column as SQL_C_WCHAR. */
/* This data will be retrieved as internally       */
/* encoded, in the UCS-2 encoding scheme.          */
rc = SQLBindCol(hstmt, 4, SQL_C_WCHAR, HVWCHAR, sizeof(HVWCHAR), &LEN_HVWCHAR);
/* Execute the SELECT statement and fetch a row    */
/* from the result set                             */
rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS);
rc = SQLFetch(hstmt);
Example of retrieving binary XML data from an XML column
The following example shows how to retrieve binary XML data from an XML column into application variables by using type SQL_C_BINARYXML. 
CREATE TABLE MYTABLE ( XML_COL XML );

/* Declare variables for binary XML data */
SQLCHAR HV1BINARYXML[100];
SQLINTEGER LEN_HV1BINARYXML;
SQLCHAR sqlstmt[250];
SQLRETURN rc = SQL_SUCCESS;

/* Prepare select statement */
strcpy((char *)sqlstmt, "select * from mytable");
rc = SQLPrepare(hstmt, sqlstmt, SQL_NTS);

/* Bind column data as SQL_C_BINARYXML */
rc = SQLBindCol(hstmt, 1, SQL_C_BINARYXML, sizeof(HV1BINARYXML), &LEN_HV1BINARYXML);

/* Execute select */ 
rc = SQLExecute(hstmt); 
/* Fetch result set column as binary XML */ 
rc = SQLFetch(hstmt);