devxlogo

Learn to Use XML with DB2

Learn to Use XML with DB2

Why Trust Us

y employer has just spun off our product testing facility into an independent organization, able to seek other clients. They will have their own, independent data systems but background data must be sent and test results must be retrieved to our core system with minimal effort.

XML is a logical choice for the exchange and is supported by the group developing for the new company.

Now I need to export data and accept it back in XML; I don’t have full control over the data formats, and I have precious little time.

XML Extender to the rescue!


I need to exchange data between an existing DB2 database and another party, using XML, with minimal development.


Use DB2’s XML Extender to translate between XML and relational data in DB2.

IBM delivers DB2’s XML support in the form of an “Extender”?a kind of privileged add-on that integrates closely with the core database. The Extender includes indexing capabilities and adds new data types and functions.

XML Extender for DB2 v7.2 is available for download. DB2 v8.1 includes the XML Extender in the regular installation kits, and it installs simply as an optional component. (See the “Getting Started” sidebar for more information on installing the XML Extender.)

What You Need
DB2 v8.1, or v7.2 with XML Extender. Based on Window 2000 install with DB2 v8.1.

There are tutorials for using the Extender in online documentation and on Web sites such as IBM’s Developer Domain. I won’t attempt to reproduce or better them, but I can give you a quick rundown on what to expect from DB2 with XML.

Before you begin working on any DB2/XML solution, you need to figure out what your real requirements are.

Table 1: XML vs. Relational Options in DB2

Requirement

Solutions

Translate relational data to XML

You can:

  • Use simple functions
  • Use a “SQL Collection” to map a query to an XML document structure
  • Use an “RDB Node Collection” to map a number of tables to an XML document structure (this also allows XML import)
  • Alternately, you could ignore DB2’s capability and use Visual Basic or VB Script (see the article, “Generating XML from ADO Recordsets“).

“Shred” (translate) XML to relational data

Compose Yourself (Translating to XML)
In the first instance, we need to export some background data. DB2 lets us generate XML documents as a whole or in pieces.

In Pieces
Some new functions in DB2 v8.1 make it very easy to produce fragments of XML. (These are not complete XML documents.) For example, using DB2’s sample database and the REC2XML function you could create the following XML fragments.

select rec2xml(1.0, 'COLATTVAL','MyRow',DEPTNO, DEPTNAME, MGRNO) AS MYROWSFROM DEPARTMENTMYROWS--------------------------------------------------------------------A00SPIFFY COMPUTER SERVICE DIV.000010B01PLANNING000020

Similarly, there are functions to return single elements (XMLELEMENT), group elements as XML attributes (XMLATTRIBUTES), and aggregate elements in a hierarchy (XMLAGG). Listing 1 is an example of aggregated data from DB2 functions, copied from the online documentation.

Whole Documents, with Help from DAD
We need more than just the elements, we need whole documents, regularly. The document will be based directly on one or more tables, and here we have a bridge to cross, as XML and relational databases are different, and best solve different problems. In the world of XML, the obvious solution is to write a kind of document that describes the specific relationships between XML and relational data. In DB2, IBM’s answer is the Document Access Definition (DAD)?an XML document that is understood by DB2. A DAD describes:

  • Which DB2 tables hold the data
  • The mapping method: RDB Node Collection, SQL Collection, or XML Column (see Table 1)
  • Whether to validate XML documents with a DTD before storing or translating the contents (the DTD can also provide default values for XML optional attributes)
  • Which DTD to use for validation and translation
  • Other details depending on the mapping method

DB2 documentation describes how to write a DAD, but getting the content right is tedious. Thankfully, there are some graphical tools for the task (see the “Tools for the Job” sidebar).

Reviewing our options from Table 1, we will consider the two XML “Collection” types:

SQL Collection Mapping is the simpler choice. It only works when you can collect all relevant data with a single SELECT statement, which is placed in the DAD. If multiple tables are involved you should use primary-foreign key relationships.

RDB_Node Collection Mapping is more flexible. It allows node definitions for multiple tables, optional columns, and optional conditions. All involved tables must have primary keys. This approach does not require SQL in the DAD. It does allow imports as well as exports, and you can create a field on each table to preserve the order of data when it appears in XML.

Listing 2 shows a sample DAD.

Once you have defined a DAD, creating XML is as easy as calling a single stored procedure, dxxGenXML:

  dxxGenXML(CLOB(100K)     DAD,       /* input */          char(resultTabName) resultTabName, /* input */          integer        overrideType  /* input */          varchar(1024)  override,     /* input */          integer        maxRows,      /* input */          integer        numRows,      /* output */          long           returnCode,   /* output */          varchar(1024)  returnMsg)    /* output */

Author’s Note: Overrides allow changes in the “WHERE” condition for selecting data to be exported.

dxxGenXML generates an XML document according to the given DAD and places the result in a table you specify when calling the routine.dxxGenXMLClob is identical except that it returns the XML directly as a Character Large Object.

In DB2 v8.1, you can now call a stored procedure directly from a DB2 command prompt. For added convenience, the extender provides an external command-line program to call these procedures. (See dxxAdm in the “Tools for the Job” sidebar.)

If you will be composing or decomposing XML documents frequently, it is worth your time to create a named collection using the dxxEnableCollection stored procedure. This procedure stores the DAD within the database, and you can then refer to it by name using dxxRetrieveXML and dxxRetrieveXMLClob, which is otherwise identical to dxxGenXML.

dxxRetrieveXML(char(collectionName) collectionName,   /* input */               char(resultTabName) resultTabName,    /* input */               integer        overrideType,     /* input */               varchar_value  override,         /* input */               integer        maxRows,          /* input */               integer        numRows,          /* output */               long           returnCode,       /* output */               varchar(1024)  returnMsg)        /* output */

You can call these procedures from your own code or by using the dxxAdm tool. Also see some of the “glue” utilities in “Filling in the Gaps.”

Taking It In (XML into DB2)
Once you’ve exported our data, you can prepare to receive some in return. Reviewing the options from Table 1, you can either:

“Shred” the XML to relational data

Filling in the Gaps
A range of other functions are available to round out DB2’s support for XML. Some of these are operations required in almost any XML environment, while others form the “glue” to more easily use the major functions.

Validation
The XML Extender provides two functions for validating XML documents, depending on your choice of schema or DTD validation:

I’ve mentioned that DTDs are required for some DADs. Schemas are more versatile than DTDs in defining and validating XML documents, and DB2 allows both types to be performed explicitly using either the SVALIDATE(doc, schema) or DVALIDATE(doc, dtd) functions.

Shredding and storing routines, however, will only validate automatically using DTDs.

For explicit validation, the XML document can be provided as a CLOB or as a file system path described in a character string. The schema or DTD can be implied from the XML document, provided as a CLOB, or specified as a file path.

Perform Transformations Using XSL
Transformations demonstrate one of XML’s powerful features?the ability to convert XML documents into other formats, by writing a formula. The same formula can be used under a range of environments, and DB2 is no exception.

The extender function XSLTransformToFile takes an XML document as a CLOB, an XSL stylesheet as a file or a CLOB, and stores the result of the transform as a file. You can either specify a full file name or just a location, and the system can generate a file name. DB2 Extender can perform validation before running the transform.

Read and Write Files
To pass XML between DB2 and other systems, there is again a choice:

  • You can write an application to pass CLOBs to and from the database.
  • DB2 Extender provides these functions, which have self-descriptive names: XMLVarcharFromFile, XMLCLOBFromFile, XMLFileFromVarchar, XMLFileFromCLOB
  • DB2 v8.1 also provides a range of functions to directly link to IBM’s Websphere MQ messaging platform, and many of these functions integrate XML support.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More
Linkedin Twitter

©2024 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.

Sitemap — Privacy Policy