XML column updates in ODBC applications

When you update or insert data into XML columns of a Db2 table, the input data can be in textual format or Extensible Dynamic Binary XML Db2 Client/Server Binary XML Format (binary XML format)

For XML data, when you use SQLBindParameter() or SQLSetParam() to bind parameter markers to input data buffers, you can specify the data type of the input data buffer (fCType) as one of the following types:

SQL_C_BINARY
SQL_C_BINARYXML
SQL_C_CHAR
SQL_C_DBCHAR
SQL_C_WCHAR.

When you bind a data buffer that contains XML data as SQL_C_BINARY, ODBC processes the XML data as internally encoded data. This is the preferred method because it avoids the overhead and potential data loss of character conversion.

Important: If the XML data is encoded in an encoding scheme and CCSID other than the application encoding scheme, you need to include internal encoding in the data and bind the data as SQL_C_BINARY to avoid character conversion.

When you bind a data buffer that contains XML data as SQL_C_CHAR, SQL_C_DBCHAR or SQL_C_WCHAR, ODBC processes the XML data as externally encoded data. ODBC determines the encoding of the data as follows:

If the fCType value is SQL_C_WCHAR, ODBC assumes that the data is encoded as UCS-2.
If the fCType value is SQL_C_CHAR or SQL_C_DBCHAR, ODBC assumes that the data is encoded in the application encoding scheme.

SQL_C_BINARYXML is neither internally encoded nor externally encoded. SQL_C_BINARYXML is in binary XML format, as opposed to textual XML format, and it has no encoding.

If you want Db2 to do an implicit XMLPARSE on the data before storing it in an XML column, the parameter marker data type in SQLBindParameter() or SQLSetParam() (fsqlType) must be specified as SQL_XML.

If you do an explicit XMLPARSE on the data, the parameter marker data type in SQLBindParameter() or SQLSetParam() (fsqlType) can be specified as any character or binary data type.

Examples

Example of inserting XML data into an XML column

The following example shows how to insert XML data into an XML column by using various C and SQL data types.

/* Variables for input XML data                  */
SQLCHAR     HVCHAR[32768];
SQLWCHAR    HVWCHAR[32768];
/* Variables for input XML data lengths          */
SQLINTEGER  LEN_HVCHAR; 
SQLINTEGER  LEN_HVWCHAR;
/* SQL statement buffer                          */
SQLCHAR     sqlstmt[250];
/* Return code for ODBC calls                    */
SQLRETURN   rc = SQL_SUCCESS;
/* Prepare an INSERT statement for inserting     */
/* data into an XML column. The input parameter  */
/* type is SQL_XML, so DB2 does an implicit      */
/* XMLPARSE.                                     */
strcpy((char *)sqlstmt,
  "INSERT INTO MYTABLE(XMLCOL) VALUES(?)");
/* Bind input XML data with the SQL_C_CHAR type, */
/* to an SQL_XML SQL type.                       */
/* The data is assumed to be externally encoded, */
/* in the application encoding scheme.           */
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_XML, 
  0, 0, HVCHAR, sizeof(HVCHAR), &LEN_HVCHAR);
/* Execute the INSERT statement */
rc = SQLExecute(hstmt);
/* Bind input XML data with the SQL_C_WCHAR type, */
/* to an SQL_XML SQL type.                        */
/* The data is assumed to be externally encoded,  */
/* in the UCS-2 encoding scheme.                  */ 
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_XML, 
  0, 0, HVWCHAR, sizeof(HVWCHAR0, &LEN_HVWCHAR);
/* Execute the INSERT statement */
rc = SQLExecute(hstmt);
/* Prepare an INSERT statement for inserting     */
/* data into an XML column. The input parameter  */
/* type is SQL_CLOB, so the application must     */
/* do an explicit XMLPARSE.                      */
strcpy((char *)sqlstmt,
  "INSERT INTO MYTABLE (XMLCOL) ");
strcat((char *)sqlstmt,
  "VALUES(XMLPARSE(DOCUMENT CAST ? AS CLOB))");
/* Bind input XML data with the SQL_C_CHAR type,  */
/* to an SQL_CLOB SQL type.                       */
/* An explicit XMLPARSE is required for inserting */
/* character data into an XML column.             */
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CLOB, 
  32768, 0, HVCHAR, sizeof(HVCHAR), &LEN_HVCHAR);
/* Execute the INSERT statement */
rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS);

Example of inserting binary XML data into an XML column

The following example shows how to insert binary XML data into an XML column by using the SQL_C_BINARYXML data type.

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;

/* Assume that HV1BINARYXML contains XML data in binary format
   and LEN_HV1BINARYXML contains the length of data in bytes */

/* Prepare insert statement */
strcpy((char *)sqlstmt, "insert into mytable values(?)");
rc = SQLPrepare(hstmt, sqlstmt, SQL_NTS);

/* Bind XML_COL column as SQL_C_BINARYXML */
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARYXML, SQL_XML,
                0, 0, HV1BINARYXML, sizeof(HV1BINARYXML), &LEN_HV1BINARYXML);

/* Execute insert */ 
rc = SQLExecute(hstmt);