XML column updates in JDBC applications

In a JDBC application, you can update or insert data into XML columns of a table at a Db2 data server using XML textual data. You can update or insert data into XML columns of a table using binary XML data (data that is in the Extensible Dynamic Binary XML DB2® Client/Server Binary XML Format), if the data server supports binary XML data.

The following table lists the methods and corresponding input data types that you can use to put data in XML columns.

Table 1. Methods and data types for updating XML columns
Method Input data type
PreparedStatement.setAsciiStream InputStream
PreparedStatement.setBinaryStream InputStream
PreparedStatement.setBlob Blob
PreparedStatement.setBytes byte[]
PreparedStatement.setCharacterStream Reader
PreparedStatement.setClob Clob
PreparedStatement.setObject byte[], Blob, Clob, SQLXML, DB2Xml (deprecated), InputStream, Reader, String
PreparedStatement.setSQLXML1 SQLXML
PreparedStatement.setString String
Note:
  1. This method requires JDBC 4.0 or later.

The encoding of XML data can be derived from the data itself, which is known as internally encoded data, or from external sources, which is known as externally encoded data. XML data that is sent to the database server as binary data is treated as internally encoded data. XML data that is sent to the data source as character data is treated as externally encoded data.

External encoding for Java applications is always Unicode encoding.

Externally encoded data can have internal encoding. That is, the data might be sent to the data source as character data, but the data contains encoding information. The data source handles incompatibilities between internal and external encoding as follows:
  • If the data source is Db2 on Linux®, UNIX, and Windows systems, the database source generates an error if the external and internal encoding are incompatible, unless the external and internal encoding are Unicode. If the external and internal encoding are Unicode, the database source ignores the internal encoding.
  • If the database source is Db2 for z/OS®, the database source ignores the internal encoding.

Character data in XML columns is stored in UTF-8 encoding. The database source handles conversion of the data from its internal or external encoding to UTF-8.

Example: The following example demonstrates inserting data from an SQLXML object into an XML column. The data is String data, so the database source treats the data as externally encoded. 
  public void insertSQLXML()
  {
	  Connection con = DriverManager.getConnection(url);
    SQLXML info = con.createSQLXML();
                              // Create an SQLXML object
    PreparedStatement insertStmt = null;
    String infoData = 
      "<customerinfo xmlns=""http://posample.org"" " +
      "Cid=""1000"">…</customerinfo>";
    info.setString(infoData);
                              // Populate the SQLXML object
    int cid = 1000;
    try {
      sqls = "INSERT INTO CUSTOMER (CID, INFO) VALUES (?, ?)";
      insertStmt = con.prepareStatement(sqls);
      insertStmt.setInt(1, cid);
      insertStmt.setSQLXML(2, info);
                              // Assign the SQLXML object value 
																 // to an input parameter
      if (insertStmt.executeUpdate() != 1) {
        System.out.println("insertSQLXML: No record inserted.");
      }
    }
    catch (IOException ioe) {
     ioe.printStackTrace();
    }
    catch (SQLException sqle) {
      System.out.println("insertSQLXML: SQL Exception: " +
        sqle.getMessage());
      System.out.println("insertSQLXML: SQL State: " +
        sqle.getSQLState());
      System.out.println("insertSQLXML: SQL Error Code: " +
        sqle.getErrorCode());

    }
  }
Example: The following example demonstrates inserting data from a file into an XML column. The data is inserted as binary data, so the database server honors the internal encoding. 
  public void insertBinStream(Connection conn)
  {
    PreparedStatement insertStmt = null;
    String sqls = null;
    int cid = 0;
    Statement stmt=null;
    try {
      sqls = "INSERT INTO CUSTOMER (CID, INFO) VALUES (?, ?)";
      insertStmt = conn.prepareStatement(sqls);
      insertStmt.setInt(1, cid);
      File file = new File(fn);
      insertStmt.setBinaryStream(2, 
        new FileInputStream(file), (int)file.length());
      if (insertStmt.executeUpdate() != 1) {
        System.out.println("insertBinStream: No record inserted.");
      }
    }
    catch (IOException ioe) {
     ioe.printStackTrace();
    }
    catch (SQLException sqle) {
      System.out.println("insertBinStream: SQL Exception: " +
        sqle.getMessage());
      System.out.println("insertBinStream: SQL State: " +
        sqle.getSQLState());
      System.out.println("insertBinStream: SQL Error Code: " +
        sqle.getErrorCode());

    }
  }
Example: The following example demonstrates inserting binary XML data from a file into an XML column.
…
SQLXML info = conn.createSQLXML();
OutputStream os = info.setBinaryStream ();
FileInputStream fis = new FileInputStream("c7.xml");
int read;
while ((read = fis.read ()) != -1) {
  os.write (read);
}

PreparedStatement insertStmt = null;
String sqls = null;
int cid = 1015;
sqls = "INSERT INTO MyCustomer (Cid, Info) VALUES (?, ?)";
insertStmt = conn.prepareStatement(sqls);
insertStmt.setInt(1, cid);
insertStmt.setSQLXML(2, info);
insertStmt.executeUpdate();