Because the data already exists in multiple tables, you will create an XML collection, which associates the tables with the XML document. To create an XML collection, you define the collection by preparing a DAD file.
In Planning you determined which columns are in the relational database where the data exists, and how the data from the tables will be structured into an XML document. In this section, you create the mapping scheme in the DAD file that specifies the relationship between the tables and the structure of the XML document.
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_xcollection.dad. It has some minor differences to the file generate in the following steps. If you use it for the lesson, note that the file paths might be different than in your environment.
To create the DAD file for composing an XML document:
<?xml version="1.0"?> <!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
The XML Extender assumes that you installed the product in c:\dxx. If this is not correct, change this value to the drive and directory that you specified during the installation of this product here and in the following steps.
<validation>NO</validation>
<Xcollection> </Xcollection>
<SQL_stmt> SELECT o.order_key, customer_name, customer_email, p.part_key, color, quantity, price, tax, ship_id, date, mode from order_tab o, part_tab p, table (select substr(char(timestamp(generate_unique())),16) as ship_id, date, mode, part_key from ship_tab) s WHERE o.order_key = 1 and p.price > 20000 and p.order_key = o.order_key and s.part_key = p.part_key ORDER BY order_key, part_key, ship_id </SQL_stmt>
This SQL statement uses the following guidelines when using SQL mapping. Refer to Figure 6 for the document structure.
See Mapping scheme requirements for requirements when writing an SQL statement.
<prolog>?xml version="1.0"?</prolog>
This exact text is required for all DAD files.
<doctype>!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\getstart.dtd"</doctype>
See The DAD file for more information about these nodes. Figure 6 shows the hierarchical structure of the XML document and the DB2 table columns, and indicates what kinds of nodes are used. The shaded boxes indicate the DB2 table column names from which the data will be extracted to compose the XML document.
The following steps have you add each type of node, one type at a time.
<root_node> <element_node name="Order"> <element_node name="Customer"> <element_node name="Name"> </element_node> <element_node name="Email"> </element_node> </element_node> <element_node name="Part"> <element_node name="key"> </element_node> <element_node name="Quantity"> </element_node> <element_node name="ExtendedPrice"> </element_node> <element_node name="Tax"> </element_node> <element_node name="Shipment" multi_occurrence="YES"> <element_node name="ShipDate"> </element_node> <element_node name="ShipMode"> </element_node> </element_node> <!-- end Shipment --> </element_node> <!-- end Part --> </element_node> <!-- end Order --> </root_node>
Note that the <Shipment> child element has an attribute of multi_occurence="YES". This attribute is used for elements without an attribute, that are repeated in the document. The <Part> element does not use the multi-occurence attribute because it has an attribute of color, which makes it unique.
<root_node> <element_node name="Order"> <attribute_node name="key"> </attribute_node> <element_node name="Customer"> <element_node name="Name"> </element_node> <element_node names"Email"> </element_node> </element_node> <element_node name="Part"> <attribute_node name="color"> </attribute_node> <element_node name="key"> </element_node> <element_node name="Quantity"> </element_node> ... </element_node> <!-- end Part --> </element_node> <!-- end Order --> </root_node>
<root_node> <element_node name="Order"> <attribute_node name="key"> </attribute_node> <element_node name="Customer"> <element_node name="Name"> <text_node> </text_node> </element_node> <element_node name="Email"> <text_node> </text_node> </element_node> </element_node> <element_node name="Part"> <attribute_node name="color"> </attribute_node> <element_node name="key"> <text_node> </text_node> </element_node> <element_node name="Quantity"> <text_node> </text_node> </element_node> <element_node name="ExtendedPrice"> <text_node> </text_node> </element_node> <element_node name="Tax"> <text_node> </text_node> </element_node> <element_node name="Shipment" multi-occurence="YES"> <element_node name="ShipDate"> <text_node> </text_node> </element_node> <element_node name="ShipMode"> <text_node> </text_node> </element_node> </element_node> <!-- end Shipment --> </element_node> <!-- end Part --> </element_node> <!-- end Order --> </root_node>
<root_node> <element_node name="Order"> <attribute_node name="key"> <column name="order_key"/> </attribute_node> <element_node name="Customer"> <element_node name="Name"> <text_node> <column name="customer_name"/> </text_node> </element_node> <element_node name="Email"> <text_node> <column name="customer_email"/> </text_node> </element_node> </element_node> <element_node name="Part"> <attribute_node name="color"> <column name="color"/> </attribute_node> <element_node name="key"> <text_node> <column name="part_key"/> </text_node> <element_node name="Quantity"> <text_node> <column name="quantity"/> </text_node> </element_node> <element_node name="ExtendedPrice"> <text_node> <column name="price"/> </text_node> </element_node> <element_node name="Tax"> <text_node> <column name="tax"/> </text_node> </element_node> <element_node name="Shipment" multi-occurence="YES"> <element_node name="ShipDate"> <text_node> <column name="date"/> </text_node> </element_node> <element_node name="ShipMode"> <text_node> <column name="mode"/> </text_node> </element_node> </element_node> <!-- end Shipment --> </element_node> <!-- end Part --> </element_node> <!-- end Order --> </root_node>
You can compare the file you have just created with the sample file c:\dxx\samples\dad\getstart_xcollection.dad. This file is a working copy of the DAD file required to compose the XML document. The sample file contains path statements that might need to be changed to match your environment in order to be run successfully.
In your application, if you will use an XML collection frequently to compose documents, you can define a collection name by enabling the collection. Enabling the collection registers it in the XML_USAGE table and can improve performance when you specify the collection name (rather than the DAD file name) when running store procedures. In these lessons, you will not enable the collection. To learn more about enabling collections, see Enabling XML collections.