The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Rational Data Architect skills series, Part 2: Generate SQL/XML queries with Rational Data Architect

Transform data from relational data sources into XML format

Torsten Bittner (tbittner@us.ibm.com), Software Engineer, IBM

Torsten Bittner works as a software engineer in Information Management, IBM Software Group. He carries a diploma degree in computer science from the University of Rostock, Germany. His development responsibilities include the Rational Data Architect mapping editor discovery and the query generation component.

Summary: You can use the SQL/XML query language to transform relational data into XML format. Since it is cumbersome to manually write SQL/XML queries, IBM® Rational® Data Architect simplifies this work by automatically generating SQL/XML queries based on graphically defined mappings. Get an introduction to the SQL/XML generation component of Rational Data Architect.

View more content in this series

Date: 07 Sep 2006
Level: Intermediate
PDF: A4 and Letter (1848 KB | 41 pages)Get Adobe® Reader®

Activity: 13693 views
Comments:

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.

About this tutorial

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

Objectives

After taking this tutorial, you should be able to use Rational Data Architect and its mapping editor to generate and run SQL/XML queries.

Prerequisites

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.)

System requirements

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).

Setup steps

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