Tutorial: Working with XML data

pureXML® lets you define table columns that store a single, well-formed XML document in each row. This tutorial demonstrates how to set up a DB2® database system to store XML data and how to perform basic operations with XML data.

1. Create a table named MYCUSTOMER that contains an XML column:

   CREATE TABLE MYCUSTOMER (Cid BIGINT, INFO XML)#

2. Create an index over XML data. For the purposes of this tutorial, all XML documents that you store in the INFO column have a root element named customerinfo with an attribute named Cid. Create a unique index on the Cid attribute:

   CREATE UNIQUE INDEX MYCUT_CID_XMLIDX ON MYCUSTOMER(INFO) 
   GENERATE KEY USING XMLPATTERN
     'declare default element namespace "http://posample.org"; /customerinfo/@Cid'
   AS SQL DECFLOAT#

   The XML pattern that defines the index is case-sensitive. The element and attribute names in the XML pattern must match the element and attribute names in the XML documents exactly. In this example, customerinfo is the element and Cid is the attribute.

3. Insert three XML documents into the MYCUSTOMER table that you created in step 1 by issuing the following INSERT statements:

   INSERT INTO MYCUSTOMER (CID, INFO) VALUES (1000, 
   '<customerinfo xmlns="http://posample.org" Cid="1000"> 
     <name>Kathy Smith</name> 
     <addr country="Canada"> 
       <street>5 Rosewood</street> 
       <city>Toronto</city> 
       <prov-state>Ontario</prov-state> 
       <pcode-zip>M6W 1E6</pcode-zip> 
     </addr> 
     <phone type="work">416-555-1358</phone> 
   </customerinfo>')#
   
   INSERT INTO MYCUSTOMER (CID, INFO) VALUES (1002, 
   '<customerinfo xmlns="http://posample.org" Cid="1002"> 
     <name>Jim Noodle</name> 
     <addr country="Canada"> 
       <street>25 EastCreek</street> 
       <city>Markham</city> 
       <prov-state>Ontario</prov-state> 
       <pcode-zip>N9C 3T6</pcode-zip> 
     </addr> 
     <phone type="work">905-555-7258</phone> 
   </customerinfo>')#
   
   INSERT INTO MYCUSTOMER (CID, INFO) VALUES (1003, 
   '<customerinfo xmlns="http://posample.org" Cid="1003"> 
     <name>Robert Shoemaker</name> 
     <addr country="Canada"> 
       <street>1596 Baseline</street> 
       <city>Aurora</city> 
       <prov-state>Ontario</prov-state> 
       <pcode-zip>N8X 7F8</pcode-zip> 
     </addr> 
     <phone type="work">905-555-2937</phone> 
   </customerinfo>')#

   You can confirm that the records were successfully inserted by issuing the following query:

   SELECT CID, INFO FROM MYCUSTOMER#

4. Update the XML documents that are stored in an XML column. Issue the following UPDATE statement to add a cell phone number to the XML document for which the CID column value is 1002. To change individual items in an XML column, you must replace the entire column.

   UPDATE MYCUSTOMER SET INFO = 
   '<customerinfo xmlns="http://posample.org" Cid="1002"> 
     <name>Jim Noodle</name> 
     <addr country="Canada"> 
       <street>25 EastCreek</street> 
       <city>Markham</city> 
       <prov-state>Ontario</prov-state> 
       <pcode-zip>N9C 3T6</pcode-zip> 
     </addr> 
     <phone type="work">905-555-7258</phone> 
     <phone type="cell">905-554-7254</phone> 
    </customerinfo>'
   WHERE CID=1002#

   You can confirm that the XML document was updated by issuing the following query:

   SELECT CID, INFO FROM MYCUSTOMER
    WHERE CID=1002#

5. Delete any rows from the MYCUSTOMER table for which the customer document in the INFO column contains a cell phone number. Issue the following DELETE statement, which contains an XMLEXISTS predicate with an XQuery expression, to specify the documents that you want to delete.

   DELETE FROM MYCUSTOMER 
   WHERE XMLEXISTS (
    'declare default element namespace "http://posample.org";
    /customerinfo/phone[@type="cell"]' PASSING INFO)# 

   You can confirm that the XML document was deleted by issuing the following query:

   SELECT COUNT(*) FROM MYCUSTOMER
   WHERE CID = 1002#

   Confirm that the resulting value is 0.

6. Query XML data. You can retrieve an entire XML document, or you can retrieve a portion of an XML document.
   • Issue the following SELECT statement to retrieve the entire XML document that has a CID value of 1000:

     SELECT CID, INFO FROM MYCUSTOMER
      WHERE CID=1000#

   • Issue the following SELECT statement with the XMLQUERY function to retrieve the name element from each XML document in the MYCUSTOMER table.

     SELECT XMLQUERY (
       'declare default element namespace "http://posample.org";
        for $d in $doc/customerinfo
        return <out>{$d/name}</out>'
        passing INFO as "doc") 
       FROM MYCUSTOMER as c
       WHERE XMLEXISTS ('declare default element namespace "http://posample.org";
        $i/customerinfo/addr[city="Toronto"]' passing c.INFO as "i")#                                      

     
   The SELECT statement returns the following result:

   <out xmlns="http://posample.org"><name>Kathy Smith</name></out>

   
7. Update part of an XML document. Issue the following UPDATE statement, which includes the XMLMODIFY function, to change the address from 5 Rosewood to 42 Rosedale for the customer with customer ID 1000 in the MYCUSTOMER table.

   UPDATE MYCUSTOMER
    SET INFO = XMLMODIFY(
     'declare default element namespace "http://posample.org";
     replace value of node /customerinfo/addr/street
     with "42 Rosedale"')
    WHERE CID=1000#

   You can confirm that the XML document was updated by issuing the following query:

   SELECT CID, INFO FROM MYCUSTOMER
    WHERE CID = 1000#

   Related information:

   • Creation of tables with XML columns
   • Deletion of rows with XML documents from tables
   • XML data indexing
   • Insertion of rows with XML column values
   • Retrieving XML data
   • Updates of XML columns
   • DSNTEP2 and DSNTEP4
   • Executing SQL by using SPUFI