Before you start
Learn how to use Rational Data Architect for SQL/XML query generation in DB2® for Linux®, UNIX®, and Windows®. The SQL/XML query language is used to extract data from relational sources and transform it into XML format.
This tutorial shows you, step by step, how to:
- Graphically define a mapping model between a relational database model as the source and an XML schema definition as the target
- Generate an SQL/XML query based on this mapping model
- Execute the SQL/XML query and analyze the result
- Define joins between multiple source columns
- Add transformation functions to the mapping model
After taking this tutorial, you should be able to use Rational Data Architect and its mapping editor to generate and run SQL/XML queries.
This tutorial assumes familiarity with relational databases, preferably DB2. You should also have a good understanding of the XML and XSD standards. Basic knowledge of the SQL/XML query language is required. Basic knowledge of Rational Data Architect is beneficial, but not required. (Consult the article "Use Rational Data Architect to integrate data sources" (developerWorks, March 2006) and part one of this series, "Access and integrate enterprise metadata with Rational Data Architect" (developerWorks, July 2006), for reference.)
In order to execute the steps that are described in this tutorial, it is necessary to have Rational Data Architect 6.1 and DB2 installed. You can download trial versions of IBM Rational Data Architect 6.1 (see Resources) and DB2 V8.2 (see Resources).
- Install DB2 V8.2..
- Install Rational Data Architect V6.1.
- Unpack the package RDA_SQLXML.zip into a folder (for example, C:\RDA_Tutorials). This will create the RDA_SQLXML folder.
- Start Rational Data Architect and specify the folder where you unpacked the package as the location for your workspace (for example, C:\RDA_Tutorials).
- The folder RDA_SQLXML that is in the package is a Rational Data Architect data project folder. In Rational Data Architect you have to import it into your workspace. From the File menu, select Import.
- Select the Existing Project into the Workspace Wizard.
Figure 1. Import Wizard selection
- Click Next. Browse to the location where you extracted RDA_SQLXML.zip (for example, C:\RDA_Tutorials).
Figure 2. Project Import Wizard
- Click Finish. As a result you see the RDA_SQLXML project with a set of data models, XSD schemas, and mappings model files in your workspace.
Figure 3. Data Project Explorer after project import
- Now you need sample data for the relational database. The data will be used to fill the generated XML instance document with data. This data will be extracted from the relational database and inserted into the XML instance document during SQL/XML query execution. The script file rda_sqlxml_db.sql creates a DB2 database LIBRARY and inserts a set of sample data. Deploy the script in your DB2 database. Start the DB2 command window (Windows menu Start > IBM DB2 > Command Line Tools > Command Window).
- Change to the folder where you extracted RDA_SQLXML.zip.
- Run this command:
db2 -tvf rda_sqlxml_db.sql. This will create the database LIBRARY, define primary and foreign keys, and insert sample data.
Figure 4. Creating LIBRARY database
- Connect to the database LIBRARY in Rational Data Architect. In the Database Explorer, right-click on Connection and select New Connection. (Note: If you don't see the Database Explorer, make sure that you are in the Data Perspective, shown in Figure 6 below.)
Figure 5. Creating new database connection
Figure 6. Data perspective
- Specify the connection information according to your environment, similar to Figure 7.
Figure 7. Database connection settings
- Click Test Connection to check whether all parameters are set correctly. If the test is successful, click Finish.