Updating XML documents with information from other tables

You can use data from other database columns to update XML documents. If you have a table that contains updated customer information, for example, you can use SQL/XML statements and XQuery expressions to update the customer information in XML documents.

Use the following SQL statements to create a sample table that contains new customer phone numbers.

CREATE TABLE NewPhones (
   CID BIGINT NOT NULL PRIMARY KEY, PhoneNo VARCHAR(20), Type VARCHAR(10))~

INSERT INTO NewPhones (CID, PhoneNo, Type) VALUES (1001, '111-222-3333', 'cell' )~
INSERT INTO NewPhones (CID, PhoneNo, Type) VALUES (1002, '222-555-1111', 'home' )~
INSERT INTO NewPhones (CID, PhoneNo, Type) VALUES (1003, '333-444-2222', 'home' )~

Use the following SQL statement to update a customer phone number in the CUSTOMER table. The statement uses the XMLQUERY function and an XQuery expression consisting of a FLWOR expression and a transform expression that contains an insert expression.

UPDATE CUSTOMER SET INFO = XMLQUERY(
   'let $myphone := db2-fn:sqlquery(''SELECT XMLELEMENT(Name "phone", 
        XMLATTRIBUTES( NewPhones.Type as "type" ), NewPhones.PhoneNo )
        FROM NewPhones WHERE CID = parameter(1)'', $mycid )
   return
     transform
     copy $mycust := $d
     modify
	     do insert $myphone after $mycust/customerinfo/phone[last()]
     return
       $mycust'
  passing INFO as "d", 1002 as "mycid" )
WHERE CID = 1002~

The XMLQUERY function executes an XQuery expression that adds a phone element node to the customer information and returns the modified information to the UPDATE statement. In the let clause of the XQuery FLWOR expression, the db2-fn:sqlquery function executes an SQL fullselect statement. The fullselect uses the customer ID passed to the XQuery expression from XMLQUERY.

The fullselect statement must return an XML data type. To create an XML data type from the PHONENO and TYPE data returned from the SELECT statement, the XMLELEMENT and XMLATTRIBUTES functions create a phone element node based on the data supplied.

In the example, the fullselect executed by db2-fn:sqlquery in the let clause creates the following phone element node.

<phone type="home">222-555-1111<phone>

Run the following SQL select to see the customer information, now with both a work and a home phone number.

SELECT INFO FROM CUSTOMER WHERE CID = 1002~