DB2 10.5 for Linux, UNIX, and Windows
Updating XML data
To update data in
an XML column, use the SQL UPDATE statement. Include a WHERE clause
when you want to update specific rows.
The
entire column value will be replaced. The input to the XML column
must be a well-formed XML document. The application data type can
be an XML, character, or binary type.
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 with the 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 assumed to be stored in a file
c7.xml, whose contents look like this:
<customerinfo 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 MyCustomer SET Info=? WHERE Cid=?";
updateStmt = conn.prepareStatement(sqls);
updateStmt.setInt(1, cid);
File file = new File("c7.xml");
updateStmt.setBinaryStream(2, new FileInputStream(file), (int)file.length());
updateStmt.executeUpdate();
Example: In an embedded C application, update data in an
XML column from a binary XML 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 Info=: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 Info=:xml_hostvar
WHERE XMLEXISTS ('$doc/customerinfo[@Cid = $c]'
passing INFO as "doc", cast(:cid as integer) as "c");
Example: The following example updates existing XML data
from the MYCUSTOMER table. The SQL UPDATE statement operates on a
row of the MYCUSTOMER table and replaces the document in the INFO
column of the row with the logical snapshot of the document modified
by the transform expression:
UPDATE MyCustomer
SET info = XMLQUERY(
'transform
copy $newinfo := $info
modify do insert <status>Current</status>
as last into $newinfo/customerinfo
return $newinfo' passing info as "info")
WHERE cid = 1004