Chapter 8. Overview of DB2 XML Extender 129
“Shredding methodology” on page 155, and Chapter 11, “Composing methodology” on
page 181.
The XML Collection method allows you to compose XML documents from existing DB2 data
or decompose XML documents and store untagged element or attribute values into DB2
tables. This method is useful for business-to-business (B2B) or electronic data interchange
(EDI) applications.
Consider the following additional points when writing an application that uses the XML
Collection method:
򐂰 You can use WebSphere Studio to help you build DADs to define documents that you want
to publish.
򐂰 More than one DAD can be defined to describe the same DB2 RDB data to produce
customized XML documents based on particular needs.
򐂰 RDB node mapping DAD to compose or decompose XML documents is limited to 30
tables.
򐂰 Null data in your columns may result in XML documents generated with empty elements or
attributes. If empty elements or attributes are not desired, they can be stripped using the
Transform UDFs.
For more information and complete descriptions about this method, see IBM DB2 Universal
Database for i5/OS XML Extender Administration and Programming, SC18-9179.
8.4 SQL mapping DAD composition example
With the two different types of DADs, the SQL mapping DAD is quicker to create. However, an
SQL mapping DAD can only by used when composing XML documents, and not when
decomposing them. The SQL mapping DAD is based on an SQL statement. After the SQL
statement is written that provides the data to be composed into an XML document, the rest of
the DAD maps the fields from the select statement to the XML document.
8.4.1 SQL mapping DAD methodology
To illustrate how we can compose using SQL mapping DAD, we show how to go from the
CountryXXX database to the County Sales XML document, which is step 3 (Figure 8-10) of
the scenario that we introduced in Chapter 2, “Scenario overview” on page 21. The goal of
the Country Sales XML document is to list a summary of sales by brand per region for a
particular country and date.
130 The Ins and Outs of XML and DB2 for i5/OS
Figure 8-10 Scenario step 3
The database model, in Figure 8-11, shows that each country’s data is kept in a separate
schema. A Sales table lists all of the sales with a foreign key of StoreId. StoreId allows us to
link from the Sales table to the Stores table. However, the goal is to summarize by region. The
Stores table contains a RegionID for each store that will allow us to link to the Region table
and group the sales by regions.
Figure 8-11 CountryXXX schema layout
Next we look at the XML document (shown in Example 8-16) that we are generating. For each
brand per region, we need to write the total of the sales per a certain currency. This means
that for each region and brand, we will have a repeating set of Sales and Returns elements for
each currency with the total values.
Store
Database
Country
Database
Corporate
Staging
Database
Corporate
Sales
Database
Store Sales
XML Documents
Country Sales
XML Documents
Corporate Sales
XML Document
Store Sales
Archive
Database
1 1B
2
3
4
5
6
Regions
PK RegionId
RegionName
CountryName
Stores
PK StoreId
FK1 RegionId
StoreName
Street
City
PostalCode
CountryXXX
Sales
FK1 StoreId
BrandName
SalesDate
Type
Currency
Amount

Get The Ins and Outs of XML and DB2 for i5/OS now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.