Lesson 4: Updating XML documents stored in XML columns

Updating without an XQuery updating expression

If you use the UPDATE statement without an XQuery updating expression, you must perform a full-document update.

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"]. The reason is that you used the DOUBLE data type when you created the index for the Cid attribute in Exercise 2. For the index to match this query, you cannot specify Cid as a string in the predicate expression.

SELECT * from Customer~

If you can identify XML documents in a table by using values in the non-XML columns of the same table, you can use SQL comparison predicates to identify which rows to update. In the previous example, where the Cid value from the XML document is also stored in the CID column of the CUSTOMER table, you could have used an SQL comparison predicate on the CID column to identify the row. In the previous example, you can replace the WHERE clause with the following clause:

WHERE Cid=1002

Updating with an XQuery updating expression

If you use the UPDATE statement with an XQuery updating expression, you can update portions of an existing XML document.

To update the customer address in an existing XML document, issue the following SQL statement, which uses an XQuery transform expression:

UPDATE Customer set Info = 
  XMLQUERY( 'declare default element namespace "http://posample.org";
  transform
  copy $mycust := $cust
  modify
    do replace $mycust/customerinfo/addr with
      <addr country="Canada"> 
        <street>25 EastCreek</street>
        <city>Markham</city> 
        <prov-state>Ontario</prov-state>
        <pcode-zip>N9C 3T6</pcode-zip>
     </addr>
  return $mycust'
  passing INFO as "cust")
WHERE CID = 1002~

To update the customer address, the XMLQUERY function executes an XQuery transform expression that uses a replace expression and then returns the updated information to the UPDATE statement, as follows:

• The XMLQUERY passing clause uses the identifier cust to pass the customer information to the XQuery expression from the XML column INFO.
• In the copy clause of the transform expression, a logical snapshot of the customer information is taken and assigned to the $mycust variable.
• In the modify clause of the transform expression, the replace expression replaces the address information in the copy of the customer information.
• XMLQUERY returns the updated customer document in the $mycust variable.

To confirm that the XML document contains the updated customer address, issue the following statement:

SELECT Info FROM Customer WHERE Cid = 1002~

Return to the tutorial