You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Bookmark and Share
RSS

Recent Posts

Control XML Decomposition Order of Target Tables

March 29, 2011

This week's blog was written by Yi Yuan who works for the China Systems and Technology Lab in Beijing.  She works mainly on the OmniFind Text Search Server for DB2 and XML related features for DB2 on i.

If you've started using the XML support provided in IBM i 7.1, you may already have tried the exciting feature called “annotated XML schema decomposition," which can be used to shred XML data into relational database columns. A recent enhancement of this feature brings more control on how you can decompose XML documents into database tables according to a specified order.

You’ll find this enhancement of rowset order support useful when your database design includes referential constraint between tables and you want to decompose XML document to such tables with one annotated XML schema. Data of the referenced table should be inserted before the insertion of referencing table during decomposition.

In this enhancement, two new annotations, db2-xdb:order and db2-xdb:rowSetOperationOrder, are provided to help define the order of insertion of target database tables.

Here is an example showing how to use the two new annotations in annotated XML decomposition schemas to define insertion order:   


     
       
         
           
              CUSTOMER
              PURCHASE_ORDER
           

           
              ITEMS_MASTER
              PO_ITEMS
           

         

       

     

   

Two independent hierarchies for order of insertion are specified in the above example as db2-xdb:order element. The first hierarchy specifies all content for the CUSTOMER rowSet or table is inserted prior to any content collected for PURCHASE_ORDER, and the second hierarchy specifies all content for the ITEMS_MASTER rowSet or table will be inserted before any content is inserted into PO_ITEMS. Note that the order between the two hierarchies is undefined. For example, any content for the PURCHASE_ORDER rowSet or table may be inserted before or after any content is inserted into ITEMS_MASTER.

With the basic knowledge of the new annotations, let’s see a complete example. Suppose a book store uses two relational tables to store book and book order information and the’re created using SQL as:

CREATE TABLE BOOK
( PUBS_ISBN VARCHAR(50),
AUTHOR VARCHAR(200),
PUBS_TITLE VARCHAR(500),
PRIMARY KEY(PUBS_ISBN));

CREATE TABLE ORDER
   (ID INT GENERATED ALWAYS,
PUBS_ISBN VARCHAR(50),
AMOUNT INT,
CUSTNAME VARCHAR(100),
PRIMARY KEY(ID),
FOREIGN KEY(PUBS_ISBN) REFERENCES BOOK (PUBS_ISBN));

And the book store receives book order information in the format of an XML document, for example one XML instance may look like:



    0-11-022222-0
    WuChengen  
 
 

    0-11-022222-0
    Lilei
    1


   0-11-022222-0
   Tom
   3


    0-11-022222-0
    Jerry
    2



To decompose the above XML document into table BOOK and ORDER, an annotated XML decomposition schema can be defined as:

          xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">
 
   
     
           
              BOOK
              ORDER
           
           
     

   

 

   
     
       
         
           
             
                                       db2-xdb:rowSet="BOOK" db2-xdb:column="PUBS_ISBN"/>
                maxOccurs="1" minOccurs="1"
                       db2-xdb:rowSet="BOOK" db2-xdb:column="AUTHOR"/>
             

                                    db2-xdb:rowSet="BOOK" db2-xdb:column="PUBS_TITLE"/>
           

         
   
         
           
             
                                       db2-xdb:rowSet="ORDER" db2-xdb:column="PUBS_ISBN"/>
                maxOccurs="1" minOccurs="1"
                       db2-xdb:rowSet="ORDER" db2-xdb:column="CUSTNAME"/>
                maxOccurs="1" minOccurs="1"
                          db2-xdb:rowSet="ORDER" db2-xdb:column="AMOUNT"/>
             
            
           

         

       

     

   



The two new annotations are used to specify that data of BOOK table are inserted before any data of ORDER is inserted. After you call XDBDECOMPXML procedure to decompose the XML instance, data in the two tables are:

BOOK:
PUBS_ISBN     AUTHOR     PUBS_TITLE
0-11-022222-0       WuChengen   Journey to the West   

ORDER:
ID  PUBS_ISBN   AMOUNT   CUSTNAME
1    0-11-022222-0       2         Jerry   
2    0-11-022222-0      3         Tom   
3    0-11-022222-0      1         Lilei   

If you don’t use the rowSet order annotations, the sequence of insertion can’t be guaranteed, and you may get failure if one row of ORDER table is inserted before its referenced BOOK row is inserted.

As you can see from the example, it’s very convenient to control shredding order using the two annotations. Follow a few rules to use them correctly:

  •  The annotation can appear only once in an XML schema.
  • One or more elements can be used inside the db2-xdb:rowSetOperationOrder element. 
  • Two or more elements can be specified inside each element. 
  • A rowSet can appear in only one instance of the element, and it can appear only once within that element.

To get this new support, you must install PTF SI42001 and its co-requisite PTFs; an IPL will be necessary to apply these fixes.

Posted March 29, 2011| Permalink

-->