IBM Books

XML Extender Administration and Programming

Planning

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

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 hierarchical structure of the DTD and XML document

Determining the XML data type for the XML column

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.

Determining elements and attributes to be searched

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

Mapping the XML document to the side tables

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:

ORDER_SIDE_TAB

Column name Data type Location path Multiple occuring?
ORDER_KEY INTEGER /Order/@key No
CUSTOMER VARCHAR(16) /Order/Customer/Name No

PART_SIDE_TAB

Column name Data type Location path Multiple occuring?
PRICE DECIMAL(10,2) /Order/Part/ExtendedPrice Yes

SHIP_SIDE_TAB

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:

getstart_db.cmd
Creates the database and populates four tables.

getstart_prep.cmd
Binds the database with the XML Extender stored procedures and the DB2 CLI.

getstart_insertDTD.cmd
Inserts the DTD used to validate the XML document in the XML column.

getstart_createTabCol.cmd
Creates an application table that will have an XML-enabled column.

getstart_alterTabCol.cmd
Alters the application table by adding the column that will be enabled for XML.

getstart_enableCol.cmd
Enables the XML column.

getstart_createIndex.cmd
Creates indexes on the side tables for the XML column.

getstart_insertXML.cmd
Inserts the XML document into the XML column.

getstart_queryCol.cmd
Runs a select statement on the application table and returns the XML document.

getstart_clean.cmd
Cleans up the tutorial environment.


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