Introduction

The eXtensible Markup Language (XML) can be used to exchange data between computers. The DB2® XML Extender™ allows you to integrate IBM® DB2® Universal Database™ (DB2 UDB) with XML. This document will introduce you to the SQL/XML functions available with DB2 UDB version 8 and the DB2 XML Extender. The DB2 XML Extender uses user-defined types (UDT), user-defined functions (UDF), and stored procedures.

XML samples are provided with DB2 UDB v8.1. The sample files are located in <DB2_install_dir>/SQLLIB/samples/db2xml. To set up and use the XML samples, please refer to the DB2 manual XML Extender Administration and Programming.

<?xml version="1.0"?>
<!DOCTYPE Order SYSTEM "dxx_install/samples/db2xml/dtd/getstart.dtd">
<Order key="1">
  <Customer>
    <Name>American Motors</Name>
    <Email>parts@am.com</Email>
  </Customer>
  <Part color="black ">
    <key>68</key>
    <Quantity>36</Quantity>
    <ExtendedPrice>34850.16</ExtendedPrice>
    <Tax>6.000000e-02</Tax>
    <Shipment>
      <ShipDate>1998-08-19</ShipDate>
      <ShipMode>BOAT  </ShipMode>
    </Shipment>
    <Shipment>
       <ShipDate>1998-08-19</ShipDate>
       <ShipMode>AIR   </ShipMode>
    </Shipment>
  </Part>
  <Part color="red   ">
    <key>128</key>
    <Quantity>28</Quantity>
    <ExtendedPrice>38000.00</ExtendedPrice>
    <Tax>7.000000e-0</Tax>
    <Shipment>
      <ShipDate>1998-12-30</ShipDate>
      <ShipMode>TRUCK </ShipMode>
    </Shipment>
  </Part>
</Order>

Back to top

DB2 and XML

DB2 UDB v8 includes new built-in SQL/XML publishing functions which make it easy to publish XML using data in a DB2 database. You do not need the XML Extender to use these functions.

In DB2 UDB v8, the XML Extender has been integrated into DB2, it does not have to be installed separately. The XML Extender provides the following:

• administration tools to help you manage the integration of XML data
• storage and access methods for XML data within the database
• a data type definition (DTD) repository to store DTDs used to validate XML data
• a mapping file called the Document Access Definition (DAD) used to map XML documents to relational data

For a detailed discussion of XML capabilities in DB2, please see the article Meet the Experts: Susan Malaika on XML Capabilities in DB2.

Back to top

SQL/XML

DB2 UDB v8 provides the following SQL/XML publishing functions which can be used for document composition and publishing. These functions work together to produce XML fragments. The XML Extender is not required.

XMLELEMENT

XMLELEMENT is used to construct an XML element. This function takes an element name, an optional collection of attributes, and zero or more arguments that will make up the element's content. The result data type is XML.

XMLATTRIBUTE

XMLATTRIBUTE is used to constructs XML attributes. The result has the same internal XML data type as the arguments.

XMLAGG

XMLAGG returns the concatenation of a set of XML data. The data type of the result is XML. If the XMLAGG function is applied to an empty set, the result is a null value. Otherwise, the result is the concatenation of the values in the set.

XML2CLOB

XML2CLOB returns the argument as a CLOB value. The argument must be an expression of data type XML. The result has the CLOB data type.

REC2XML

The REC2XML function returns a string formatted with XML tags and containing column names and column data.

Example

Query using the DB2 sample database.
Generate a list of employee names and numbers for departments A00 and C01:

      SELECT XML2CLOB(
    XMLELEMENT(NAME "Department",
        XMLATTRIBUTES(workdept AS "name"),
        XMLAGG(
            XMLELEMENT(NAME "employee",
                XMLATTRIBUTES(lastname, firstnme, empno))
            ORDER BY lastname)
    ))
    FROM employee
    WHERE workdept IN('A00', 'C01') 
    GROUP BY workdept

Result:

<Department name="A00">
  <employee LASTNAME="HAAS" FIRSTNME="CHRISTINE" EMPNO="000010"></employee>
  <employee LASTNAME="LUCCHESSI" FIRSTNME="VINCENZO" EMPNO="000110"></employee>
  <employee LASTNAME="O'CONNELL" FIRSTNME="SEAN" EMPNO="000120"></employee>
</Department>

<Department name="C01">
  <employee LASTNAME="KWAN" FIRSTNME="SALLY" EMPNO="000030"></employee>
  <employee LASTNAME="NICHOLLS" FIRSTNME="HEATHER" EMPNO="000140"></employee>
  <employee LASTNAME="QUINTANA" FIRSTNME="DOLORES" EMPNO="000130"></employee>
</Department>

Query using the DB2 sample database.
List some information for department D01:

SELECT REC2XML (1.0, 'COLATTVAL', '', DEPTNO, MGRNO, ADMRDEPT)
     FROM DEPARTMENT
     WHERE DEPTNO = 'D01'

Result:

<row>
  <column name="DEPTNO">D01</column>
  <column name="MGRNO" null="true"/>
  <column name="ADMRDEPT">A00</column>
</row>

For details and examples on XMLELEMENT, XMLATTRIBUTE, XMLAGG, and XML2CLOB, please see the section on XML functions in the DB2 manual SQL Reference Volume 1. For details and examples on REC2XML, please see the functions section in the DB2 manual SQL Reference Volume 1.

Back to top

The DB2 XML Extender

The DB2 XML Extender can be used to generate XML documents and to store XML documents in an XML repository. The schema named used by the DB2 XML Extender is DB2XML.

Administration Tools

The XML Extender administration tools help you enable your database and table columns for XML, and map XML data to DB2 relational databases. The following tools can be used to perform administration tasks for the XML Extender:

• The XML Extender administration wizards provide a graphical user interface for administration tasks.
• The dxxadm command allows you to perform administration tasks from the command-line.
• The XML Extender administration stored procedures allow you to invoke administration commands from a program.

XML Extender administration wizard
The XML Extender administration wizard calls XML Extender stored procedures. The stored procedures are listed in Table 3

Starting the XML Extender administration wizard
Note - the sample screen shots are from the database sales_db provided with the XML samples in DB2 v8.1.

1. Set the classpath. Make sure dxxadmin.jar, xml4j.jar, and db2java.zip are included in the CLASSPATH.
2. Execute the class file com.ibm.dxx.admin.Admin by entering one of the following commands in a command window:

   jre -classpath %CLASSPATH% com.ibm.dxx.admin.Admin
java -classpath %CLASSPATH% com.ibm.dxx.admin.Admin

   The XML Extender Administration wizard will start.

Address: jdbc:db2:<database name>
User ID: DB2 user id
Password: Password for DB2 user id
JDBC Driver: COM.ibm.db2.jdbc.app.DB2Driver
Select Finish.

dxxadm administration command
Please see the DB2 manual XML Extender Administration and Programming - Chapter 6 for details.

The XML Extender provides an administration command, dxxadm, for performing administration tasks. The dxxadm command calls the XML Extender administration stored procedures listed in Table 3.

 
          <<-CALL dxxadm--'---a--+-enable_db--parameters----------+------->

                       +-disable_db--parameters---------+

                       +-enable_column--parameters------+

                       +-disable_column--parameters-----+

                       +-enable_collection--parameters--+

                       '-disable_collection--parameters-'

            <--'--ASIS-----------------------------------------------------><
    

The call assumes you have the XML Extender load module library activated. If you do not, use the fully qualified name for dxxadm.

The XML Extender stored procedures

Please see the DB2 manual XML Extender Administration and Programming - Chapter 10. Each stored procedure is described in detail.

The stored procedures provided by the DB2 XML Extender can be categorized into three types:

• administration stored procedures for administration tasks
• composition stored procedures used to generate XML documents
• decomposition stored procedures used to decompose or shred XML documents

The stored procedures used for administration tasks are called by the XML Extender administration wizard and the administration command dxxadm. They are listed in Table 3.

XML Extender user-defined types (UDTs)

Please see the DB2 manual XML Extender Administration and Programming - Chapter 7 for details.

XML UDTs are required to define the columns used to store XML documents. The DB2 XML Extender provides three UDTs.

• XMLVarchar
• XMLCLOB
• XMLFILE

The data type should be defined based on the size of the document.

XML Extender user-defined functions (UDFs)

Please see the DB2 manual XML Extender Administration and Programming - Chapter 8. Each function is described in detail and examples of how to use them are provided.

The XML UDFs are used for XML columns only. They are not used for XML collections.

There are four types of XML Extender UDFs:

• Storage functions are used to insert intact XML documents in XML columns.
• Retrieval functions are used to retrieve XML documents from XML columns.
• Extraction functions are used to extract and convert the element content or attribute value from an XML document. Different extracting functions are used for various data types.
• Update functions are used to modify an entire XML document or specific element content or attribute value. It returns a copy of the XML document with the updated value.

Storing and Composing XML documents

The DB2 XML Extender provides two methods to store and access XML documents in DB2, XML Column and XML Collection. Both methods can be used in the same application.

XML Column
The XML document is stored intact in a DB2 column.

Choose this method if:

• you want to archive documents
• the documents will be read frequently
• the documents will not be updated

The XML documents are inserted into an XML enabled column and can be updated, retrieved, and searched. Element and attribute data can be mapped to DB2 side tables, which can be indexed.

XML Collection
The data for an XML document are stored in on or more DB2 tables. This method does not preserve the formatting of the XML document.

Choose this method if:

• you want to compose XML documents using data in your existing relational tables
• you want to save the data from XML documents
• data is to be exchanged between applications
• the contents of the XML document are updated frequently

XML documents can be composed using existing DB2 data, or it can be decomposed and the untagged data can be stored in DB2 tables. Stored procedures are provided to compose or decompose XML documents.

A collection is a set of columns that contain the data for an XML document. To use an XML collection, the collection name must be defined in a Document Access Definition (DAD) file and enabled. The DAD file specifies how the elements and attributes are mapped to one or more DB2 tables. The collection name is used when the stored procedures are called to compose or decompose the XML documents.

Data Type Definition (DTD)

DTDs are used to validate the structure of XML documents. It is a set of declarations for XML elements and attributes. When the database is enabled for XML, the XML Extender creates a DTD repository table called DTD_REF. DTDs are stored in this table. Each row of the DTD_REF table represents a DTD. Users can insert their own DTDs.

In an XML collection, a DTD is used to define the structure of the XML document.

Sample DTD from the XML Extender Administration and Programming manual:

<!xml encoding="US-ASCII"?>
 

<!ELEMENT Order (Customer, Part+)>
<!ATTLIST Order key CDATA #REQUIRED>
<!ELEMENT Customer (Name, Email)>
<!ELEMENT Name (#PCDATA)>
<!ELEMENT Email (#PCDATA)>
<!ELEMENT Part (key, Quantity, ExtendedPrice, Tax, Shipment+)>
<!ELEMENT key (#PCDATA)>
<!ELEMENT Quantity (#PCDATA)>
<!ELEMENT ExtendedPrice (#PCDATA)>
<!ELEMENT Tax (#PCDATA)>
<!ATTLIST Part color CDATA #REQUIRED>
<!ELEMENT Shipment (ShipDate, ShipMode)>
<!ELEMENT ShipDate (#PCDATA)>
<!ELEMENT ShipMode (#PCDATA)>

Document Access Definition (DAD)

The DAD file is required to administer the XML Extender. It specifies how XML documents are to be processed by the XML Extender and where key files like the DTD are located. The DAD file is an XML document that maps the XML document structure to one or more DB2 tables. DAD files are used for both XML column and XML collection methods. The method being used is specified in the DAD file.

Elements in the DAD are referred to as tags and the elements of the XML document are referred to as elements.

For XML columns, a DAD file is required to map the XML documents to side tables. It maps the XML elements and attributes to DB2 side tables, which are used for searching.

Sample DAD for an XML column from the XML Extender Administration and Programming manual:

<?xml version="1.0"?>
<!DOCTYPE Order SYSTEM  "dxx_install/samples/db2xml/dtd/dad.dtd">
<DAD>
   <dtdid> "dxx_install/samples/db2xml/dtd/getstart.dtd"
   </dtdid>
   <validation>YES</validation>
 
   <Xcolumn>
     <table name="order_side_tab">
         <column name="order_key" 
            type="integer" 
            path="/Order/@key" 
            multi_occurrence="NO"/>
         <column name="customer" 
            type="varchar(50)" 
            path="/Order/Customer/Name" 
            multi_occurrence="NO"/>
     </tabl>
     <table name="part_side_tab">
         <column name="price" 
            type="decimal(10,2)" 
            path="/Order/Part/ExtendedPrice" 
            multi_occurrence="YES"/>
     </table>
     <table name="ship_side_tab">
         <column name="date" 
            type="DATE" 
            path="/Order/Part/Shipment/ShipDate" 
            multi_occurrence="YES"/>
     </table>
 
   </Xcolumn>
 
</DAD>

For XML collections, a DAD file is used to define the collection name and to map elements and attributes to one or more DB2 tables. A mapping scheme is used to specify how XML data is represented in the DB2 database.

Tools like the WebSphere Studio Application Developer (WSAD) can be used to generate the DAD file. Please see the Tools section below.

Mapping Scheme
For XML collections, a mapping scheme must be selected. The DAD file uses either SQL mapping or RDB_node mapping.

SQL Mapping
SQL mapping can only be used to compose XML documents. It cannot be used to decompose XML documents.

This is a direct mapping from relational data to XML documents using a single SQL statement. In the DAD file, a valid SQL statement is defined within the <SQL_stmt> tag. The SQL statement defines the table and columns used to compose the XML document.

Sample DAD for an XML collection using SQL mapping from the XML Extender Administration and Programming manual:

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "dxx_installsamples/db2xml/dtd/dad.dtd">
<DAD>
<validation>NO</validation>
<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>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE Order SYSTEM "dxx_install/samples/db2xml/dtd/getstart.dtd"
</doctype>
 
<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>
    <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_occurrence="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>
  </element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>

SQL Mapping

SQL mapping can only be used to compose XML documents. It cannot be used to decompose XML documents.

This is a direct mapping from relational data to XML documents using a single SQL statement. In the DAD file, a valid SQL statement is defined within the <SQL_stmt> tag. The SQL statement defines the table and columns used to compose the XML document.

Sample DAD for an XML collection using SQL mapping from the XML Extender Administration and Programming manual:

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "dxx_installsamples/db2xml/dtd/dad.dtd">
<DAD>
<validation>NO</validation>
<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>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE Order SYSTEM "dxx_install/samples/db2xml/dtd/getstart.dtd"
</doctype>
 
<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>
    <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_occurrence="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>
  </element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>

Relational Database mapping (RDB_node)
RDB_node mapping can be used to compose and decompose XML documents.

This is used to define the location of the content of an XML element or the value of an XML attribute. In the DAD file, <RDB_node> tags are used to specify DB2 tables, columns, and conditions for an element or attribute node. This type of mapping is similar to the XML structure.

Sample DAD for an XML collection using RDB_node mapping from the XML Extender Administration and Programming manual:

<?xml version="1.0"?> 
<!DOCTYPE DAD SYSTEM "SQLLIB/samples/db2xml/dtd/dad.dtd> 
<DAD> 
 <dtdid>E:\dtd\lineItem.dtd</dtdid> 
 <validation>YES</validation> 
 <Xcollection> 
 <prolog>?xml version="1.0"?</prolog> 
 <doctype>!DOCTYPE Order SYSTEM
                   "SQLLIB/samples/db2xml/dtd/getstart.dtd"
 </doctype>
 <root_node> 
 <element_node name="Order"> 
 <RDB_node> 
 <table name="order_tab"/> 
 <table name="part_tab"/> 
 <table name="ship_tab"/> 
 <condition>order_tab.order_key=part_tab.order_key AND
              part_tab.part_key=ship_tab.part_key 
 </condition> 
 </RDB_node> 
 <attribute_node name="Key"> 
 <RDB_node> 
 <table name="order_tab"/> 
 <column name="order_key"/> 
 </RDB_node> 
 </attribute_node> 
 <element_node name="Customer">
 
        <element_node name="Name">
            <text_node> 
               <RDB_node>
                  <table name="order_tab"/>
                  <column name="customer_name"/>     
                </RDB_node>
           </text_node>
        </element_node>
        <element_node name="Email">
           <text_node>
               <RDB_node>
                   <table name="order_tab"/> 
                   <column name="customer_email"/> 
               </RDB_node> 
           </text_node> 
        </element_node> 
  </element_node> 
      <element_node name="Part">
          <attribute_node name="Key">
                <RDB_node>
                    <table name="part_tab"/>
                    <column name="part_key"/>
                </RDB_node>
          </attribute_node>
          <element_node name="ExtendedPrice"> 
                <text_node> 
                   <RDB_node> 
                      <table name="part_tab"/> 
                      <column name="price"/>  
                      <condition>price > 2500.00</condition> 
                   </RDB_node> 
                 </text_node>  
           </element_node> 
           <element_node name="Tax">  
                <text_node> 
                   <RDB_node> 
                       <table name="part_tab"/> 
                       <column name="tax"/>  
                   </RDB_node> 
                </text_node>  
           </element_node> 
 
           <element_node name="Quantity">  
                <text_node> 
                   <RDB_node> 
                       <table name="part_tab"/> 
                       <column name="qty"/>  
                   </RDB_node> 
                </text_node>  
          </element_node> 
          <element_node name="Shipment" multi_occurrence="YES">
               <element_node name="ShipDate"> 
                   <text_node> 
                      <RDB_node>
                          <table name="ship_tab"/>  
                          <column name="date"/>  
                          <condition>date > '1966-01-01'</condition> 
                      </RDB_node> 
                   </text_node>  
               </element_node> 
               <element_node name="ShipMode">  
                   <text_node> 
                      <RDB_node>
                          <table name="ship_tab"/> 
                          <column name="mode"/>  
                      </RDB_node> 
                    </text_node>  
               </element_node> 
               <element_node name="Comment"> 
                    <text_node> 
                      <RDB_node> 
                          <table name="ship_tab"/>
                          <column name="comment"/>  
                      </RDB_node> 
                    </text_node>  
               </element_node> 
         </element_node>  
      </element_node>  
    </element_node>  
</root_node> 
 
</Xcollection>
 
</DAD>

Side Tables
Side tables are used for XML columns. The content of an XML column is mapped to side tables. Side tables are DB2 tables used to store the elements and attributes of an XML document which will be searched frequently. When the XML document is updated, the values in the side tables are automatically updated.

The side tables can be indexed to improve search performance.

Note: Do not modify the side tables. The XML Extender will update the side tables automatically when the XML document in the XML column is updated.

Location paths
The location path specifies the location of an element or attribute within an XML document. The XML Extender uses the location path to navigate the XML document to locate the elements and attributes.

In XML columns, the location path is used to map the content of an XML element or attribute to a side table. It is also used to identify the elements and attributes to be extracted or updated when using the XML Extender user-defined functions.

In XML collections, the location path is used to override the XML collection DAD file values in a stored procedure.

Back to top

How to use the DB2 XML Extender

Before using the XML Extender, you should decide how you want to store the document. For example, as an intact document stored in a DB2 column, or decomposed and stored in multiple DB2 tables.

When storing the XML document intact in an XML column, you will need to:

• Understand the structure of the document.
• Determine how you want to search the document.
• Determine the XML user-defined type in which you will store the document
• Determine the XML elements and attributes which will be frequently searched so they can be store in side tables and indexed.

When composing or decomposing an XML document using an XML collection, you will need to:

• Understand the structure of the document.
• Determine if the XML data will be validated. It is recommended.
• Determine how the XML data will be mapped to the DB2 tables.
• Determine the mapping scheme to use. SQL mapping or RDB_node mapping.
• Create a DAD file.

XML Column

Please see the DB2 manual XML Extender Administration and Programming for detailed instructions.

Store an XML document in an XML column

1. Create the database and tables. (if it does not already exist).
2. Bind the database with the XML Extender stored procedures and the DB2 CLI.
3. Enable the database for the XML Extender. Call the dxxEnableDB() stored procedure.
4. Insert the DTD used to validate the XML document in the XML column.
5. Create a table which will contain an XML enabled column.
6. Enable the XML column.
7. Create indexes on the side tables for the XML column.
8. Insert the XML document into the XML column.
9. Test. Execute a select statement which returns the XML document.

XML Collection

Please see the DB2 manual XML Extender Administration and Programming for detailed instructions.

Compose an XML document from an XML collection

1. Create the database and tables (if it does not already exist).
2. Bind the database with the XML Extender stored procedures and the DB2 CLI.
3. Enable the database for the XML Extender. Call the dxxEnableDB() stored procedure.
4. Map the structure of the XML document to DB2 tables that contain the contents of the element and attribute data.
5. Select the mapping method. SQL mapping or RDB_node mapping.
6. Create the XML collection. Create the DAD file for the XML collection. This will define the name for the XML collection and map elements and attributes to DB2 tables.
7. Enable the XML collection. Required if using the dxxRetrieveXML() stored procedure to compose the XML document. Not required when using the dxxGenXML() stored procedure to compose XML documents.Call the dxxEnableCollection() stored procedure. This will register the collection in the XML_USAGE table and helps to improve performance when you specify the collection name when calling stored procedures.
8. Create a result table to store the XML document (if it does not already exist). This table should contain one column of type XMLVARCHAR or XMLCLOB.
9. Compose the XML document. For documents which will be updated occasionally: Call the dxxGenXML() stored procedure with the name of a DAD file. dxxGenXML() composes XML documents using data stored in the XML collection tables specified by the <Xcollection> element in the DAD file. The XML document is inserted into the result table. For documents which will be updated frequently: Call the dxxRetrieveXML() stored procedure with the name of an enabled XML collection. dxxRetrieveXML() retrieves a decomposed XML document. The XML document is inserted into the result table.
10. Verify that the XML document has been composed. Select the XML document from the result table.
11. Export the XML document from the table to a file. Call the Content() retrieval UDF.
12. Optional: Transform the XML document into an HTML file. Create a stylesheet. Call the XSLTransformToFile() UDF.

Decompose and store an XML document in an XML collection

1. Create the database (if it does not already exist).
2. Bind the database with the XML Extender stored procedures and the DB2 CLI.
3. Enable the database for the XML Extender. Call the dxxEnableDB() stored procedure.
4. Map the structure of the XML document to the DB2 tables that contain the contents of the element and attribute values.
5. Create the XML collection. Create the DAD file for the XML collection. Use RDB_node mapping. This will define the name for the XML collection and map elements and attributes to DB2 tables.
6. Enable the XML collection.
• Required if:
• the tables in the XML collection do not already exist
• the dxxShredXML() stored procedure will be used to decompose the XML document
• Not required if:
• the tables in the XML collection already exist
• the dxxInsertXML() stored procedure will be used to decompose the XML document
• Call the dxxEnableCollection() stored procedure. All tables in the XML collection will be created by the XML Extender when the collection is enabled.
7. Decompose the XML document. Call one of the decomposition stored procedures. dxxShredXML() or dxxInsertXML().

dxxShredXML() - decomposition stored procedure Choose this method if there will be occasional updates or if you do not want the overhead of administering the XML data. A DAD file is used. The XML collection does not have to be enabled.

Call the dxxShredXML() stored procedure with a DAD file and the XML document. The tables used in the <Xcollection> of the DAD file must exist with the columns specified in the DAD mapping. The untagged XML data from the XML document is inserted into an XML collection in the tables specified according to the <Xcollection> specification in the DAD file.

dxxInsertXML() - decomposition stored procedure Choose this method if there will be regular updates. An enabled XML collection is required.

Call the dxxInsertXML() stored procedure with an enabled XML collection which is associated with a DAD file. The collection tables are checked or created according to the specifications in the <Xcollection>. The XML document is decomposed according to the mapping and the untagged XML data is inserted into the tables of the XML collection.

Back to top

Tools

WebSphere Studio Application Developer

WebSphere Studio provides an XML development environment which includes tools for building DTDs, XML schemas, and XML files. The following XML tools are provided:

• XML editor - create, view and validate XML files
• DTD editor - create, view, and validate DTDs
• XML schema editor - create, view, and validate XML schemas
• XSL trace editor - apply an XSL stylesheet to an XML document to transform an XML document into HTML, text, or other XML document types
• XML to XML mapping editor - map one or more source XML document to one target XML document
• XML to SQL query wizard - create an XML file from the result of an SQL query
• RDB to XML mapping editor - define the mapping between relational tables and a DTD file, and generate a DAD file

There is a series of articles on XML and WebSphere Studio Application Developer. The article WebSphere Studio Application Developer -- Part 5: Exploring the RDB to XML Mapping Editor shows how the RDB to XML mapping editor can be used to generate a DAD file.

Resources

• XML Extender Administration and Programming Manual
  
  
• SQL Reference Volume 1
  
  
• Meet the Experts: Susan Malaika on XML Capabilities in DB2
  
  
• XML and WebSphere Studio Application Developer -- Part 5: Exploring the RDB to XML Mapping Editor
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• DB2 and XML
• SQL/XML
• The DB2 XML Extender
• How to use the DB2 XML Extender
• Tools
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers