Introduction to XML in Db2 for z/OS – Part 8 – pureXML enhancements in Db2 12

It has been some time since the first part of this short series came out. The previous post reviewed SQL/XML functions and concluded our short series of basic introductions to XML support in Db2 for z/OS. Since that time, besides Db2 losing its capital B, a new version of Db2 12 came out that introduced a couple of interesting enhancements to the SQL/XML functions. We are going to discuss them in this last post of the whole series.

XMLMODIFY

This function is used to modify XML documents. Aside the fact it can be used only in multiple versioned tablespaces, the biggest limitation of this function is that you can specify only a single basic updating expression (insert, replace, delete node). So, for example, if you need to modify the price for every item in your order list, you will need an XMLMODIFY call for each item.

This limitation has been removed in Db2 12. XMLMODIFY function accepts more than a basic updating expression. Updating expressions can contain:

  • A basic updating expression
  • A sequence expression
  • A FLWOR that returns an updating expression
  • A conditional expression that contains an updating expression in its then/else clause

The following examples illustrate the new possibilities that are available to updating expressions (a basic updating expression has been covered in the previous post). For the last two examples, we will use a generic item with price.

A sequence expression:

// Change the blog url and add old_url as a new item
UPDATE BLOGS SET XMLDATA = xmlmodify(
          'replace node /blog/url with $newnode1,
           insert node $newnode2 after /blog/url',
           XMLPARSE('<url>iloita.com</url>') as "newnode1",
           XMLPARSE('<old_url>cookdiary.com</old_url>') as "newnode2"); 

A FLWOR that returns an updating expression:

// Apply the sale 
UPDATE ORDERS SET XMLDATA = xmlmodify(          
  'for $item in /order/item                     
   let $p := $item/price
   return
     replace value of node $p with $p - 100 

A conditional expression that contains updating expression in its then/else clause:

// Appply the sale conditionally
UPDATE ORDERS SET XMLDATA = xmlmodify(          
  'for $item in /order/item                     
   let $p := $item/price
   let $type := $item/type
   where $p > 10000
   return
     (if ($type = 'X') then
         replace value of node $p with $p – 100
      else
         replace value of node $p with $p – 200)'
);

 

XMLTABLE

This function allows you to create a relational Db2 table from the XML data. The last post discussed the various uses of the XMLTABLE function. Db2 12 focused on one use case, which has not been discussed, and significantly impoved performance. Let’s take a look at the use case and the performance improvements made to XMLTABLE in Db2 12.

Asume you have an XML document structure where you represent attributes of an object as name-value tuples as ilustrated below:

<item>
  <blog name='id' value='3011975'/>
   <blog name='title' value='Cook Diary'/>
   <blog name='author' value='Iloita'/>
     ...
</item> 

You want to decompose these attributes as relational data, so you use the XMLTABLE function:  

SELECT X.* FROM BLOGS B,                                        
XMLTABLE('$b/blogs/item' passing B.XML_DATA as "b"  
      COLUMNS "ID"     INT          PATH '../blog[@name="id"]/@value',
              "TITLE"  VARCHAR(128) PATH '../blog[@name="title"]/@value',
              "ARTIST" VARCHAR(128) PATH '../blog[@name="author"]/@value',
         ...            

All the XPath expressions in the above example share the same pattern, but differ in predicate value. In previous versions of Db2, the XPath was evaluated for each column definition. Now in Db2 12, an XPath expression is evaluated only once and this evaluation is then used in all the subsequent column definitions. This improvement is a significant performance benefit for this use case. 

XSLTRANSFORM

The XSLTRANFORM function is used to transform XML documents into other formats such as HTML, plain text, different XML schemas, and so on. XSL stands for eXtensible Stylesheet Language and it is a styling language for XML. For more information about XSL, see https://www.w3.org/Style/XSL/. Db2 supports the V1.0 Recommendation.

You can convert part of an XML document or the whole XML document. You can also select or rearrange the data with the XPath query language and the built-in functions of XSL. The XSLTRANSFORM function requires Java. 

XSLTRANSFORM has three parameters:

xml_parameters7.jpg

The following example will illustrate the transformation of an XML document into HTML using the XSLTRANSFORM function.

The XML document (the one used in most of the examples in this series):

<?xml version= "1.0" ?> 
<blog id="3011975">
  <title>Cook Diary</title>
  <author>Iloita</author>
  <url>cookdiary.com</url>
  <articles>
    <article aid="00001" promoted="YES">
      <title>
        Best Chicken Tikka Masala
      </title>
      <tag>
        chicken
      </tag>
    </article>
    ...
  </articles>
</blog> 

The XSLT code to transform the XML document:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
   xmlns="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" encoding="UTF-8"/>
<xsl:template match="/blog">
    <html>
      <head>
        <title>
          <xsl:value-of select="title"/>
        </title>
      </head>
      <body>
        <h1>Articles</h1>
        <ul>
          <xsl:apply-templates select=" /blog/articles/article">
            <xsl:sort select="title"/>
          </xsl:apply-templates>
        </ul>
      </body>
    </html>
  </xsl:template>
  <xsl:template match=" /blog/articles/article">
    <li>
      <xsl:value-of select="title"/>
    </li>
  </xsl:template>
</xsl:stylesheet>

 The transformed document in HTML:

<?xml version="1.0" encoding="UTF-8"?>
   <head>
      <title>Cook Diary</title>
   </head>
   <body>
      <h1>Articles</h1>
      <ul>
         <li>Best Chicken Tikka Masala</li>
         <li>Chocolate Ganache</li>
         <li>How to Make Besan Ladoo</li>
      </ul>
   </body>
</html> 

Conclusion

This brief overview of the new enhancements to D2b 12 concludes our introduction to XML support in Db2 11 for z/OS. This introduction included the basics of XML support, from XML document manipulation through internals of the pureXML storage, to the rudiments of the XPath and XQuery expressions used in SQL/XML functions. If you have further interest in this topic, refer to the references below for more advanced information.

References

 

1 Like
Recent Stories
Introduction to XML in Db2 for z/OS – Part 8 – pureXML enhancements in Db2 12

Introduction to XML in DB2 11 for z/OS – Part 7 – SQL/XML functions

Introduction to Db2 for z/OS System Profiles