WebSphere DataStage XML

Figure 7. XML Meta Data importer













The XML Meta Data Importer can import XML structure from either XML sample or XSD files. Open the target.xml file in the XML Meta Data Importer. Once opened, the XML file is mapped into tabular structure. As depicted in Figure 7, select the TEXT fields of the five data elements (marked in red circle) as the columns of table definition to extract text content of the XML elements. The "Description" field (marked in red box of Figure 7) contains the XPath, which indicates the corresponding position of a column in the XML document. In this sample, it is assumed that every contact record has a unique e-mail address that is used as the "Key" column. Save the table definition in the "PlugIn\sample" category, named as "target_xml".

As of now, three table definitions (in red circle of Figure 8) have been imported into the category successfully.
Figure 8. Imported table definitions











Step 4. Set up DB2 stages with a transformer to provide joined data

With the table definitions ready in repository, it is time to set up the data source for XML output.
There are two options to extract the data:

Use the SQL, as shown in Listing 3, in one DB2 stage
Use the transformer to join the data from multiple stages
This sample follows the second option:

Listing 3. Query for the data extraction

SELECT cnt.CUST_NUM,c.CUST_NAME,cnt.F_NAME,cnt.L_NAME,cnt.EMAIL
FROM S_CONTACT cnt
JOIN S_CUST c ON cnt.CUST_NUM=c.CUST_NUM ;

Figure 9. Results of query from Listing 3

To achieve the same output shown in Figure 9 in DataStage, two DB2/UDB API stages (in Database category of the Palette) and a Transformer stage (in Processing category of the Palette) are adopted. These stages are added to a new job diagram and named as follows:

The two DB2 stages: "CONTACT" and "CUST"
The Transformer: "JOIN_CUST_CNT"
Connect "CONTACT" and "CUST" to the "JOIN_CUST_CNT" using DSLink3 and DSLink4 respectively. (See Figure 10.)

Figure 10. Job diagram

As a database developer, you usually join the customer table to the contact table and treat the customer as the main table. But in this sample, the contact table is the main one, indicated by the solid arrow DSLink3. The transformer carries out a lookup action in non-primary tables for records of the primary table. For a single record of the primary table, only one record will be retrieved from the non-primary tables.

Note: Only ODBC and UniVerse stages support multi-row lookup.

"CONTACT" stage configuration

Double-click on the "CONTACT" stage to configure its properties. On the "Stage" page, fill in the DB2 connection information: database name, username, and password. Switch to the "Output" page, and click on the columns tab. The column information includes column name, derivation, type, and so on. Columns can be edited directly or loaded from an existing table definition. Here, use the imported CHENLI.S_CONTACT table definition to load the columns for this stage. Click the Load button, select CHENLI.S_CONTACT in the pop-up table definition window, press OK, then select all the columns of the table definition in the successive column selection window. When the columns are loaded, the result looks like Figure 11: