IBM Books

XML Extender Administration and Programming

Creating the XML collection: preparing the DAD file

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:

  1. From the c:\dxx\samples\cmd directory, open a text editor and create a file called getstart_xcollection.dad.
  2. Create the DAD header, using the following text:
    <?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.

  3. Insert the <DAD></DAD> tags. All other tags are located inside these tags.
  4. Specify <validation> </validation> tags to indicate whether the XML Extender validates the XML document structure using the DTD you inserted into the DTD repository table.
    <validation>NO</validation>   
    
  5. Use the <Xcollection></Xcollection> tags to define the access and storage method as XML collection. The access and storage methods define that the XML data is stored in a collection of DB2 tables.
    <Xcollection>
    </Xcollection>
    
  6. Specify an SQL statement to specify the tables and columns used for the XML collection. This method is called SQL mapping and is one of two ways to map relational data to the XML document structure. (See Types of mapping schemes to learn more about mapping schemes.) Enter the following statement:
    <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.

  7. Add the following prolog information to be used in the composed XML document.
    <prolog>?xml version="1.0"?</prolog> 
    

    This exact text is required for all DAD files.

  8. Add the <doctype></doctype> tags to be used in the XML document you are composing. The <doctype> tag contains the path to the DTD stored on the client.
    <doctype>!DOCTYPE Order SYSTEM "c:\dxx\samples\dtd\getstart.dtd"</doctype> 
    
  9. Define the root element of the XML document using the <root_node></root_node> tags. Inside the root_node, you specify the elements and attributes that make up the XML document.
  10. Map the XML document structure to the DB2 relational table structure using the following three types of nodes:

    element_node
    Specifies the element in the XML document. Element_nodes can have child element_nodes.

    attribute_node
    Specifies the attribute of an element in the XML document.

    text_node
    Specifies the text content of the element and the column data in a relational table for bottom-level element_nodes.

    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.

    1. Define an <element_node> tag for each element in the XML document.
      <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.

    2. Define an <attribute_node> tag for each attribute in your XML document. These attributes are nested in their element_node. The added attribute_nodes are highlighted in bold:
      <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> 
      
    3. For each bottom-level element_node, define <text_node> tags, indicating that the XML element contains character data to be extracted from DB2 when composing the document.
      <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> 
      
    4. For each bottom-level element_node, define a <column> tag. These tags specify from which column to extract data when composing the XML document and are typically inside the <attribute_node> or the <text_node> tags. Remember, the columns defined here must be in the <SQL_stmt> SELECT clause.
      <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> 
      
  11. Ensure that you have an ending </root_node> tag after the last </element_node> tag.
  12. Ensure that you have an ending </Xcollection> tag after the </root_node> tag.
  13. Ensure that you have an ending </DAD> tag after the </Xcollection> tag.
  14. Save the file as getstart_xcollection.dad

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.


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