XML data retrieval in CLI applications

For XML data, when you use SQLBindCol() to bind columns in a query result set to application variables, you can specify the application C character and graphic data types, the character and graphic LOB data types, and the binary data types. When retrieving a result set from an XML column, you should consider binding your application variable to the binary types. Binding to character types can result in possible data loss resulting from the CCSID conversion. Data loss can occur when characters in the source XML CCSID cannot be represented in the target string CCSID. Binding your variable to the binary types avoids these issues.

XML data is returned to the application as internally encoded data. DB2® CLI determines the encoding of the data as follows:

• If the C type is SQL_C_BINARY, DB2 CLI returns the data in the XML value encoding scheme.
• If the C type is SQL_C_CHAR, DB2 CLI returns the data in the application character encoding scheme.
• If the C type is SQL_C_WCHAR, DB2 CLI returns the data in the UTF-16 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