Updating XML documents stored in an XML column
To update an XML document stored in an XML column, you must perform a full-document update using the UPDATE SQL statement.
Update one of the documents as follows (where the values of the <street>, <city>, and <pcode-zip> elements have changed):
UPDATE customer SET info = '<customerinfo xmlns="http://posample.org" Cid="1002"> <name>Jim Noodle</name> <addr country="Canada"> <street>1150 Maple Drive</street> <city>Newtown</city> <prov-state>Ontario</prov-state> <pcode-zip>Z9Z 2P2</pcode-zip> </addr> <phone type="work">905-555-7258</phone> </customerinfo>' WHERE XMLEXISTS ( 'declare default element namespace "http://posample.org"; $doc/customerinfo[@Cid = 1002]' passing INFO as "doc")
The XMLEXISTS predicate ensures that only the document containing the attribute Cid="1002" is replaced. Notice how the predicate expression in XMLEXISTS, [@Cid = 1002], is not specified as a string comparison: [@Cid = "1002"]. This is because the index for the Cid attribute was defined with the DOUBLE data type. In order for the index to match this query, Cid cannot be specified as a string in the predicate expression.
You can confirm that the XML document was updated as follows:
SELECT * from Customer
The record where Cid="1002" should contain the changed <street>, <city>, and <pcode-zip> values.
If XML documents can be identified by values in the non-XML columns of the same table, then you can also use SQL comparison predicates to identify rows for update. In this example, where the Cid value from the XML document is also stored in the CID column of the CUSTOMER table, an SQL comparison predicate on the CID column could have been used to identify the row. For demonstration purposes, the XMLEXISTS predicate was used in this example.