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

In the last two parts we covered XPath and XQuery expressions. Both expressions are heavily used in DB2 SQL/XML functions. This part follows up the previous articles and is dedicated to SQL/XML functions. Most of the article is built around simple examples to illustrate how those functions can be used in certain scenarios. In later examples, we’ll reuse the XML document used in the previous articles as an example. For convenience’s sake, it’s included in the references section at the end of this article.

SQL/XML functions

We can divide the SQL/XML functions into several groups.

  • Publishing functions that are used to define or create XML documents within a SQL statement.
  • Functions to embed XPath and XQuery statements to query XML documents.
  • Functions to serialize and parse XML documents.
  • Schema validation functions.

The following table lists the SQL /XML Functions within the groups:

XML_Functions.jpg

 

Publishing functions

Publishing functions are used to define or create XML documents within a SQL statement. To create different elements of the document you are provided with various functions. The simple examples below illustrate how those functions are used. All examples reference the ARTICLES table using the following definition:

CREATE TABLE ARTICLES 
  (ID       INTEGER NOT NULL PRIMARY KEY,
   PROMOTED CHAR(1),
   TITLE    VARCHAR(128),
   TAG      VARCHAR(32),
   TEXT     CLOB(2M) );

The XMLELEMENT function is used in the following example to create an XML element that contains an article title. The XMLNASPACE declares the namespace. If there is a null value in the title column, the null value is returned:

SELECT XMLELEMENT(
         NAME "blog2017:article",
         XMLNAMESPACES('http://blog2016.cz' AS "blog2017"),
         A.TITLE OPTION NULL ON NULL ) AS "articles"
  FROM ARTICLES A;

Result:

<blog2017:article xmlns:blog2017="http://blog2016.cz">
   TITLE
</blog2017:article>

The XMLATTRIBUTES function enhances the statement from the previous example and adds article_id as an attribute of the article element:

SELECT XMLELEMENT(
         NAME "blog2017:article",
         XMLNAMESPACES('http://blog2017.cz' AS "blog2017"),
         XMLATTRIBUTES(A.ID as "article_id"),
         A.TITLE ) AS "articles"
  FROM ARTICLES A;

Result:

<blog2017:article xmlns:blog2017="http://blog2017.cz" article_id="1">
    TITLE
</blog2017:article>

In the example below, we generate an "article" element for each article. The article_id and the promoted flag are used as attributes. Two subelements are generated from the TITLE and TAG columns using the XMLFOREST function to populate their content:

SELECT XMLSERIALIZE( 
         XMLELEMENT(
           NAME "article",
           XMLATTRIBUTES(A.ID AS "article_id",
             A.PROMOTED AS "promoted" ),
           XMLFOREST(
             A.TITLE AS "title",
             A.TAG as "tag" ))  AS CLOB(100)) AS "articles"
  FROM ARTICLES A;

Result:

<article article_id="1" promoted="Y">
    <title>TITLE</title>
    <tag>TAG</tag>
</article>

The example below illustrates how to group articles by their tag. The 'tag' element is generated for each tag with its name as the attribute and nests all of the 'article' elements for articles that have the same tag. The articles are ordered by title name:

SELECT XMLSERIALIZE(
         XMLDOCUMENT(
           XMLELEMENT(
             NAME "tag",
               XMLATTRIBUTES ( A.TAG AS "name" ),
               XMLAGG(
                 XMLELEMENT(NAME "title", A.TITLE)
                 ORDER BY A.TITLE) )) AS CLOB(1M) ) AS "articles"
  FROM ARTICLES A
 GROUP BY A.TAG;

Result:

<tag name="TAG">
    <title>TITLE</title>
</tag>

Embed XQuery

These functions are used to query the XML data within SQL statements by embedding and executing XQuery stetments. 

XMLEXISTS?

The XMLEXISTS function is used to test the existence of the XML element(s).

In the simple example below, the statements return the number of blogs which contains at least one promoted article.

SELECT COUNT(*)            
  FROM BLOGS
 WHERE XMLEXISTS('/blog/articles/article[@promoted = "Y"]'
                  passing XMLDATA);

XMLMODIFY

This function is used to modify XML documents. Note that you can use XMLMODIFY only on the XML columns that were created in a table with multiple versioned tablespaces  (for example, UTS created on Db2 10 and further). Only a single updating expression for each XMLMODIFY call is allowed.

Consider the following example that uses the XMLMODIFY function to add a subtitle to each article:

UPDATE ORDERS 
   SET XML_DATA = XMLMODIFY('insert node $newnode after node
                             /blog/articles/article/title',
                             XMLPARSE('<subtitle></subtitle') as
                                 "newnode");

Now, think about updating the subtitle value for every article. You would need to use a single XMLMODIFY statement for every item. Coding this is quite cumbersome and calling the XMLMODIFY function several times implies overhead. This problem has been resolved with Db2 12. In Db2 12, XMLMODIFY allows more complex updating expressions within a single call. More on this topic will come shortly in a separate part dedicated to Db2 12 enhancements.

XMLQUERY

XQuery and XPath were briefly covered in Part 6. XMLQUERY is the function you use in the SQL statement to execute XQuery/XPath.

The following example of an XMLQUERY fucntion returns list of tags with the article titles ordered their corresponding tag value:

SELECT xmlquery('
for $t in fn:distinct-values($data/blog/articles/article/tag)
order by $t
return
<tag>
    <name>{$t}</name>
    <articles> {
       for $b in $data/blog/articles/article
       where $b[tag = $t]
       order by $b/title
       return $b/title
        }
    </articles>
</tag>
' passing XMLDATA as "data") FROM BLOGS;

Result:

<tag>
    <name>chicken</name>
    <articles>
        <title>Best Chicken Tikka Masala</title>
    </articles>
</tag>
<tag>
    <name>sweets</name>
    <articles>
        <title>Chocolate Ganache</title>
        <title>How to Make Besan Ladoo</title>
    </articles>
</tag>

XMLTABLE

This function allows you to create a relational Db2 table from the XML data. There are many scenarios where this functionality comes in very handy. Let’s ilustrate some of them using the examples below.

One common use of the XMLTABLE function is to split a huge XML document stream into smaller chunks of single XML documents. Consider the following example where the blog document is split into articles:

INSERT INTO ARTICLES
SELECT X.* FROM ARTICLES A,                                  
XMLTABLE('$b/blog/articles/article' passing A.XML_DATA as "b"
          COLUMNS "XML_ARTICLE"   XML PATH 'document{.}'   
) AS X              

In addition to shredding the XML stream, the XMLTABLE function can be used to extract data received from the XML document and store this information into a relation table alongside the XML document itself. The extracted portion of the data is then available for SQL queries.

INSERT INTO ARTICLES
SELECT X.* FROM ARTICLES A,     
XMLTABLE('$b/blog/articles/article' passing A.XML_DATA as "b"  
          COLUMNS "XML_ARTICLE"   XML PATH 'document{.}',
                  "ARTICLE_ID" INTEGER PATH ' XML PATH
                          '/blog/articles/article[@aid]',
                  "TITLE" VARCHAR(128) PATH
                          'articles/article/title'
) AS X 

In some situations you need to access XML data using SQL statements. This can be achieved by using the XMLTABLE function with VIEW.

CREATE VIEW ARTICLES_VIEW (ARTICLE_ID, TITLE) AS    
SELECT X.*  FROM ARTICLES A,                                     
XMLTABLE('$b/blog/articles/article' passing A.XML_DATA as "b" 
          COLUMNS "ARTICLE_ID" INTEGER PATH ' XML PATH
                     '/blog/articles/article[@aid]',
                  "TITLE" VARCHAR(128) PATH
                     'articles/article/title'
) AS X 

Serialize/Parse

It is recommended that you avoid using the XMLPARSE function with character string input directly in applications. If the encoding scheme between the external input and the XML declaration does not match, it might cause unwanted conversion. To avoid this situation, use host variables.

The following example illustrates the use of the XMLPARSE function inside a simple INSERT statement (the host_variable contains XML document):

INSERT INTO ARTICLES (ID, XML_DATA) VALUES(18051983,
XMLPARSE(DOCUMENT :host_variable));

Schema validation

Generally, there are two ways to validate an XML document against the XML schema: Automatically, whenever the XML document is inserted or updated; or manually, on request by using the DSN_XMLVALIDATE function. You can read more about schema validation in Part 4 of this short series.

DSN_XMLVALIDATE

If the XML type modifier is not used when creating or altering the XML column, the DSN_XMLVALIDATE function can be used to request schema validation. The XML schema is identified by either the registered schema ID, or the namespace URI and location URI.

Consider the following example to request schema validation on insert:

INSERT INTO BLOGS VALUES(
    DSN_XMLVALIDATE('<root>my xml document</root>',
                    'www.blogs.cz/Blog',
                    'www.blogs.cz/Blog2016.xsd'));

XMLXSROBJECTID

This function returns the registered XML schema identifier of the XML schema that was used to validate the XML document, or 0 if the document has not been validated.

The following example shows how to validate all blog XML documents which have not been validated against the 'SYSXSR.BLOG2016' schema.

UPDATE BLOGS
   SET blog = DSN_XMLVALIDATE(blog, 'SYSXSR.BLOG2016')
 WHERE XMLXSROBJECTID(blog) = 0;

Conclusion

After reading this part of our short series introducing XML in Db2 11 for zOS, you should have a basic idea of what the SQL/XML functions are and how they can be used in various scenarios. The information we provided however is very limited, and we covered just the basic use. Refer to the SQL Reference section for more detailed information on the syntax for the functions. One reference to Db2 12 has been mentioned in relation to XMLMODIFY function. As Db2 12 has brought a couple of interesting enhancements to XML, let’s review them in the next part.  

References

Example used in the article

<?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>
   <article aid="00002" promoted="NO">
     <title>
       How to Make Besan Ladoo
      </title>
     <tag>
       sweets
      </tag>
   </article>
   <article aid="00003" promoted="NO">
     <title>
       Chocolate Ganache  
     </title>
     <tag>
       sweets
     </tag>
   </article>
 </articles>
</blog>

XQuery_Example1.jpg

Color legend:

  • Gray – document node
  • Dark blue – element
  • Orange – attribute
  • Light blue – element value
Recent Stories
Lateral join

Programmers, Take a Look at Db2 and SQL!

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