XML column inserts and updates in CLI 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, you use SQLBindParameter() to bind parameter markers to input data buffers.

The SQL XML data type can be bound to the following application C character and graphic data types:

SQL_C_CHAR
SQL_VARCHAR
SQL_C_WCHAR
SQL_VARGRAPHIC

The following character LOB data types:

SQL_C_CLOB
SQL_C_CLOB_LOCATOR

and the following binary data types:

SQL_C_BINARY
SQL_C_BLOB
SQL_C_BLOB_LOCATOR
SQL_C_BINARY

When you bind a data buffer that contains XML data as a binary data type, DB2® CLI 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 when character types are used.

Note: The XML data should be bound to a binary data type when the XML is received from many sources with different encoding schemes.

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

If the C type is SQL_C_WCHAR, DB2 CLI assumes that the data is encoded as UTF-16.
If the C type is SQL_C_CHAR, DB2 CLI assumes that the data is encoded in the application's single-byte default CCSID.

If you want the database server to implicitly parse the data before storing it in an XML column, the parameter marker data type in SQLBindParameter() should be specified as SQL_XML.

The following example shows how to update XML data in an XML column using the 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 = &apos;001&apos;", SQL_NTS); 
SQLBindParameter (hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_XML, 0, 0, 
                  xmlBuffer, 10240, &length); 
SQLExecute (hStmt);