DB2 Basics: An introduction to the SQL/XML publishing functions

Document options Document options requiring JavaScript are not displayed Discuss My developerWorks needs you! Connect to your technical community Rate this page Help us improve this content

Level: Introductory

Roman Melnyk, Ph.D. (roman_b_melnyk@hotmail.com), Senior Member of the DB2 Information Development team, IBM

03 Nov 2005

You can use the SQL/XML publishing functions to produce a tagged XML document from relational data. IBM® DB2® Universal Database™ (DB2 UDB) includes built-in SQL/XML publishing functions that make it easy to publish XML documents using data that is stored in a DB2 database. This article introduces you to the SQL/XML publishing functions, and shows you, by way of working examples, how to use these functions.

XML is eXtensible Markup Language, a universal data format for publishing, storing, and sharing structured documents. XML can be used to exchange data between computers. Marked-up text can be classified by XML node type, such as "document," "element," "attribute," or "text," among others. "Namespaces" are associated with the names of nodes, such as elements and attributes.

SQL/XML is an extension of the SQL language standard (ANSI/ISO) that includes XML publishing functions for converting relational data into XML. IBM DB2 Universal Database for Linux®, UNIX®, and Windows® (DB2 UDB) includes built-in SQL/XML publishing functions that make it easy to publish DB2 UDB data in an XML document. These functions let you create tagged XML documents in character large objects (of type CLOB, one of the DB2 UDB built-in data types). You can use a SELECT statement to assemble the required XML nodes, and capture the marked-up text by directing the output to a file. You can also use an INSERT statement to write the generated text to a table.

SQL/XML publishing functions

DB2 UDB Version 8.2 supports seven SQL/XML publishing functions:

• XMLSERIALIZE
• XMLELEMENT
• XMLFOREST
• XMLATTRIBUTES
• XMLCONCAT
• XMLAGG
• XMLNAMESPACES

The following sections introduce you to each of these publishing functions. Although the types of XML elements and attributes that you can construct with these functions is virtually unlimited, the examples in this article focus on XML table elements and attributes. The examples require a connection to the SAMPLE database; if you don’t have the SAMPLE database created on your system, you can create it by entering the db2sampl command from any command prompt.

XMLSERIALIZE

The XMLSERIALIZE function converts an XML value into a string that is stored as a CHAR, VARCHAR, or CLOB value. XMLSERIALIZE is a cast function that returns a value of type CHAR, VARCHAR, or CLOB. The argument must be an expression of data type XML. Note that the XML data type is an internal representation of XML that can only be used as input to functions (such as XMLSERIALIZE) that accept this data type as input. XML is a transient data type that cannot be stored in the database.

All of the examples in this article make use of the XMLSERIALIZE function. Each of the other functions return XML values that are, in turn, passed to the XMLSERIALIZE function.

XMLELEMENT

The XMLELEMENT function constructs a named XML element node. XMLELEMENT is a scalar function that returns a value of type XML. The arguments include an element name, optional namespace declarations, optional attributes, and zero or more expressions that make up the element's content. The XMLELEMENT function can be nested.

Listing 1 shows an example of using the XMLELEMENT function in a nested context to produce XML table data from data stored in the EMPLOYEE and DEPARTMENT tables of the SAMPLE database. Several calls to the function are used to produce table cell (<td>) elements nested within table row (<tr>) elements. Listing 1 also shows the output from this query. Because the output strings are quite long, each string in this example appears on two lines. Table 1 shows what the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr",
      xmlelement(name "td", e.empno),
      xmlelement(name "td", e.firstnme),
      xmlelement(name "td", e.lastname),
      xmlelement(name "td", e.phoneno),
      xmlelement(name "td", d.deptno),
      xmlelement(name "td", substr(d.deptname, 1, 24))
  )
  as clob(120)
) as "result"
from employee e, department d
where e.workdept = d.deptno and year(hiredate) < 1970;


<tr><td>000010</td><td>CHRISTINE</td><td>HAAS</td><td>3978</td>
  <td>A00</td><td>SPIFFY COMPUTER SERVICE </td></tr>       
<tr><td>000050</td><td>JOHN</td><td>GEYER</td><td>6789</td>
  <td>E01</td><td>SUPPORT SERVICES        </td></tr>           
<tr><td>000110</td><td>VINCENZO</td><td>LUCCHESSI</td><td>3490</td>
  <td>A00</td><td>SPIFFY COMPUTER SERVICE </td></tr>   
<tr><td>000120</td><td>SEAN</td><td>O'CONNELL</td><td>2167</td>
  <td>A00</td><td>SPIFFY COMPUTER SERVICE </td></tr>       
<tr><td>000200</td><td>DAVID</td><td>BROWN</td><td>4501</td>
  <td>D11</td><td>MANUFACTURING SYSTEMS   </td></tr>          
<tr><td>000220</td><td>JENNIFER</td><td>LUTZ</td><td>0672</td>
  <td>D11</td><td>MANUFACTURING SYSTEMS   </td></tr>        
<tr><td>000230</td><td>JAMES</td><td>JEFFERSON</td><td>2094</td>
  <td>D21</td><td>ADMINISTRATION SYSTEMS  </td></tr>      
<tr><td>000250</td><td>DANIEL</td><td>SMITH</td><td>0961</td>
  <td>D21</td><td>ADMINISTRATION SYSTEMS  </td></tr>         
<tr><td>000280</td><td>ETHEL</td><td>SCHNEIDER</td><td>8997</td>
  <td>E11</td><td>OPERATIONS              </td></tr>      
<tr><td>000310</td><td>MAUDE</td><td>SETRIGHT</td><td>3332</td>
  <td>E11</td><td>OPERATIONS              </td></tr>       
<tr><td>000320</td><td>RAMLAL</td><td>MEHTA</td><td>9990</td>
  <td>E21</td><td>SOFTWARE SUPPORT        </td></tr>         
<tr><td>000340</td><td>JASON</td><td>GOUNOT</td><td>5698</td>
  <td>E21</td><td>SOFTWARE SUPPORT        </td></tr>         


connect reset

XMLFOREST

The XMLFOREST function constructs a sequence (forest) of XML element nodes. XMLFOREST is a scalar function that returns a value with the same internal XML data type as the arguments.

Listing 2 shows an example of using the XMLFOREST function nested within an XMLELEMENT function to produce XML table data. A single call to the XMLFOREST function is used to produce table cell (<td>) elements nested within table row (<tr>) elements. Listing 2 also shows the first record returned by this query, again appearing on two lines. Table 2 shows what the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr",
      xmlforest(
        e.empno as "td",
        e.firstnme as "td",
        e.lastname as "td",
        e.phoneno as "td",
        d.deptno as "td",
        d.deptname as "td"
      )
  )
  as clob(144)
) as "result"
from employee e, department d
where e.workdept = d.deptno and year(hiredate) < 1970;


<tr><td>000010</td><td>CHRISTINE</td><td>HAAS</td><td>3978</td>
  <td>A00</td><td>SPIFFY COMPUTER SERVICE DIV.</td></tr>       
...


connect reset

XMLATTRIBUTES

The XMLATTRIBUTES function constructs one or more XML attribute nodes for an XML element node. XMLATTRIBUTES is a scalar function that returns a value of type XML.

Listing 3 shows an example of using the XMLATTRIBUTES function in conjunction with the XMLELEMENT function to produce XML table data. In this case, three of the generated table cell (<td>) elements receive a column alignment attribute ('align="center"'). Listing 3 also shows the first record returned by this query, again appearing on two lines. Table 3 shows what some of the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr",
      xmlelement(name "td", xmlattributes('center' as "align"), e.empno),
      xmlelement(name "td", e.firstnme),
      xmlelement(name "td", e.lastname),
      xmlelement(name "td", xmlattributes('center' as "align"), e.phoneno),
      xmlelement(name "td", xmlattributes('center' as "align"), d.deptno),
      xmlelement(name "td", substr(d.deptname, 1, 24))
  )
  as clob(180)
) as "result"
from employee e, department d
where e.workdept = d.deptno and year(hiredate) < 1970;


<tr><td align="center">000010</td><td>CHRISTINE</td><td>HAAS</td>
  <td align="center">3978</td><td align="center">A00</td>
  <td>SPIFFY COMPUTER SERVICE </td></tr>                      
...


connect reset

In this example, the XMLELEMENT function is calling the XMLATTRIBUTES function to define a column alignment attribute for three output columns. Table 3 shows the serialized output.

XMLCONCAT

The XMLCONCAT function concatenates two or more XML values (expressions of data type XML). XMLCONCAT is a scalar function that returns a value with the same internal XML data type as the arguments.

Listing 4 shows an example of using the XMLCONCAT function along with the XMLELEMENT function to produce XML table data. In this case, two generated table cell (<td>) elements are concatenated. Listing 4 also shows the first record returned by this query. Table 4 shows what some of the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr", xmlconcat(
      xmlelement(name "td", firstnme),
      xmlelement(name "td", lastname)
    )
  )
  as clob(48)
) as "result"
from employee
order by lastname;


<tr><td>BRUCE</td><td>ADAMSON</td></tr>         
...


connect reset

In this example, the XMLELEMENT function is calling the XMLCONCAT function to concatenate a pair of nested XML element nodes. Table 4 shows the serialized output.

XMLAGG

The XMLAGG function aggregates XML values as sequence items in the resulting XML value. XMLAGG is an aggregate (column) function.

Listing 5 shows an example of using the XMLAGG function along with the XMLELEMENT function to produce XML table data. In this case, three generated table cell (<td>) elements are aggregated for each instance of a table row (<tr>) element. Listing 5 also shows the output from this query. Table 5 shows what the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr", xmlattributes(workdept as "id"),
      xmlagg(
        xmlelement(name "td", lastname) order by lastname
      )
  )
  as clob(72)
) as "result"
from employee
group by workdept having count(*) = 3;


<tr id="A00"><td>HAAS</td><td>LUCCHESSI</td><td>O'CONNELL</td></tr>     
<tr id="C01"><td>KWAN</td><td>NICHOLLS</td><td>QUINTANA</td></tr>       


connect reset

In this example, the XMLELEMENT function is calling the XMLAGG function to aggregate nested XML element nodes. Table 5 shows the serialized output.

XMLNAMESPACES

The XMLNAMESPACES function constructs XML namespace declarations from the arguments. The declarations are in the scope of the elements that are generated by the XMLELEMENT and XMLFOREST functions. XMLNAMESPACES is a scalar function that returns a value of type XML.

An XML namespace can be used to qualify an element or an attribute name by associating it with a specific universal resource identifier (URI). It is a collection of names that are used in XML documents as element types and attribute names. XML namespaces ensure that elements and attributes have universal names whose scope extends beyond a given document, and are designed to prevent misinterpretation when applications are processing similar tags and attributes.

Listing 6 shows an example of using the XMLNAMESPACES function along with the XMLELEMENT function. In this case, an XML element node named 'emp', nested within both table cell (<td>) and table row (<tr>) elements, has a default namespace that has been set to the empty string, which is equivalent to having no default namespace. Listing 6 also shows the first two records returned by this query. Table 6 shows what some of the transformed output looks like after it has been inserted into an XML table element.

connect to sample
...

select xmlserialize(
  content xmlelement(
    name "tr",
      xmlelement(
        name "td",
          xmlelement(
            name "emp",
              xmlnamespaces(
                no default
              ), lastname
          )
      )
  )
  as clob(48)
) as "result"
from employee
order by lastname;


<tr><td><emp xmlns="">ADAMSON</emp></td></tr>   
<tr><td><emp xmlns="">BROWN</emp></td></tr>     
...


connect reset

In this example, the XMLELEMENT function is calling the XMLNAMESPACES function to construct a default namespace that has been set to the empty string. Table 6 shows the serialized output.

Back to top

Summary

The DB2 UDB built-in SQL/XML publishing functions can be used to produce tagged XML documents from relational data. This article has introduced you to the various SQL/XML publishing functions (cast, scalar, and aggregate functions), and shown working examples that you can run yourself and build upon. To learn more about the SQL/XML publishing functions, or for more detailed information about any of the topics covered in this article, see the DB2 Information Center.

Resources

• The IBM DB2 Universal Database SQL Reference, Volume 1 provides more details about XML functions.
  
  
• DB2 Universal Database for Linux, UNIX and Windows Support is the ideal place to locate resources such as the Version 8.2 Information Center and PDF product manuals.
  
  
• For the latest DB2 information online, including more detailed information about the SQL/XML publishing functions, visit the DB2 Information Center.
  
  
• The developerWorks DB2 UDB for Linux, UNIX, and Windows page brings you a world of resources for DB2 on the distributed platforms.
  
  
• Visit the developerWorks XML zone to expand your XML skills.
  
  
• Stay current with developerWorks technical events and Webcasts.

• Download a free trial version of DB2 Universal Database Enterprise Server Edition.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

		Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than eleven years at IBM, Roman has written and edited numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. Roman recently edited Apache Derby -- Off to the Races. You can reach him at roman_b_melnyk@hotmail.com.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top