Before you begin working with the XML Extender to store your documents, you need to understand the structure of the XML document so that you can determine how to search the document. When planning how to search the document, you need to determine:
The following sections will describe how to make these decisions.
The XML document structure for this lesson takes information for a specific order that is structured by the order key as the top level, then customer, part, and shipping information on the next level. The XML document is described in Figure 4.
This lesson also provides a sample DTD for you to use in understanding and validating the XML document structure. You can see the DTD file in Appendix B, Samples. It matches the structure in Figure 4.
Figure 4. The hierarchical structure of the DTD and XML document
![]() |
The XML Extender provides XML user data types in which you define a column to hold XML documents. These data types are:
In this lesson, you will store a small document in DB2 and will, therefore, use the XMLVarchar data type.
When you understand the XML document structure and the needs of the
application, you can determine which elements and attributes to be searched,
the elements and attributes that will be searched or extracted most
frequently, or will be the most expensive to query. The service
department has indicated they will be frequently querying the order key,
customer name, price, and shipping date of an order, and need quick
performance for these searches. This information is contained in
elements and attributes of the XML document structure. Table 2 describes the location paths of each element and
attribute.
Table 2. Elements and attributes to be searched
Data | Location path |
---|---|
order key | /Order/@key |
customer | /Order/Customer/Name |
price | /Order/Part/ExtendedPrice |
shipping date | /Order/Part/Shipment/ShipDate |
In this tutorial, you will be creating a DAD file for the XML column, which is used to store the XML document in DB2. It also maps the XML element and attribute contents to DB2 side tables used for indexing, which improves search performance. In the last section, you saw which elements and attributes are to be searched. In this section, you learn more about mapping these element and attribute values to DB2 tables that can be indexed.
After identifying the elements and attributes to be searched, you determine how they should be organized in the side tables, how many tables and which columns are in what table. Typically, you organize the side tables by putting similar information in the same table. The structure is also determined by whether the location path of any elements can be repeated more than once in the document. For example in our document, the part element can be repeated multiple times, and therefore, the price and date elements can occur multiple times. Elements that can occur multiple times must be in their own tables.
Additionally, you also need to determine what DB2 base types the element or attribute values should use. Typically, this is easily determined by the format of the data. If the data is text, choose VARCHAR; if the data is an integer, choose INTEGER; or if the data is a date and you want to do range searches, choose DATE.
In this tutorial, the elements and attributes are mapped to the following side tables:
Column name | Data type | Location path | Multiple occuring? |
---|---|---|---|
ORDER_KEY | INTEGER | /Order/@key | No |
CUSTOMER | VARCHAR(16) | /Order/Customer/Name | No |
Column name | Data type | Location path | Multiple occuring? |
---|---|---|---|
PRICE | DECIMAL(10,2) | /Order/Part/ExtendedPrice | Yes |
Column name | Data type | Location path | Multiple occuring? |
---|---|---|---|
DATE | DATE | /Order/Part/Shipment/ShipDate | Yes |
For this tutorial, we provide a set of scripts for you to use to set up your environment. These scripts are in the DXX_INSTALL\samples\cmd directory (where DXX_INSTALL is the drive and directory where you installed the XML Extender, for example c:\dxx\samples\cmd), and they are as follows: