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 support on 7.1, you must install PTF SI42001 and its co-requisite PTFs; an IPL will be necessary to apply these fixes. This support is in the base operating system in the releases after 7.1.
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.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.