Introduction

IBM WebSphere ® Studio Application Developer is an application development product that supports the building of a large spectrum of applications using different technologies such as JSP, servlets, HTML, XML, Web services, databases, and EJBs. In particular, Application Developer provides tight integration between XML and relational data. Application Developer supports all of the databases that WebSphere Application Server supports, including DB2 ® , Oracle Sybase and Microsoft ® SQL Server ^TM .

This is Part 5 of a series of articles focusing on the XML tools provided with WebSphere Studio Application Developer. Part 5 focuses on the RDB to XML Mapping Editor. The RDB to XML Mapping Editor allows the user to map XML data to relational data, and vice versa. The tool can then be used to generate a DAD file , which you can use with the DB2 XML Extender to retrieve or store relational data in XML files.

We recommend that you read the other articles in this series:

• Part 1 Learn how to use Application Developer to develop XML Schema.
• Part 2 Learn how to create an SQL query using Application Developer's SQL Builder.
• Part 3 Learn about the Application Developer features available to incorporate data access and XML in your application.
• Part 4 Learn how to use the XML Editor, a visual tool for creating and editing XML documents.
• Part 5 Learn how to use the RDB to XML Mapping Editor to create DAD files for use with DB2 XML Extender.
• Part 6 Learn how to use the XML Schema Editor and the XML Editor together to develop XML applications that make use of XML namespaces.

Back to top

Prerequisites

To follow and execute this example, you need the following installed:

• WebSphere Studio Application Developer 4.0
• DB2 UDB, Version 7.1 fixpack 3 or later
• DB2 XML Extenders, Version 7.2 fixpack 5

Note that the examples can be run on either Windows ® or Linux operating systems.

Back to top

Concepts

What is the DB2 XML Extender?

DB2's XML Extender is an extension of DB2 that provides the ability to store XML documents or generate XML documents from existing relational data. The DB2 XML Extender also provides new data types, functions, and stored procedures to manage XML data in DB2.

For more information on the DB2 XML Extender, refer to the DB2 XML Extender Library .

What is a DAD file?

A DAD (Document Access Definition) file is an XML-formatted file that associates XML document structure to a DB2 database. It is used by the DB2 XML Extender to compose and decompose XML data into relational data. For more information on DAD files and their particular syntax, refer to the DB2 XML Extender Library .

Back to top

Motivation

Why use the RDB to XML Mapping Editor?

The RDB to XML Mapping Editor is designed to work in conjunction with the DB2 XML Extender. It simplifies development tasks by:

• Providing a visual interface to easily define mappings between relational data and XML elements and attributes.
• Automatic generation of DAD files that would normally need to be coded by hand and could be quite error prone.
• Automatic generation of a test harness on multi-platforms that can be used to enable the DB2 database for use by the DB2 XML Extender , as well as store and retrieve XML files from relational data.

DB2 XML Extender versus the SQL to XML component

In Part 3 of this series, we introduced the SQL to XML wizard and the corresponding Java ^TM library in WebSphere Studio Application Developer. As we mentioned, the RDB to XML Mapping Editor is designed to work with the DB2 XML Extender. However, if an organization uses other database vendors, the SQL to XML component provides an alternative. The SQL to XML component supports all JDBC databases that are currently supported by WebSphere Application Server.

The main difference between the RDB to XML Mapping Editor/DB2 XML Extender and the SQL to XML component is that the former provides more flexibility when defining the mapping structure between relational tables and the XML data. Using the DB2 XML Extender and the Mapping Editor, you can map arbitrary SQL queries or multiple tables to an XML document. When using the SQL to XML component, the mapping is strictly one to one, based on a set of specific mapping patterns.

Back to top

Extending the Video Store example

The example in this article is an extension of the previous article's Video Store sample. For this example, we are interested in retrieving and storing information on all of the customers ( Customer Id and Customer Name ) and all of the videos ( Vid Id and Video Name ) they have rented and the date they rented the videos. This will be done using the RDB Table Map option in the RDB to XML Mapping tool to generate a DAD file and a test harness. The test harness will be used to enable the DB2 database for use by the DB2 XML Extender , as well as store and retrieve XML data from a relational database. The data for this example is stored in three database tables -- CUSTOMERS, VIDEOS, and RENTALS -- that are found in the VIDEOS database.

The input and output XML files will use the following DTD file. See Listing 1 below.

<?xml version="1.0" encoding="UTF-8"?>
<!ELEMENT CustomerRental (Customer*)>
<!ELEMENT Customer (Rental*)>
<!ATTLIST Customer
   ID CDATA #IMPLIED
   Name CDATA #IMPLIED>
<!ELEMENT Rental (VideoName)>
<!ATTLIST Rental
   Date CDATA #IMPLIED
   VidID CDATA #IMPLIED>
<!ELEMENT VideoName (#PCDATA)>

Create directories for the project

In the Workbench, create a project called CustomerRentals . In this project, create three folders: db , dtd , and testharness . These directories will be used to hold the imported database, the CustomerRental.dtd file, and the generated test harness files respectively.

Connect to a database and import a local copy

Refer to Part 2 of this series on how to connect to the VIDEOS database and to import a local copy into the CustomerRentals\dB directory specified above.

Back to top

The new RDB to XML Mapping Wizard: Create the RDB to XML Mapping session

Before we can create our mappings, we need to create an RDB to XML Mapping session. The information about this mapping session, including source tables, the target DTD file, any mappings created, and join conditions will be stored in a file with a .rmx extension. This file is necessary for us to persist information about any mappings we have created so that we can easily reload or modify them in the future. We will complete the following steps to create our initial RDB to XML Mapping session.

1. Start the RDB to XML Mapping Wizard
2. Name the RDB to XML Mapping session
3. Choose the type of mapping
4. Choose the source RDB tables
5. Choose the target DTD file
6. Choose the root element for the target DTD file

1. Start the new RDB to XML Mapping Wizard

We will now create an RDB to XML Mapping session. In Application Developer:

1. In the top menu, select File => New => Other . A window with the title, New , opens.
2. Select XML on the left side.
3. Select RDB to XML Mapping on the right side.
4. Click Next . The Specify New RDB to XML Mapping session page opens.

2. Name the RDB to XML Mapping session

On this page, we specify the name and location where we want the session file to be created.

1. Select the CustomerRentals folder (this was created earlier).
2. Specify the file name, CustomerRentals.rmx .
3. Click Next .

3. Choose the type of mapping

The RDB to XML Mapping tools provide two ways of mapping relational information to XML data, either through the RDB Table Map or the SQL Statement Map. Both of these methods of mapping use the DAD file to represent their mappings, but use different tags.

The RDB Table Map maps RDB columns to XML data. Optional join conditions can be specified. This method is useful if you want to simply extract or store information in database tables. The DAD file created from an RDB Table Map can be used for both decomposing an XML file and storing it as relational data, and also for taking relational data and composing an XML file from it.

The SQL Statement Map takes an SQL statement and allows the user to map the result columns from executing the SQL Statement to XML attributes and elements. This method is useful if you have complex queries that you would like to represent as XML data. These SQL statements need to have been created using Application Developer's SQL Builder, as shown in Part 2 . The key difference between the RDB Table Map and SQL Statement Map is that the SQL Statement Map can only be used for taking relational data and composing an XML file from it, not the reverse.

For the purpose of this example, we will be working with an RDB Table Map.

1. Select RDB Table to XML Mapping .
2. Click Next .

4. Choose the source RDB tables

On this page, we select the RDB tables that we want to map columns from. For this example, we are interested in three tables: CUSTOMERS, VIDEOS, and RENTALS.

1. Expand the tree under CustomerRentals\dB (we imported the database here earlier).
2. Select CUSTOMERS .
3. Press Ctrl and RENTALS .
4. Press Ctrl and VIDEOS . Only the three tables we want should be highlighted.
5. Click Next .

5. Choose the target DTD file

We need to specify a DTD that our source and target XML files will conform to. If you have not already imported the DTD file, CustomerRental.dtd , into your CustomerRental\dtd folder, you can select the Import button to do it now.

1. Select CustomerRental.dtd .
2. Click Next .

6. Choose the root element for the target DTD file

Here, we need to select the root element of the target DTD file. This will be used to allow us to represent the DTD file in an XML format, so that we can map RDB table columns to particular elements and attributes of an XML document.

1. Select CustomerRentals .
2. Click Finish .

We have now created an RDB to XML mapping session. The session will open in the RDB to XML Mapping Editor.

Back to top

Using the RDB to XML Mapping Editor

The RDB to XML Mapping Editor is where we will be doing all of our work. From here, we will complete the following tasks:

1. Create mappings for our RDB columns to XML elements and attributes .
2. Specify join conditions for our source tables .
3. Generate a DAD file and test harness .

Click the CustomerRentals.rmx tab, and you will see the two panes, as shown in Figure 3 below.

The pane on the left, titled Tables , represents all of the RDB table columns that can be mapped. In this case, we will see the CUSTOMERS, RENTALS, and VIDEOS tables with all of their columns.

The pane on the right, titled XML , shows the XML representation of the DTD file CustomerRentals.dtd that would be created by selecting CustomerRentals as the root element.

Besides the RDB to XML Mapping view, two other views are available: the Outline view, which shows all of the current mappings, and the Overview view, which shows a summary view of the XML document as well as everything that has been mapped to the XML document's attributes and elements.

In the top menubar, there is a menu item, Mapping , which shows functions that are specific to the RDB to XML Mapping tool.

On the toolbar, there are two additional icons: the icon allows you to generate a DAD file, and the icon allows you to specify join conditions .

1. Create mappings

From the RDB to XML Mapping view, we can define mappings between relational data and XML elements and attributes. To create a mapping between the CUSTOMERS.CUST_ID RDB column to the ID XML attribute under the CustomerRentals/Customer element, do the following:

1. In the Tables pane, select the CUST_ID column under the CUSTOMERS table.
2. In the XML pane, select the ID attribute under the CustomerRentals/Customer element.
3. Right-click to bring up the context menu, and select Create Mapping .
   
   Figure 5. The RDB to XML Mapping view context menu
   
   

A mapping is now created between the CUST_ID column and the ID attribute of the CustomerRentals/Customer element. You will see this mapping listed in both the Outline and Summary views. If you look in the RDB to XML Mapping view, you will notice that there are small arrows beside the CUST_ID column and ID attribute, denoting that these items have been mapped. When we generate a DAD file , this mapping information will be reflected in the generated DAD file and can be used by the DB2 XML Extender to compose or decompose XML files to relational data.

In total, we need to create five mappings for this example:

• Table CUSTOMERS column ID to attribute ID under the Customer element.
• Table CUSTOMERS column NAME to attribute Name under the Customer element.
• Table RENTALS column DATE to attribute Date under the Rental element.
• Table RENTALS column VID_ID to attribute Vidid under the Rental element.
• Table VIDEOS column TITLE to element VideoName under the Rental element.
  
  Figure 6. The RDB to XML Mapping view after mappings
  
  

2. Specify join conditions

Since we have more than one table in our example, we need to specify join conditions for our tables. A join condition is used to specify which column in a table is to be matched with a column in another table. If a join condition between two tables is not specified, a result set will be returned containing all possible combinations of the two tables. More than likely, this will return incorrect results, as invalid rows may be returned. For our example, we need to specify join conditions between the CUSTOMERS and RENTALS tables as well as between the RENTALS and VIDEOS tables.

If you click the Generate DAD file button, and there is more than one source table and no join conditions have been specified, then a warning message appears prompting the user to add join conditions or continue without them.

To specify join conditions, click the Edit Join Conditions button on the toolbar. A window containing a table opens, allowing us to specify the columns we would like to join. In this scenario, we want to join the CUST_ID column from CUSTOMERS table with the CUST_ID column from the RENTALS table.

Steps to add a join condition between CUSTOMERS.CUST_ID and RENTALS.CUST_ID :

1. Select CUSTOMERS.CUST_ID in the first column. An "="will be populated in the second column. This field is not editable since "=" is the only possible value for this column.
2. Select RENTALS.CUST_ID in the third column.

We have now successfully created a join condition. If we need to add another join condition, we would go to the next row and repeat the steps. The fourth column would automatically be populated with the word, '"AND." The AND column is not editable and will be removed if not needed.

For this example, we also need to create a join condition between RENTALS.VID_ID and VIDEOS.VID_ID . Repeat the steps above to create this join condition. The Edit Join Conditions page should look similar to Figure 7 above.

If you want to remove a join condition, simply select the row, right-click and select Remove Join Condition .

Once the appropriate join conditions have been created, click Finish .

3. Generate the DAD file and test harness

After all of the mappings have been created and all of the join conditions specified, it is time to generate a DAD file. On the toolbar, click the Generate DAD file icon to launch the Generate DAD file wizard.

Specify the DAD file name

The first page that comes up in the Generate DAD file wizard is the Specify DAD File Name page. This is where we specify location and name of the DAD file to be generated.

1. Select the CustomerRentals\testharness folder that we created earlier.
2. Specify a filename of CustomerRentals.dad .

If we were only interested in generating a DAD file, we would click Finish and be done. But we want to specify an enclosing tag and generate a test harness, so click Next .

Specify an enclosing tag

Specifying an enclosing tag is an option that allows us to specify a tag to enclose the entire document. This is useful in the situation where we are composing an XML file, and the top element of an XML document contains PCDATA or an attribute, and the value of this PCDATA or attribute can have multiple values. In this case, multiple XML files may be generated as output, since we have multiple top elements. The enclosing tag option allows a convenient way to specify a single tag that would be used to enclose the results, and consequently, only one XML document will be returned. In the case where this option is chosen, the store XML feature will not work with the generated DAD file.

In our scenario, we do not need to specify an enclosing tag. Click Next .

Generate a test harness

The purpose of the test harness is to provide operating system-specific command line files that will enable the DB2 database for the XML extender , and process the DAD file in order to generate XML from relational data or to decompose XML data into relational data.

To generate the test harness, select the Generate Test Harness check box, and complete the following.

1. In the Folder field, browse to the CustomerRentals\testharness folder and select it.
2. Now provide the environment variable values. These are the environment variables that contain specific path information on the destination machine where the test harness will actually be executed . This does not necessarily need to be on the same machine or operating system that we are currently running Application Developer on.
   
   1. Populate the SQLLIB environment variable with the location of the DB2 SQLLIB directory.
   2. Populate the DB2EXTENDER environment variable with the directory of the DB2 XML Extenders. Generally, this directory contains dxx in it (that is, e:\dxx on Windows or /home/db2inst1/dxx on Linux).
   3. Populate the XMLDIR environment variable with the location of where you want the generated XML file to be placed. This is also the same location where storeXML will look for source XML files. This directory must exist before actually executing the test harness.
      
      
   
   
3. Now select the destination platform. The destination platform is the platform on which the test harness files will actually be executed. This is very important since it determines OS-specific information in the generated test harness files, such as file separators and how environment variables are accessed.
   
   1. Select Windows. (Select UNIX® if you are going to run the test harness on Linux.)
   2. Click Finish to generate the test harness files.
      
      

   The test harness files will be generated to the directory specified in the Destination Folder field. The DAD file will be opened in Application Developer, and may be edited. The changes made to the generated DAD file are local to the DAD file, and are not reflected in the RDB to XML Mapping session file. As a result, the next time a regeneration of the DAD file occurs, any previous changes will not be reflected.

   
   Figure 8. The Generate Test Harness page
   
   

Back to top

Enable DB2 for the XML Extender

Before we can store and retrieve XML documents from DB2 with the DB2 XML Extender, we need to first enable the database for XML. As stated in the DB2 XML Extender documentation , this will:

• Create all the user-defined types (UDTs) and user-defined functions (UDFs).
• Create and populate control tables with the necessary metadata that the XML Extender requires.
• Create the db2xml schema and assign the necessary privileges.

The enabling of the database for XML is done for us in the test harness files, retrieveXML and storeXML . For more information on the specifics, and how to manually enable the database for XML, refer to the DB2 XML Extender documentation .

Back to top

Run the test harness

Go to the directory where the test harness was generated: CustomerRentals\testharness . Make sure that the database has been started before executing any of the test harness files.

Back to top

Run the setup file

On Windows, run setup.bat in a command shell. This file sets up the environment variables that the other files use. After running setup.bat on Windows, an initialized DB2 shell appears. All other test harness files will need to be run from this shell.

On Linux, you will need to source the setup in a command shell in order to initialize the environment. All other test harness files must be run in this shell.

Back to top

Run the updateDTD_REF file

If you need to enable validation or want the DTD file to be inserted into the DB2XML.DTD_REF table, run the updateDTD_REF file. To enable validation, you will need to modify the DAD file manually to change the VALIDATION tag to YES . By default, it is NO . Any changes made to the generated DAD file will not be reflected in the RDB to XML Mapping session file, and will not show up in any future generations of the DAD file.

In our scenario, you do not need to run this command.

Back to top

Run the retrieveXML file

The retrieveXML file will be used to enable the DB2 database for DB2 XML Extenders , as well as retrieve relational data and store it in an XML file following the mappings defined in the DAD file.

The result of enabling the database, as well as the resulting XML data, will be displayed to the screen. The resulting XML data will also be written to a file in XMLDIR , and will have the same name as your session file but with a .xml extension. In our example, this file will be called CustomerRental.xml . If there are multiple XML files returned, only the first one will be written to XMLDIR .

On Windows, run the retrieveXML.bat file from the DB2 shell that was created by running setup.bat .

On Linux, run retrieveXML.sh in the same command shell where setup was sourced.

A sample result XML file of running retrieveXML using our DTD would be stored in XMLDIR under the file name CustomerRental.xml , and would resemble Listing 2 below.

<?xml version="1.0"?>
<!DOCTYPE CustomerRental PUBLIC "CustomerRentalId" "CustomerRental.dtd">
<CustomerRental>
   <Customer Name="John Doe" ID="500">
      <Rental Date="2000-05-25" Vidid="100">
         <VideoName>The Adventures of Kernighan and Ritchie</VideoName>
      </Rental>
      <Rental Date="2000-05-25" Vidid="101">
         <VideoName>The taming of the Foo</VideoName>
      </Rental>
   </Customer>
   <Customer Name="Jane Doe" ID="501">
      <Rental Date="2000-06-01" Vidid="100">
         <VideoName>The Adventures of Kernighan and Ritchie</VideoName>
      </Rental>
   </Customer>
   <Customer Name="Bob Smith" ID="502">
      <Rental Date="2000-10-10" Vidid="104">
         <VideoName>Bar Trek 4 The Voyage Home Directory</VideoName>
      </Rental>
      <Rental Date="2000-10-11" Vidid="104">
         <VideoName>Bar Trek 4 The Voyage Home Directory</VideoName>
      </Rental>
      <Rental Date="2001-02-05" Vidid="105">
         <VideoName>High RISC</VideoName>
      </Rental>
   </Customer>
   <Customer Name="Roberta Smith" ID="503">
      <Rental Date="2000-09-01" Vidid="102">
         <VideoName>Take out the Garbage Collection</VideoName>
      </Rental>
   </Customer>
</CustomerRental>

Back to top

Run the storeXML file

The storeXML file will be used to enable the DB2 database for DB2 XML Extenders , and store the data in XML elements and attributes in RDB tables. It will also delete all rows in the tables before it inserts rows. This file will take the input XML file from the XMLDIR directory. It will look for a file with the same name as your session file, but with a .xml extension. In our example, it will look for a file called CustomerRental.xml . You can use the CustomerRental.xml file provided with this example, or you can use the result from running retrieveXML as input. Copy the file CustomerRental.xml to the directory specified earlier in your XMLDIR environment variable.

On Windows, run the storeXML.bat file from the DB2 shell that was created by running setup.bat .

On Linux, run storeXML.sh in the same command shell where setup was sourced.

To validate that the store worked correctly:

• Check the database manually for the data that you expect to be there, or
• Run the retrieveXML file first, copy the resulting XML file, then run the retrieve again after running the store, and compare the resulting XML file to the original file.

Back to top

Summary

This article has shown how touse the RDB to XML Mapping Editor to:

• Create an RDB Mapping session file.
• Map RDB columns to XML elements and attributes.
• Specify join conditions for tables.
• Generate a DAD file.
• Generate a test harness.
• Run the test harness to enable DB2 for the XML Extenders and to create an XML file from database data.
• Run the test harness to enable DB2 for the XML Extenders and to store XML data in relational tables.

Back to top

Questions from users

Question: I followed all the steps outlined above, and have successfully generated test harnest. However, when Itry to run the "retrieveXML.bat" file, i got nothing returned. An error given is during the execution of "c:\dxx\bin\tests2x.exe VIDEOS CustomerRental.dad result_tab" and produced n=0:0 errCode=-81:0 msgtext'DXXQ021E Table CUSTOMERS does not have column CUST_ID. ':0 .

I've check the database, the column is there. I've tried different join techniques, and realised that the target of the join statement during test harnest creation will output this error, e.g. if I have customers.cust_id = rentals.cust_id, then cust_id from rental will produce error, or rentals.vid_id = videos.vid_id, then vid_id from video will produce error. Please help...

Response from author: The problem seems to be with the database schema not being specified in the DAD file. This was a problem that has since been fixed, but I think that it was still the in V4 release of the product. The problem appears to be that in the DAD file, all the tables are not prepended with the appropriate schema. For example, customers.cust_id should be xmltest.customers.cust_id and so on. So, the equivalent line that you describe should have been: xmltest.customers.cust_id = xmltest.rentals.cust_id. Unfortunately, this is not generated and would have to be changed manually in the DAD file after generation. The only scenario that would work immediately after would be if the tables existed in the local schema, i.e., the tables were created without xmltest (they would be created in the local schema). On my machine, it would have been created as russelld.customers.cust_id, and then the generated DAD file would work properly.

So, if this is the problem, there are two solutions: 1) Update the generated DAD file with schema information, or 2) Create the VideoStore example in the local schema. As I mentioned above, this schema problem has been resolved in V5.

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Concepts
• Motivation
• Extending the Video Store example
• The new RDB to XML Mapping Wizard: Create the RDB to XML Mapping session
• Using the RDB to XML Mapping Editor
• Enable DB2 for the XML Extender
• Run the test harness
• Run the setup file
• Run the updateDTD_REF file
• Run the retrieveXML file
• Run the storeXML file
• Summary
• Questions from users
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers