XML data retrieval in CLI applications

You can retrieve data from XML columns with use of the SQLBindCol() function in your CLI application. The SQLBindCol() function is called to bind XML columns in a result-set to variables, arrays, or LOB locators after the SQLPrepare(), SQLExecDirect() or one of the schema function calls. The data is retrieved when the SQLFetch() or SQLFetchScroll() function is called by a CLI application.

When you use the SQLBindCol() function to bind XML columns in a query result-set to application variables, you can specify the data type of the application variables as SQL_C_BINARY, SQL_C_CHAR, SQL_C_DBCHAR or SQL_C_WCHAR. When you select data from XML columns in a table by using the SQLBindCol() function, the output is returned as the serialized data.

When you are retrieving a result-set from an XML column, you can bind your application variable to the SQL_C_BINARY type to avoid possible data corruption. Binding to character types can result in possible data corruption as result of a code page conversion. Data corruption can occur when characters in the source code page cannot be represented in the target code page.

XML data is returned to the application as internally encoded data. The CLI determines the encoding of the data as follows:
  • If the C type is SQL_C_BINARY, the CLI driver returns the data in the UTF-8 encoding scheme.
  • If the C type is SQL_C_CHAR or SQL_C_DBCHAR, the CLI driver returns the data in the application code page encoding scheme.
  • If the C type is SQL_C_WCHAR, the CLI driver returns the data in the UCS-2 encoding scheme.

An implicit serialization of data takes place on the database server before XML data is sent to the application. You can explicitly serialize the XML data to a specific data type by calling the XMLSERIALIZE() function. However, the explicitly serializing to character types with the XMLSERIALIZE() function 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