Mapping from relational databases to XML files

You can use the relational database (RDB) to XML mapping editor to map relational database data to elements and attributes in an XML file. From the mapping, you can generate a Document Access Definition (DAD) file that can be used with the IBM DB2 XML Extender to generate XML documents from existing relational data, or to decompose XML documents into relational data. The mapping is saved in a session file with the extension .rmx .

To use the RDB to XML mapping editor, and deploy the DAD file, have installed and configured IBM DB2 Universal Database version 8.1 or higher (version 8.1 or higher includes IBM DB2 XML Extender). If you want to map an SQL statement to a DTD, have a valid SQL SELECT or FULLSELECT statement that contains an ORDER BY clause.

The following instructions were written for the Resource perspective, but they will also work in many other perspectives.

The steps that you would typically follow to map your data and generate a DAD file are as follows:

1. Select a folder and start the RDB to XML mapping wizard...

   File > New > Other > XML > RDB to XML Mapping

   If you cannot see the XML option, select the Show All Wizards check box.

2. In the wizard, specify what type of mapping you would like to create:
   • RDB table to XML mapping

     This creates a mapping between a relational database table and an XML document. The DAD file you generate from this type of mapping can be used to store and retrieve data from DB2 databases.

   • SQL Query to XML mapping

     The DAD file you generate from this type of mapping can be used to compose an XML document from an SQL query.

3. Specify the source RDB table(s) or SQL query, and the target DTD file for the mapping.

4. If you are mapping tables to a DTD, specify join conditions between RDB tables (if necessary) and map RDB table columns and XML elements and attributes.

5. If you are mapping an SQL statement to a DTD, map the SQL statement columns and XML elements and attributes.

6. Generate the DAD file.

Once you have created your DAD file, you can run it with the DB2 XML Extender. You can also generate a DADX file from the DAD file using the DADX wizard.

When creating an RMX session file, you are only allowed to create a session in an EJB project under any subdirectory of EJBProjectName/ejbmodule. As well, you are only allowed to create a session in a Web project under any subdirectory of WebProjectName/Web Content. You can manually move your RMX session file to an invalid directory (for example, directly under EJBProjectName or WebProjectName), but if you do this, the product will not be able to resolve the RMX session properly.

Relational database to XML mapping (visual DAD builder)

The relational database (RDB) to XML mapping editor is a visual tool used for mapping relational tables or an SQL statement to a DTD. After completing the mapping, you can generate a document access definition (DAD) file that can be run by the DB2 XML Extender to compose or decompose other XML documents into DB2 data.

Specifying the source tables and target DTD file

Using the relational database (RDB) to XML mapping editor, you can select data from one or more relational tables and map it to elements and attributes of an XML document. The tables must exist in the workbench before you can specify them in the RDB to XML Mapping wizard.

Editing an RDB to XML mapping
After you have created a mapping, you can open it in the RDB to XML mapping editor by double-clicking it in the Navigator view.

Generating DAD files
Once you have mapped the columns to the DTD file, you can generate and test a DAD file. To test the DAD file, have the DB2 XML Extender installed and configured.