To store an XML document in a DB2 database, you must enable a column for XML. Enabling a column prepares it for indexing so that it can be searched quickly. You can enable a column by using the XML Extender administration wizard or using the DB2 command shell. The column must be of XML type.
When the XML Extender enables an XML column, it:
After you enable the XML column, you can
Create an XML table by creating or altering a DB2 table with a column of XML UDT.
Use the following steps to enable XML columns:
SALES_TAB
ORDERThe column must exist and be of XML type.
c:\dxx\samples\dad\getstart.dad
The table space contains side tables that the XML Extender created. If you specify a table space, the side tables are created in the specified table space. If you do not specify a table space, the side tables are created in the default table space.
When specified, the default view is automatically created when the column is enabled and joins the XML table and all of the related side tables.
The XML Extender uses the value of ROOT ID as a unique identifier to associate all side tables with the application table. If not specified, the XML Extender adds the DXXROOT_ID column to the application table and generates an identifier.
To enable an XML column, enter the following command:
Syntax:
dxxadm enable_column |
---|
>>-dxxadm---enable_column---dbName---tbName---colName-----------> >----DAD_file----+-----------------+---+-------------------+----> '--t--tablespace--' '--v--default_view--' >-----+--------------+----------------------------------------->< '--r--root_id--' |
Parameters:
Restriction: If the application table has a column name of DXXROOT_ID, but this column does not contain the value for root_id, you must specify the root_id parameter; otherwise, an error occurs.
Example: The following example enables a column using the DB2 command shell. The DAD file and XML document can be found in Appendix B, Samples.
dxxadm enable_column SALES_DB sales_tab order getstart.dad -v sales_order_view -r invoice_num
In this example, the column ORDER is enabled in the table SALES_DB.SALES_TAB. The DAD file is getstart.dad, the default view is sales_order_view, and the ROOT ID is INVOICE_NUM.
Using this example, the SALES_TAB table has the following schema:
Column name | INVOICE_NUM | SALES_PERSON | ORDER |
---|---|---|---|
Data type | CHAR(6) | VARCHAR(20) | XMLVARCHAR |
The following side tables are created based on the DAD specification:
ORDER_SIDE_TAB:
Column name | ORDER_KEY | CUSTOMER | INVOICE_NUM |
---|---|---|---|
Data type | INTEGER | VARCHAR(50) | CHAR(6) |
Path expression | /Order/@key | /Order/Customer/Name | N/A |
PART_SIDE_TAB:
Column name | PART_KEY | PRICE | INVOICE_NUM |
---|---|---|---|
Data type | INTEGER | DOUBLE | CHAR(6) |
Path expression | /Order/Part/@key | /Order/Part/ExtendedPrice | N/A |
SHIP_SIDE_TAB:
Column name | DATE | INVOICE_NUM |
---|---|---|
Data type | DATE | CHAR(6) |
Path expression | /Order/Part/Shipment/ShipDate | N/A |
All the side tables have the column INVOICE_NUM of the same type, because the ROOT ID is specified by the primary key INVOICE_NUM in the application table. After the column is enabled, the value of the INVOICE_NUM is inserted into the side tables. Specifying the default_view parameter when enabling the XML column, ORDER, creates a default view, sales_order_view. The view joins the above tables using the following statement:
CREATE VIEW sales_order_view(invoice_num, sales_person, order, order_key, customer, part_key, price, date) AS SELECT sales_tab.invoice_num, sales_tab.sales_person, sales_tab.order, order_tab.order_key, order_tab.customer, part_tab.part_key, part_tab.price, ship_tab.date FROM sales_tab, order_tab, part_tab, ship_tab WHERE sales_tab.invoice_num = order_tab.invoice_num AND sales_tab.invoice_num = part_tab.invoice_num AND sales_tab.invoice_num = ship_tab.invoice_numIf the table space is specified in the enable_column command, the side tables are created in the specified table space. If the table space is not specified, the side tables are created in the default table space.