XML support - IBM DB2

The XML data type is supported for use in embedded SQL and CLI applications. We briefly discuss how to work with the data type within C/C++ applications.

Embedded SQL
Embedded applications can make use of XML, LOB, or LOB_FILE data types when working with XML data in a DB2 database. If you choose to use XML host variables, they will be implicitly parsed, whereas using host variables of character and binary data type might not be. For dynamic SQL, CLOB, and BLOB are also implicitly parsed. For static SQL, an explicit XMLPARSE() will be injected in the SQL statement, but only for CLOB and BLOB (not DBCLOB). To declare host variables to handle XML data, we do so in the DECLARE section as we normally would for host variables of other data types. For XML host variables, we can use declarations of the form:

In the above statement, <host var> denotes the host variable name, and <base type> denotes the data type of the XML host variable. The possible values are listed in Table .

Allowed XML host variable data types

Allowed XML host variable data types

Once the host variable declarations are done, we can write code to process SQL statements using XML data in the same way we write code to process other types of SQL statements.“Application development with DB2 pureXML” to obtain information about how to write SQL statements to interact with data of the DB2 XML data type.

Selecting XML data using embedded SQL
Example below shows a simple example of how to retrieve an XML column in embedded SQL.

Retrieving an XML column using embedded SQL

In Example , we first declare a host variable of character LOB type to hold the serialized string format of our XML data. The precompile form of the clob1 host variable shows that clob1 is a structure with a length and data field:

We then run a select statement to retrieve the DESCRIPTION field of the PRODUCT table in the SAMPLE database. In the database, the DESCRIPTION column is of type xml. We call the XMLSERIALIZE method to convert the XML data from the database hierarchical format to an application CLOB and store the result in clob1. Running the application produces the following output.

Output from running Example

Inserting XML data using embedded SQL
Example shows a simple example of how to insert XML data into the XML column (DESCRIPTION column in the PRODUCT table) using embedded SQL.

Insert data into XML column

In Example , we declare a host variable of XML as CLOB. The bind file resulting from the db2 prep utility results in the following db2bfd –v command output:

Inserting-XML-data-using-embedded-SQL

Updating XML data column using embedded SQL
Example below shows a simple example of how to update XML data in the XML column (DESCRIPTION column of the PRODUCT table) using embedded SQL.

Updating XML column in C

In Example , we declare a host variable of XML as CLOB. The bind file resulting from the db2 prep utility results in the following db2bfd –v command output:

Updating-XML-data-column-using-embedded-SQL

Call Level Interface (CLI)
CLI Applications can use the xml, binary, or character data types to work with XML data from a DB2 database. In CLI API calls, applications can use application C types of the following:

  • SQL_C_BINARY
  • SQL_C_CHAR
  • SQL_C_WCHAR
  • SQL_C_DBCHAR

This means that when applications work with XML data types, they can bind application variables to any of the types just mentioned. The default C type is SQL_C_BINARY and using this type can avoid code page conversion issues. The other C types assume the application code page encoding. That is, SQL_C_BINARY would output in UTF-8, SQL_C_WCHAR in platform-encoding UTF-16, and SQL_C_CHAR/SQL_C_DBCHAR in the application mixed and graphic code pages respectively.

On the database end, a column of type xml has a symbolic SQL type of SQL_XML. The application can use this SQL type when trying to store or retrieve XML data. Let us try a simple CLI example. Assuming we have initialized all the necessary data structures and are connected to the database, we now want to get back the DESCRIPTION column from the PRODUCT table. The column in the database is of type xml. We can do the following

Example Retrieving an XML column using CLI

In Example , we declare a buffer called DESCRIPTION to hold the returned value from the database. We call SQLExecDirect() to execute the SELECT statement, and call SQLBindCol() to bind the returned column value to the application storage buffer named description. We specify SQL_C_CHAR as the C data type because we are expecting the xml to be converted to a CLOB in our XMLSERIALIZE call. Running the program produces the output as shown in Example .

Example Output from running Example

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics