Updates of entire XML documents

To update an entire XML document in an XML column, use the SQL UPDATE statement. Include a WHERE clause when you want to update specific rows.

The input to the XML column must be a well-formed XML document, as defined in the XML 1.0 specification. A document node will be created implicitly if one does not already exist. The application data type can be XML (XML AS BLOB, XML AS CLOB, XML AS DBCLOB), character, or binary.

XML data in an application can be in textual XML format or extensible dynamic binary XML Db2 client/server binary XML format (binary XML format). Binary XML format is valid only for JDBC, SQLJ, and ODBC applications. When you update data in an XML column, it must be converted to its XML hierarchical format. The Db2 database server performs this operation implicitly when XML data from a host variable directly updates an XML column. Alternatively, you can invoke the XMLPARSE function explicitly when you perform the update operation, to convert the data to the XML hierarchical format.

When you update an XML column, you might also want to validate the input XML document against a registered XML schema. You can do that in one of the following ways:

Implicitly, if the XML column has an XML type modifier defined on it.
Explicitly, with the DSN_XMLVALIDATE function.

You can use XML column values to specify which rows are to be updated. To find values within XML documents, you need to use XQuery expressions. One way of specifying XQuery expressions is the XMLEXISTS predicate, which allows you to specify an XQuery expression and determine if the expression results in an empty sequence. When XMLEXISTS is specified in the WHERE clause, rows will be updated if the XQuery expression returns a non-empty sequence.

The following examples demonstrate how XML data can be updated in XML columns. The examples use table MYCUSTOMER, which is a copy of the sample CUSTOMER table. The examples assume that MYCUSTOMER already contains a row with a customer ID value of 1004. The XML data that updates existing column data is in file c7.xml, and looks like this:

<customerinfo xmlns="http://posample.org" Cid="1004">
<name>Christine Haas</name>
<addr country="Canada">
<street>12 Topgrove</street>
<city>Toronto</city>
<prov-state>Ontario</prov-state>
<pcode-zip>N9Y-8G9</pcode-zip>
</addr>
<phone type="work">905-555-5238</phone>
<phone type="home">416-555-2934</phone>
</customerinfo>

Example: In a JDBC application, read XML data from file c7.xml as binary data, and use it to update the data in an XML column:

PreparedStatement updateStmt = null;
String sqls = null;
int cid = 1004;
sqls = "UPDATE Customer SET Info=? WHERE Cid=?";
updateStmt = conn.prepareStatement(sqls);
updateStmt.setInt(2, cid);
File file = new File("c7.xml");
updateStmt.setBinaryStream(1, new FileInputStream(file), (int)file.length());
updateStmt.executeUpdate();

Example: In an embedded C application, update data in an XML column from an XML AS BLOB host variable:

EXEC SQL BEGIN DECLARE SECTION;
   sqlint64 cid;
   SQL TYPE IS XML AS BLOB (10K) xml_hostvar;
EXEC SQL END DECLARE SECTION;
…
cid=1004;
/* Read data from file c7.xml into xml_hostvar */
…
EXEC SQL UPDATE MYCUSTOMER SET xmlcol=:xml_hostvar WHERE Cid=:cid;

In these examples, the value of the Cid attribute within the <customerinfo> element happens to be stored in the CID relational column as well. Because of this, the WHERE clause in the UPDATE statements used the relational column CID to specify the rows to update. In the case where the values that determine which rows are chosen for update are found only within the XML documents themselves, the XMLEXISTS predicate can be used. For example, the UPDATE statement in the previous embedded C application example can be changed to use XMLEXISTS as follows:

EXEC SQL UPDATE MYCUSTOMER SET xmlcol=:xml_hostvar
       WHERE XMLEXISTS ('declare default element namespace "http://posample.org";
                         /customerinfo[@Cid = $c]' 
                         passing INFO, cast(:cid as integer) as "c");