Browse DevX
Sign up for e-mail newsletters from DevX


Learn to Use XML with DB2  : Page 2

If you need to store XML data in DB2, translate XML to relational data, or shred relational data into XML, the XML Extender for DB2 is an essential utility. But it's not always straightforward. Find out what it can do and what problems to expect.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 MYROWS FROM DEPARTMENT MYROWS -------------------------------------------------------------------- <MyRow> <column name="DEPTNO">A00</column> <column name="DEPTNAME">SPIFFY COMPUTER SERVICE DIV.</column> <column name="MGRNO">000010</column> </MyRow> <MyRow> <column name="DEPTNO">B01</column> <column name="DEPTNAME">PLANNING</column> <column name="MGRNO">000020</column> </MyRow>

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

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date