The XML Extender provides a method of storing and accessing whole XML documents in the database, called XML column. Using the XML column method, you can store the document using the XMLFILE type, index the column in side tables, and then query or search the XML document. This storage method is particularly useful for archival applications in which documents are not frequently updated. For the purposes of this tutorial, you will store the provided XML document in the XML column.
In this lesson, you will store the document in the SALES_TAB table. To store the document, you will:
You can use a DTD to validate XML data in an XML column. The XML Extender creates a table in the XML-enabled database, called DTD_REF. The table is known as the DTD reference and is available for you to store DTDs. When you decide to validate XML documents, you must store the DTD in this repository. The DTD for this tutorial is c:\dxx\samples\dtd\getstart.dtd.
To insert the DTD:
From the DB2 Command Window, enter the following SQL INSERT command, all on the same line:
DB2 CONNECT TO SALES_DB DB2 INSERT into db2xml.dtd_ref values('c:\dxx\samples\dtd\getstart.dtd', db2xml.XMLClobFromFile('c:\dxx\samples\dtd\getstart.dtd'), 0, 'user1', 'user1', 'user1')
You can also run the following command file to insert the DTD:
getstart_insertDTD.cmd
The DAD file for the XML column has a simple structure. You specify that the storage mode is XML column, and you define the tables and columns for indexing.
In the following steps, elements in the DAD are referred to as tags and the elements of your XML document structure are referred to as elements. A sample of a DAD file similar to the one you will create is in c:\dxx\samples\dad\getstart_xcolumn.dad. It has some minor differences from the file generated in the following steps. If you use it for the lesson, note that the file paths might be different than those for your environment, the <validation> value is set to NO, rather than YES.
To prepare the DAD file:
Note that all the tags used in the DAD file are case sensitive.
<?xml version="1.0"?> <!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">The DAD file is an XML document and requires XML declarations.
<dtdid>c:\dxx\samples\dtd\getstart.dtd</dtdid>
Verify that this string matches the value used as the first parameter value when inserting the DTD in the DTD reference table in Storing the DTD in the DTD repository. For example, the path you used for the DTDID might be different that the above string if you are working on a different machine drive.
<validation>YES</validation>The value of <validation> must be in uppercase.
<Xcolumn> </Xcolumn>
<Xcolumn> <table name="order_side_tab"> </table> <table name="part_side_tab"> </table> <table name="ship_side_tab"> </table> </Xcolumn>
<Xcolumn> <table name="order_side_tab"> <column name="order_key" type="integer" path="/Order/@key" multi_occurrence="NO"/> <column name="customer" type="varchar(50)" path="/Order/Customer/Name" multi_occurrence="NO"/> </table> <table name="part_side_tab"> <column name="price" type="decimal(10,2)" path="/Order/Part/ExtendedPrice" multi_occurrence="YES"/> </table> <table name="ship_side_tab"> <column name="date" type="DATE" path="/Order/Part/Shipment/ShipDate" multi_occurrence="YES"/> </table> </Xcolumn>
You can compare the file you have just created with the sample file, c:\dxx\samples\dad\getstart_xcolumn.dad. This file is a working copy of the DAD file required to enable the XML column and create the side tables. The sample file contains path statements that might need to be changed to match your environment in order to be run successfully.
In this section you create the SALES_TAB table. Initially, it has two columns with the sale information for the order.
To create the table:
From the DB2 Command Window, enter the following CREATE TABLE statement:
DB2 CONNECT TO SALES_DB DB2 CREATE TABLE SALES_TAB(INVOICE_NUM CHAR(6) NOT NULL PRIMARY KEY, SALES_PERSON VARCHAR(20))
Alternatively, you can run the following command file to create the table:
getstart_createTabCol.cmd
Now, add a new column into the SALES_TAB table. This column will contain the intact XML document that you generated earlier and must be of XML UDT. The XML Extender provides multiple data types, described in XML Extender user-defined types. In this tutorial, you will store the document as XMLVARCHAR.
To add the column of XML type:
From the DB2 Command Window, enter the following SQL statement:
DB2 ALTER TABLE SALES_TAB ADD ORDER DB2XML.XMLVARCHAR
Alternatively, you can run the following command file to alter the table:
getstart_alterTabCol.cmd
After you create the column of XML type, you enable it for the XML Extender. When you enable the column, the XML Extender reads the DAD file and creates the side tables. Before enabling the column, you must:
To enable the column for XML:
From the DB2 Command Window, enter the following command:
dxxadm enable_column SALES_DB SALES_TAB ORDER GETSTART_XCOLUMN.DAD -v SALES_ORDER_VIEW -r INVOICE_NUM
Alternatively, you can run the following command file to enable the column for XML:
getstart_enableCol.cmd
The XML Extender creates the side tables with the INVOICE_NUM column and creates the default view.
Important: Do not modify the side tables in any way. You should only update the XML document using the UDFs supplied by the XML Extender. The XML Extender will automatically update the side tables when you update the XML document in the XML column.
When you enabled the XML column, you created a view of the XML column and side tables. You can use this view when working with the XML column.
To view the XML column and side table columns:
From the DB2 Command Window, enter the following SQL SELECT statement:
DB2 SELECT * FROM SALES_ORDER_VIEW
The view shows the columns in the side tables, as specified in the getstart_xcolumn.dad file.
Creating indexes on side tables allows you to do fast structural searches
of the XML document. In this step, you create indexes on key columns in
the side tables that were created when you enabled the XML column,
ORDER. The service department has specified which columns their
employees are likely to query most often. Table 3 describes these columns, which you will index:
Table 3. Side-table columns to be indexed
Column | Side table |
---|---|
ORDER_KEY | ORDER_SIDE_TAB |
CUSTOMER | ORDER_SIDE_TAB |
PRICE | PART_SIDE_TAB |
DATE | SHIP_SIDE_TAB |
To index the side tables:
Enter the following SQL commands from the DB2 Command Window:
DB2 CREATE INDEX KEY_IDX ON ORDER_SIDE_TAB(ORDER_KEY) DB2 CREATE INDEX CUSTOMER_IDX ON ORDER_SIDE_TAB(CUSTOMER) DB2 CREATE INDEX PRICE_IDX ON PART_SIDE_TAB(PRICE) DB2 CREATE INDEX DATE_IDX ON SHIP_SIDE_TAB(DATE)
Alternatively, you can run the following command file to create the indexes:
getstart_createIndex.cmd
Now that you have enabled a column that can contain the XML document and indexed the side tables, you can store the document using the functions that the XML Extender provides. When storing data into an XML column, you either use default casting functions or the XML Extender UDFs. Because you will be storing an object of the base type VARCHAR into a column of the XML UDT XMLVARCHAR, you will use the default casting function. See Storing data for more information about the storage default casting functions and the XML Extender-provided UDFs.
To store the XML document:
Important: Open the XML document c:\dxx\samples\xml\getstart.xml. Ensure that the file path in the DOCTYPE matches the DTD ID specified in the DAD and when inserting the DTD in the DTD repository. You can verify they match by querying the db2xml.DTD_REF table and by checking the DTDID element in the DAD file. If you are using a different drive and directory structure than the default, you might need to change the path in the DOCTYPE declaration.
From the DB2 Command Window, enter the following SQL INSERT command:
DB2 INSERT INTO SALES_TAB (INVOICE_NUM, SALES_PERSON, ORDER) VALUES('123456', 'Sriram Srinivasan', db2xml.XMLVarcharFromFile('c:\dxx\samples\cmd\getstart.xml'))
When you store the XML document, the XML Extender automatically updates the side tables.
Alternatively, you can run the following command file to store the document:
getstart_insertXML.cmd
To verify that the tables have been updated, run the following SELECT statements for the tables from the DB2 Command Window:
DB2 SELECT * FROM SALES_TAB DB2 SELECT * FROM PART_SIDE_TAB DB2 SELECT * FROM ORDER_SIDE_TAB DB2 SELECT * FROM SHIP_SIDE_TAB
You can search the XML document with a direct query against the side tables. In this step, you will search for all orders that have a price over 2500.00.
To query the side tables:
Enter the following SELECT statement from the DB2 Command Window:
DB2 "SELECT DISTINCT SALES_PERSON FROM SALES_TAB S, PART_SIDE_TAB P WHERE PRICE > 2500.00 AND S.INVOICE_NUM=P.INVOICE_NUM"
The result set should show the names of the sales people who sold an item that had a price greater than 2500.00.
Alternatively, you can run the following command file to search the document:
getstart_queryCol.cmd
You have completed the getting started tutorial for storing XML documents in DB2 tables. Many of the examples in the book are based on these lessons.