Working with the XML data type

Start of change These conventions can help you handle various aspects of using the XML data type in DB2® for iODBC functions. End of change

XML data handling in ODBC applications

DB2 for i ODBC applications can retrieve and store XML data using the SQL_XML data type. This data type corresponds to the native XML data type of the DB2 for i database, which is used to define columns that store well-formed XML documents. The SQL_XML type can be bound to the following C types: SQL_C_BINARY, SQL_VARBINARY, SQL_C_CHAR, SQL_VARCHAR, SQL_C_WCHAR, andSQL_WVARCHAR. Using binary types, however, instead of character types, is recommended to avoid possible data loss or corruption resulting from CCSID conversion when character types are used. To store XML data in an XML column, bind a binary (SQL_C_BINARY or SQL_VARBINARY) or character (SQL_C_CHAR, SQL_VARCHAR, SQL_C_WCHAR, or SQL_VARWCHAR) buffer that contains the XML value to the SQL_XML SQL type and execute the INSERT or UPDATE SQL statements. To retrieve XML data from the database, bind the result set to a binary (SQL_C_BINARY or SQL_VARBINARY) or character (SQL_C_CHAR, SQL_VARCHAR, SQL_C_WCHAR, or SQL_WVARCHAR) type. Use character types with caution because of encoding issues. When an XML value is retrieved into an application data buffer, the DB2 for i server performs an implicit serialization on the XML value to convert it from its internal form to the serialized string form. For character typed buffers, the XML value is implicitly serialized to the application CCSID associated with the character type. By default, an XML declaration is included in the output serialized string. This default behavior can be changed by setting the SQL_ATTR_XML_DECLARATION connection attribute.

XML column inserts and updates in ODBC applications

When you update or insert data into XML columns of a table, the input data must be in the serialized string format. For XML data, when you use SQLBindParameter() to bind parameter markers to input data buffers, you can specify the data type of the input data buffer as SQL_C_BINARY, SQL_VARBINARY, SQL_C_CHAR, SQL_VARCHAR_, SQL_C_WCHAR, or SQL_VARCHAR. When you bind a data buffer that contains XML data as SQL_C_BINARY or SQL_VARBINARY, DB2 for i ODBC processes the XML data as internally encoded data. This method is preferred because it avoids the added processing and potential data loss of character conversion when character types are used. When you bind a data buffer that contains XML data as SQL_C_CHAR, SQL_VARCHAR, SQL_C_WCHAR, or SQL_WVARCHAR, DB2 for i ODBC processes the XML data as externally encoded data.

DB2 for i ODBC determines the encoding of the data as follows:

If the C type is SQL_C_WCHAR or SQL_WVARCHAR, ODBC assumes that the data is encoded as UCS-2.
If the C type is SQL_C_CHAR or SQL_C_VARCHAR, ODBC assumes that the data is encoded in the job CCSID.

The following example shows how to update XML data in an XML column using the recommended SQL_C_BINARY type.

char xmlBuffer[10240];
integer length;

// Assume a table named dept has been created with the following statement:
// CREATE TABLE dept (id CHAR(8), deptdoc XML)

// xmlBuffer contains an internally encoded XML document that is to replace
// the existing XML document
length = strlen (xmlBuffer);
SQLPrepare (hStmt, "UPDATE dept SET deptdoc = ? WHERE id = '001'", SQL_NTS);
SQLBindParameter (hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_XML, 0, 0,
                  xmlBuffer, 10240, &length); SQLExecute (hStmt);

XML data retrieval in ODBC applications

When you select data from XML columns in a table, the output data is in the serialized string format. For XML data, when you use SQLBindCol() API to bind columns in a query result set to application variables, you can specify the data type of the application variables as SQL_C_BINARY, SQL_VARBINARY, SQL_C_CHAR, SQL_VARCHAR, SQL_C_WCHAR, or SQL_WVARCHAR. When retrieving a result set from an XML column, it is recommended that you bind your application variable to the SQL_C_BINARY or SQL_VARBINARY type. Binding to character types can result in possible data loss resulting from CCSID conversion. Data loss can occur when characters in the source code page cannot be represented in the target code page. Binding your variable to the binary types avoids these issues. XML data is returned to the application as internally encoded data.

ODBC determines the encoding of the data as follows:

If the C type is SQL_C_BINARY or SQL_VARBINARY, DB2 for i ODBC returns the data in the encoding of the column.
If the C type is SQL_C_CHAR or SQL_VARCHAR, DB2 for i ODBC returns the data in job CCSID.
If the C type is SQL_C_WCHAR or SQL_WVARCHAR, DB2 for i ODBC returns the data in the UCS-2 encoding scheme.

The database server performs an implicit serialization of the data before returning it to the application. You can explicitly serialize the XML data to a specific data type by calling the XMLSERIALIZE function. Implicit serialization is recommended, however, because explicitly serializing to character types with XMLSERIALIZE can introduce encoding issues.

The following example shows how to retrieve XML data from an XML column into a binary application variable.

char xmlBuffer[10240];
// xmlBuffer is used to hold the retrieved XML document
integer length;

// Assume a table named dept has been created with the following statement:
// CREATE TABLE dept (id CHAR(8), deptdoc XML)

length = sizeof (xmlBuffer);
SQLExecute (hStmt, "SELECT deptdoc FROM dept WHERE id='001'", SQL_NTS);
SQLBindCol (hStmt, 1, SQL_C_BINARY, xmlBuffer, &length, NULL);
SQLFetch (hStmt); 
SQLCloseCursor (hStmt); 
// xmlBuffer now contains a valid XML document encoded in UTF-8