DB2 Basics

An introduction to the SQL/XML publishing functions

Comments

Content series:

This content is part # of # in the series: DB2 Basics

Stay tuned for additional content in this series.

This content is part of the series:DB2 Basics

Stay tuned for additional content in this series.

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:

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.

Listing 1. Using the XMLELEMENT function to generate XML table elements from relational data
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
Table 1. Serialized output from the XMLELEMENT function is ready for use in an XML document
EMPNOFIRST_NAMELAST_NAMEPHONENODEPTNODEPARTMENT
000010CHRISTINEHAAS3978A00SPIFFY COMPUTER SERVICE
000050JOHNGEYER6789E01SUPPORT SERVICES
000110VINCENZOLUCCHESSI3490A00SPIFFY COMPUTER SERVICE
000120SEANO'CONNELL2167A00SPIFFY COMPUTER SERVICE
000200DAVIDBROWN4501D11MANUFACTURING SYSTEMS
000220JENNIFERLUTZ0672D11MANUFACTURING SYSTEMS
000230JAMESJEFFERSON2094D21ADMINISTRATION SYSTEMS
000250DANIELSMITH0961D21ADMINISTRATION SYSTEMS
000280ETHELSCHNEIDER8997E11OPERATIONS
000310MAUDESETRIGHT3332E11OPERATIONS
000320RAMLALMEHTA9990E21SOFTWARE SUPPORT
000340JASONGOUNOT5698E21SOFTWARE SUPPORT

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.

Listing 2. Using the XMLFOREST function to generate XML table elements from relational data
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
Table 2. Serialized output from the XMLFOREST function is ready for use in an XML document
EMPNOFIRST_NAMELAST_NAMEPHONENODEPTNODEPARTMENT
000010CHRISTINEHAAS3978A00SPIFFY COMPUTER SERVICE DIV.
000050JOHNGEYER6789E01SUPPORT SERVICES
000110VINCENZOLUCCHESSI3490A00SPIFFY COMPUTER SERVICE DIV.
000120SEANO'CONNELL2167A00SPIFFY COMPUTER SERVICE DIV.
000200DAVIDBROWN4501D11MANUFACTURING SYSTEMS
000220JENNIFERLUTZ0672D11MANUFACTURING SYSTEMS
000230JAMESJEFFERSON2094D21ADMINISTRATION SYSTEMS
000250DANIELSMITH0961D21ADMINISTRATION SYSTEMS
000280ETHELSCHNEIDER8997E11OPERATIONS
000310MAUDESETRIGHT3332E11OPERATIONS
000320RAMLALMEHTA9990E21SOFTWARE SUPPORT
000340JASONGOUNOT5698E21SOFTWARE SUPPORT

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.

Listing 3. Constructing XML attribute nodes for an XML element node
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.

Table 3. Excerpt of serialized output from the XMLELEMENT function
EMPNOFIRST_NAMELAST_NAMEPHONENODEPTNODEPARTMENT
000010CHRISTINEHAAS3978A00SPIFFY COMPUTER SERVICE
000050JOHNGEYER6789E01SUPPORT SERVICES
000110VINCENZOLUCCHESSI3490A00SPIFFY COMPUTER SERVICE
000120SEANO'CONNELL2167A00SPIFFY COMPUTER SERVICE
000200DAVIDBROWN4501D11MANUFACTURING SYSTEMS
000220JENNIFERLUTZ0672D11MANUFACTURING SYSTEMS

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.

Listing 4. The XMLCONCAT function can be used to concatenate XML values such as, in this case, two XML element nodes
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.

Table 4. Excerpt of serialized output from the XMLELEMENT function
FIRST_NAMELAST_NAME
BRUCEADAMSON
DAVIDBROWN
JOHNGEYER
JASONGOUNOT
CHRISTINEHAAS
EILEENHENDERSON
JAMESJEFFERSON
SYBILJOHNSON
WILLIAMJONES
SALLYKWAN
WINGLEE
VINCENZOLUCCHESSI

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.

Listing 5. The XMLAGG function can be used to aggregate XML values; in this case, three XML element nodes
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.

Table 5. Serialized output from the XMLELEMENT function
DEPTNO123
A00HAASLUCCHESSIO'CONNELL
C01KWANNICHOLLSQUINTANA

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.

Listing 6. The XMLNAMESPACES function can be used to construct XML namespace declarations
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.

Table 6. Excerpt of serialized output from the XMLELEMENT function
EMP_NAME
<emp xmlns="">ADAMSON</emp>
<emp xmlns="">BROWN</emp>

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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=97890
ArticleTitle=DB2 Basics: An introduction to the SQL/XML publishing functions
publish-date=11032005