XML data in SQLJ applications

In SQLJ applications, you can store data in XML columns and retrieve data from XML columns.

In Db2® tables, the XML built-in data type is used to store XML data in a column as a structured set of nodes in a tree format.

SQLJ applications can send XML data to the data server or retrieve XML data from the data server in one of the following forms:
  • As textual XML data
  • As binary XML data (data that is in the Extensible Dynamic Binary XML Db2 Client/Server Binary XML Format), if the data server supports it
In SQLJ applications, you can:
  • Store an entire XML document in an XML column using INSERT, UPDATE, or MERGE statements.
  • Retrieve an entire XML document from an XML column using single-row SELECT statements or iterators.
  • Retrieve a sequence from a document in an XML column by using the SQL XMLQUERY function to retrieve the sequence in the database, and then using single-row SELECT statements or iterators to retrieve the serialized XML string data into an application variable.
  • Retrieve a sequence from a document in an XML column by using an XQuery expression, prepended with the string 'XQUERY', to retrieve the elements of the sequence into a result table in the database, in which each row of the result table represents an item in the sequence. Then use using single-row SELECT statements or iterators to retrieve the data into application variables.
  • Retrieve a sequence from a document in an XML column as a user-defined table by using the SQL XMLTABLE function to define the result table and retrieve it. Then use using single-row SELECT statements or iterators to retrieve the data from the result table into application variables.
  • You can update or retrieve XML data as textual XML data. Alternatively, for connections to a data server that supports binary XML data, you can update or retrieve XML data as binary XML data.

    For data retrieval, you use the Datasource or Connection property xmlFormat to control whether the format of the retrieved data is textual XML or binary XML.

    For update of data in XML columns, xmlFormat has no effect. If the input data is binary XML data, and the data server does not support binary XML data, the input data is converted to textual XML data. Otherwise, no conversion occurs.

    The format of XML data is transparent to the application. Storage and retrieval of binary XML data on a Db2 for z/OS® data server requires version 4.9 or later of the IBM® Data Server Driver for JDBC and SQLJ. Storage and retrieval of binary XML data on a Db2 data server requires version 4.11 or later of the IBM Data Server Driver for JDBC and SQLJ.

JDBC 4.0 java.sql.SQLXML objects can be used to retrieve and update data in XML columns. Invocations of metadata methods, such as ResultSetMetaData.getColumnType return the integer value java.sql.Types.SQLXML for an XML column type.