IBM Books

XML Extender Administration and Programming

Creating the XML column

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:

  1. Insert the DTD for the XML document into the DTD reference table, DTD_REF.
  2. Prepare a DAD file that specifies the XML document location and side tables for structural search.
  3. Add a column in the SALES_TAB table with an XML user-defined type of XMLVARCHAR.
  4. Enable the column for XML.
  5. Index the side tables for structural search.
  6. Store the document using a user-defined function, which is provided by the XML Extender.

Storing the DTD in the DTD repository

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

Preparing the DAD file

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:

  1. Open a text editor and name the file getstart_xcolumn.dad

    Note that all the tags used in the DAD file are case sensitive.

  2. Create the DAD header, with the XML and the Doctype declarations.
    <?xml version="1.0"?> 
    <!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd"> 
    
    The DAD file is an XML document and requires XML declarations.
  3. Insert opening and closing <DAD></DAD> tags. All other tags are located inside these tags.
  4. Insert opening and closing <DTDID></DTDID> tags to specify the DTD ID identifier that associates the DAD with the XML document DTD and specifies the DTD location at the client.
    <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.

  5. Specify opening and closing <validation></validation> tags to indicate that the XML Extender is to validate the XML document structure using the DTD you inserted into the DTD repository table.
    <validation>YES</validation>
    
    The value of <validation> must be in uppercase.
  6. Insert opening and closing <Xcolumn></Xcolumn> tags to define the storage method as XML column. The method defines that the XML data is to be stored in an XML column.
    <Xcolumn>
    </Xcolumn>
    
  7. Insert opening and closing <table></table> tags for each side table that is to be generated.
    <Xcolumn>
    <table name="order_side_tab">
    </table>
    <table name="part_side_tab">
    </table>
    <table name="ship_side_tab">
    </table>
    </Xcolumn>
    
  8. Insert opening and closing <column></column> tags for each column that is to be included in the side tables. Each <column> tag has four attributes:
    <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>
    
  9. Ensure that you have a closing </Xcolumn> after the last </table> tag.
  10. Ensure that you have a closing </DAD> after the </Xcolumn> tag.
  11. Save the file as getstart_xcolumn.dad.

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.

Creating the SALES_TAB table

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

Adding the column of XML type

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

Enabling the XML column

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.

Viewing the column and side tables

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 the side tables

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

Storing the XML document

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
 

Searching the XML document

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.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]