WebSphere DataStage XML

Part I. Publishing XML documents from table data

Publishing XML documents based on the existing table data is a common scenario. Sometimes, relational tables or sequential files are required to be transformed to XML hierarchical structures, such as XML documents or XML chunks. In this case, the XML output stage can be used to generate XML output. It uses XPath expressions to map input table fields to certain positions in the output documents.

Sample 1. Generate XML files based on two tables using XML output stage
Figure 1. Job diagram of XML publishing

Overview of sample 1

In sample 1, customer data and contact data has been extracted from two corresponding DB2 tables respectively, as shown in Figure 1. The transformer is used to replace the complex SQL, integrate the data, and feed the joined data to the XML output stage through DSLink6. XML output stage then generates the XML results and saves them to the file system. Figure 1 briefly describes the whole application demo.

The general steps for deployment are:

Define and deploy DB2 tables
Prepare XML structure
Import DB2 table and XML table definitions
Set up DB2 stages with a transformer to provide joined data
Set up XML output stage to generate XML document
Compile and run
Let's examine these steps in detail:

Step 1. Define and deploy DB2 tables

Deploy these tables into the sample database using the DDL shown in Listing 1 on the local DB2 server, and load some sample data, as shown in figures 2.1 and 2.2:

Listing 1. DDL for customer and contact table

--customer table
CREATE TABLE S_CUST
(
CUST_NUM CHARACTER(10) PRIMARY KEY,
CUST_NAME VARCHAR(100) NOT NULL
);
--contact table
CREATE TABLE S_CONTACT
(
CNT_NUM INTEGER
GENERATED ALWAYS AS IDENTITY( START WITH 1, INCREMENT BY 1 )
PRIMARY KEY,
CUST_NUM CHARACTER(10) NOT NULL,
F_NAME VARCHAR(50) NOT NULL,
L_NAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(100) NOT NULL
);