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 */

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. Previous Next Share the Post: Different Types of Data Models Explained with Examples June 7, 2023 In the modern world, data is everything and everywhere. With so much access to technology, data has become a valuable resource for any business. Albeit a complex one. Data is Revolutionizing Search: A Glimpse Into Google’s Generative Experience June 6, 2023 Google is revolutionizing the search experience as we know it with its latest generative experience. No longer will you be bound by the limitations of traditional keyword searching. Now, you 10 Productivity Hacks to Supercharge Your Business in 2023 June 5, 2023 Picture this: your team working seamlessly, completing tasks efficiently, and achieving goals with ease. Sounds like too good to be true? Not at all! With our productivity hacks, you can GM Creates Open Source uProtocol and Invites Automakers to Adopt It: Revolutionizing Automotive Software Development. June 2, 2023 General Motors (GM) recently announced its entry into the Eclipse Foundation. The Eclipse Foundation is a prominent open-source software foundation. In addition, GMC announced its contribution of “uProtocol” to facilitate What is Metadata? June 1, 2023 What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular What We Should Expect from Cell Phone Tech in the Near Future May 31, 2023 The earliest cell phones included boxy designs full of buttons and antennas, and they only made calls. Needless to say, we’ve come a long way from those classic brick phones The Best Mechanical Keyboards For Programmers: Where To Find Them May 29, 2023 When it comes to programming, a good mechanical keyboard can make all the difference. Naturally, you would want one of the best mechanical keyboards for programmers. But with so many The Digital Panopticon: Is Big Brother Always Watching Us Online? May 26, 2023 In the age of digital transformation, the internet has become a ubiquitous part of our lives. From socializing, shopping, and learning to more sensitive activities such as banking and healthcare, Embracing Change: How AI Is Revolutionizing the Developer’s Role May 25, 2023 The world of software development is changing drastically with the introduction of Artificial Intelligence and Machine Learning technologies. In the past, software developers were in charge of the entire development The Benefits of Using XDR Solutions May 24, 2023 Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved How AI is Revolutionizing Fraud Detection May 23, 2023 Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across Companies Leading AI Innovation in 2023 May 22, 2023 Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several Show More