Generating XML from Relational Data using Oracle SQL/XML

May 29, 2017

in SQL, XML DB

SQL XML in Oracle: Generating XML from Relational DataOracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.

This article shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.

Let’s use the classic emp table of SCOTT schema as example.

SQL> desc emp;
 Name                    Null?    Type
 ----------------------- -------- ------------
 EMPNO                   NOT NULL NUMBER(4)
 ENAME                            VARCHAR2(10)
 JOB                              VARCHAR2(9)
 MGR                              NUMBER(4)
 HIREDATE                         DATE
 SAL                              NUMBER(7,2)
 COMM                             NUMBER(7,2)
 DEPTNO                           NUMBER(2)

That’s the data inside emp table:

SQL> select * from emp;

EMPNO ENAME    JOB          MGR HIREDATE    SAL  COMM DEPTNO
----- -------- ---------- ----- --------- ----- ----- ------
 7839 KING     PRESIDENT        17-NOV-81  5000           10
 7698 BLAKE    MANAGER     7839 01-MAY-81  2850           30
 7782 CLARK    MANAGER     7839 09-JUN-81  2450           10
 7566 JONES    MANAGER     7839 02-APR-81  2975           20
 7788 SCOTT    ANALYST     7566 19-APR-87  3000           20
 7902 FORD     ANALYST     7566 03-DEC-81  3000           20
 7369 SMITH    CLERK       7902 17-DEC-80   800           20
 7499 ALLEN    SALESMAN    7698 20-FEB-81  1600   300     30
 7521 WARD     SALESMAN    7698 22-FEB-81  1250   500     30
 7654 MARTIN   SALESMAN    7698 28-SEP-81  1250  1400     30
 7844 TURNER   SALESMAN    7698 08-SEP-81  1500     0     30
 7876 ADAMS    CLERK       7788 23-MAY-87  1100           20
 7900 JAMES    CLERK       7698 03-DEC-81   950           30
 7934 MILLER   CLERK       7782 23-JAN-82  1300           10

The requirement is to return details of highly paid employees (say, sal > 2000) – i.e. the result of this SQL…

SQL> select empno
  2       , ename
  3       , sal
  4       , deptno
  5  from emp
  6  where sal > 2000;

     EMPNO ENAME        SAL     DEPTNO
---------- ---------- ----- ----------
      7839 KING        5000         10
      7698 BLAKE       2850         30
      7782 CLARK       2450         10
      7566 JONES       2975         20
      7788 SCOTT       3000         20
      7902 FORD        3000         20
 

..constructed in this XML form:

<Employee xmlns="http://www.w3.org/2001/XMLSchema">
  <!--XML generated from emp table-->
  <r deptno="10">
    <empno>7839</empno>
    <ename>KING</ename>
    <sal>5000</sal>
  </r>
  <r deptno="30">
    <empno>7698</empno>
    <ename>BLAKE</ename>
    <sal>2850</sal>
  </r>
  <r deptno="10">
    <empno>7782</empno>
    <ename>CLARK</ename>
    <sal>2450</sal>
  </r>
  <r deptno="20">
    <empno>7566</empno>
    <ename>JONES</ename>
    <sal>2975</sal>
  </r>
  <r deptno="20">
    <empno>7788</empno>
    <ename>SCOTT</ename>
    <sal>3000</sal>
  </r>
  <r deptno="20">
    <empno>7902</empno>
    <ename>FORD</ename>
    <sal>3000</sal>
  </r>
</Employee>

How do we go from the simple relational output to XML form?

Understanding the output XML structure

Before approaching the solution, take a close look at the requirement. Note that:

  • Each row of the SQL output is a separate XML element (see <r> tag). For N rows of relational output, there are N <r> elements in the XML output.
  • Each <r> tag has sub-elements corresponding to the attributes selected by the relational SQL (empno, ename, sal). One attribute (deptno) is represented as an XML attribute, not as a sub-element.
  • The XML output contains a root element (<Employee>) which is not derived from the relational SQL.
  • The XML output contains a comment and a default namespace, also not derived from the relational SQL.

Oracle SQL/XML is designed to address such a requirement.

Oracle SQL/XML: What is it for?

As the documentation says, SQL/XML functions let you take advantage of the power and flexibility of both SQL and XML.

SQL/XML functions fall into two groups:

  • Query and access functions

    Query and access functions can be used to access XML content in the database (stored as XMLType) using SQL or to construct relational data from XML data.

    XMLQUERY, XMLTABLE, XMLEXISTS, XMLCAST are examples of query and access functions. We’ll see them in action in a future post.

  • Publishing (or generation) functions

    Publishing (or generation) functions can be used to construct XML data from relational data.

    XMLELEMENT, XMLFOREST, XMLCONCAT, XMLAGG are examples of generation functions. We’ll focus on these in this post.

Note: All tests in this post were done on Oracle version 11.2.0.2.

SQL/XML generation functions: XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLAGG, XMLCOMMENT, XMLSERIALIZE

A rundown of SQL/XML generation functions that go into constructing the XML output required for this case study.

XMLELEMENT

creates an XML element from relational data.

To generate a simple XML element, call XMLELEMENT with an identifier and value:

XMLELEMENT("identifier", value)

where
identifier forms the XML tags surrounding the value, of this form <identifier>value</identifier>

Use XMLELEMENT on empno column, to enclose it within <empno> tags:

SQL> select xmlelement("empno", empno) empno_x
  2  from emp
  3  where sal > 2000;

EMPNO_X
----------------------------------------------
<empno>7839</empno>
<empno>7698</empno>
<empno>7782</empno>
<empno>7566</empno>
<empno>7788</empno>
<empno>7902</empno>

6 rows selected.

To create an XML hierarchy, XMLELEMENT definitions can be nested within other XMLELEMENTs as parameters.

Use nested XMLELEMENTs to make element <empno> a sub-element of <r>:

SQL> select xmlelement("r"
  2     , xmlelement("empno", empno)
  3                   ) result_x
  4  from emp
  5  where sal > 2000;

RESULT_X
------------------------------------
<r><empno>7839</empno></r>
<r><empno>7698</empno></r>
<r><empno>7782</empno></r>
<r><empno>7566</empno></r>
<r><empno>7788</empno></r>
<r><empno>7902</empno></r>

6 rows selected.

A sequence of XMLEMENTs can be stacked and nested to produce this XML result:

SQL> select xmlelement("r"
  2     , xmlelement("empno", empno)
  3     , xmlelement("ename", ename)
  4     , xmlelement("sal", sal)
  5                   ) result_x
  6  from emp
  7  where sal > 2000;

RESULT_X
--------------------------------------------------------------
<r><empno>7839</empno><ename>KING</ename><sal>5000</sal></r>
<r><empno>7698</empno><ename>BLAKE</ename><sal>2850</sal></r>
<r><empno>7782</empno><ename>CLARK</ename><sal>2450</sal></r>
<r><empno>7566</empno><ename>JONES</ename><sal>2975</sal></r>
<r><empno>7788</empno><ename>SCOTT</ename><sal>3000</sal></r>
<r><empno>7902</empno><ename>FORD</ename><sal>3000</sal></r>

6 rows selected.

We have got the core of the output needed, the next step is to put in the XML attribute (deptno) inside the <r> tag. On to XMLATTRIBUTES.

XMLATTRIBUTES

is optionally used with XMLELEMENT to specify XML attributes for the generated elements. XMLATTRIBUTES can only be used with XMLELEMENT, not on its own.

To generate a simple XML attribute, include the XMLATTRIBUTES function inside the XMLEMENT call:

XMLELEMENT("identifier", XMLATTRIBUTES(attribute_value AS "attribute_name"), value)

Apply this on the emp XML to include deptno as XML attribute:

SQL> -- XMLATTRIBUTES to add attribute to XML
SQL> select xmlelement("r"
  2     , xmlattributes(deptno AS "deptno")
  3     , xmlelement("empno", empno)
  4     , xmlelement("ename", ename)
  5     , xmlelement("sal", sal)
  6                   ) result_x
  7  from emp
  8  where sal > 2000;

RESULT_X
-------------------------------------------------------------------------
<r deptno="10"><empno>7839</empno><ename>KING</ename><sal>5000</sal></r>
<r deptno="30"><empno>7698</empno><ename>BLAKE</ename><sal>2850</sal></r>
<r deptno="10"><empno>7782</empno><ename>CLARK</ename><sal>2450</sal></r>
<r deptno="20"><empno>7566</empno><ename>JONES</ename><sal>2975</sal></r>
<r deptno="20"><empno>7788</empno><ename>SCOTT</ename><sal>3000</sal></r>
<r deptno="20"><empno>7902</empno><ename>FORD</ename><sal>3000</sal></r>

6 rows selected.

That gives us the bulk of the output we want. There is a more compact SQL though that can give us the same output, using XMLFOREST.

XMLFOREST

generates a "forest" (perhaps so named since it is a collection of XML trees) of XML elements.

XMLFOREST can work as a simpler alternative* to generating multiple XMLELEMENTs, especially useful if a largeish number of elements are to be included in the output.

To generate an XML forest, include the values in the XMLFOREST call, optionally with aliases.

XMLFOREST(value1 AS "identifier1"
                           , value2 AS "identifier2"
                           , …)

Rewrite the set of XMLELEMENTs in the previous SQL to use XMLFOREST instead:

SQL> -- XMLFOREST to generate "forest" of XML elements
SQL> select xmlelement("r"
  2     , xmlattributes(deptno AS "deptno")
  3     , xmlforest (empno as "empno"
  4                  , ename as "ename"
  5                  , sal as "sal"
  6                  )) result_x
  7  from emp
  8  where sal > 2000;

RESULT_X
-------------------------------------------------------------------------
<r deptno="10"><empno>7839</empno><ename>KING</ename><sal>5000</sal></r>
<r deptno="30"><empno>7698</empno><ename>BLAKE</ename><sal>2850</sal></r>
<r deptno="10"><empno>7782</empno><ename>CLARK</ename><sal>2450</sal></r>
<r deptno="20"><empno>7566</empno><ename>JONES</ename><sal>2975</sal></r>
<r deptno="20"><empno>7788</empno><ename>SCOTT</ename><sal>3000</sal></r>
<r deptno="20"><empno>7902</empno><ename>FORD</ename><sal>3000</sal></r>

6 rows selected.

Now for adding the root element <Employee>. Note that the SQL so far returns multiple rows, and to wrap one root element around all of it, we need a function to group the multiple rows as a single unit. Enter XMLAGG.

XMLAGG

aggregates a set of XMLType instances.

The XMLType instances could be the output of XMLELEMENT, as in this example.

To aggregate using XMLAGG, pass the XMLType instance to XMLAGG along with an optional ORDER BY clause.

XMLAGG(XMLType instance, ORDER BY clause)

Encase the result obtained so far in XMLAGG, and pass it as parameter to another XMLELEMENT which adds the root element <Employee>. Along with the root element, also set the XML attribute for namespace.

SQL> -- XMLAGG to include root element + namespace
SQL> select xmlelement("Employee"
  2   , xmlattributes('http://www.w3.org/2001/XMLSchema'
  3                                  as "xmlns")
  4   , xmlagg
  5     (xmlelement("r"
  6     , xmlattributes(deptno AS "deptno")
  7     , xmlforest (empno as "empno"
  8                  , ename as "ename"
  9                  , sal as "sal"
 10                  )))
 11       ) result_x
 12  from emp
 13  where sal > 2000;

RESULT_X
------------------------------------------------------------
<Employee xmlns="http://www.w3.org/2001/XMLSchema"><r deptno
="10"><empno>7839</empno><ename>KING</ename><sal>5000</sal><
/r><r deptno="30"><empno>7698</empno><ename>BLAKE</ename><sa
l>2850</sal></r><r deptno="10"><empno>7782</empno><ename>CLA
RK</ename><sal>2450</sal></r><r deptno="20"><empno>7566</emp
no><ename>JONES</ename><sal>2975</sal></r><r deptno="20"><em
pno>7788</empno><ename>SCOTT</ename><sal>3000</sal></r><r de
ptno="20"><empno>7902</empno><ename>FORD</ename><sal>3000</s
al></r></Employee>

Let’s add in the comment (<!–XML generated from emp table–>) next.

XMLCOMMENT

adds a comment to the XML document.

To add a comment in XML, include this inside XMLELEMENT:

XMLCOMMENT(comment text)

SQL> -- XMLCOMMENT to add comment
SQL> select xmlelement("Employee"
  2   , xmlattributes('http://www.w3.org/2001/XMLSchema'
  3                                  as "xmlns")
  4   , xmlcomment('XML generated from emp table')
  5   , xmlagg
  6     (xmlelement("r"
  7     , xmlattributes(deptno AS "deptno")
  8     , xmlforest (empno as "empno"
  9                  , ename as "ename"
 10                  , sal as "sal"
 11                  )))
 12       ) result_x
 13  from emp
 14  where sal > 2000;

RESULT_X
------------------------------------------------------------
<Employee xmlns="http://www.w3.org/2001/XMLSchema"><!--XML g
enerated from emp table--><r deptno="10"><empno>7839</empno>
<ename>KING</ename><sal>5000</sal></r><r deptno="30"><empno>
7698</empno><ename>BLAKE</ename><sal>2850</sal></r><r deptno
="10"><empno>7782</empno><ename>CLARK</ename><sal>2450</sal>
</r><r deptno="20"><empno>7566</empno><ename>JONES</ename><s
al>2975</sal></r><r deptno="20"><empno>7788</empno><ename>SC
OTT</ename><sal>3000</sal></r><r deptno="20"><empno>7902</em
pno><ename>FORD</ename><sal>3000</sal></r></Employee>

We now have the output we need in XML form. If you want to “prettify” the output, XMLSERIALIZE is there to help.

XMLSERIALIZE

returns a string or LOB representation of XML data. While doing so, it takes an "indent" parameter to present the XML structure in a more readable form.

To pretty-print using XMLSERIALIZE, wrap the XMLType instance inside XMLSERIALIZE as document of datatype CLOB, with indent = a non-zero number, say 2.

SQL> -- XMLSERIALIZE to "pretty print" XML
SQL> select xmlserialize(
  2     document xmlelement("Employee"
  3   , xmlattributes('http://www.w3.org/2001/XMLSchema'
  4                                  as "xmlns")
  5   , xmlcomment('XML generated from emp table')
  6   , xmlagg
  7     (xmlelement("r"
  8     , xmlattributes(deptno AS "deptno")
  9     , xmlforest (empno as "empno"
 10                  , ename as "ename"
 11                  , sal as "sal"
 12                  )))
 13       )
 14    as clob indent size = 2) result_x
 15  from emp
 16  where sal > 2000;

RESULT_X
-------------------------------------------------------
<Employee xmlns="http://www.w3.org/2001/XMLSchema">
  <!--XML generated from emp table-->
  <r deptno="10">
    <empno>7839</empno>
    <ename>KING</ename>
    <sal>5000</sal>
  </r>
  <r deptno="30">
    <empno>7698</empno>
    <ename>BLAKE</ename>
    <sal>2850</sal>
  </r>
  <r deptno="10">
    <empno>7782</empno>
    <ename>CLARK</ename>
    <sal>2450</sal>
  </r>
  <r deptno="20">
    <empno>7566</empno>
    <ename>JONES</ename>
    <sal>2975</sal>
  </r>
  <r deptno="20">
    <empno>7788</empno>
    <ename>SCOTT</ename>
    <sal>3000</sal>
  </r>
  <r deptno="20">
    <empno>7902</empno>
    <ename>FORD</ename>
    <sal>3000</sal>
  </r>
</Employee>

Done. That’s given us the exact XML structure we wanted.

*Caution! XMLFOREST and XMLELEMENT handle NULLs differently

It is intuitive to think of XMLFOREST as a blanket replacement for a set of XMLELEMENTs, but there is a major difference in the way the two handle NULLs. Take care before rewriting one as the other.

Check the result of using a set of XMLELEMENTs vs XMLFOREST on the nullable “comm” column in the emp table.

Taking a small selection of data for the demo:

SQL> select empno, comm
  2  from emp
  3  where empno < 7600;

     EMPNO       COMM
---------- ----------
      7369
      7499        300
      7521        500
      7566

Querying this data using XMLELEMENT and XMLFOREST produces different results.

SQL> -- Using XMLELEMENT: nulls included
SQL> select xmlelement("comm", comm) result_x
  2  from emp where empno < 7600;

RESULT_X
----------------------------------------------
<comm></comm>
<comm>300</comm>
<comm>500</comm>
<comm></comm>

SQL> -- Using XMLFOREST: nulls excluded
SQL> select xmlforest(comm as "comm") result_x
  2  from emp where empno < 7600;

RESULT_X
----------------------------------------------

<comm>300</comm>
<comm>500</comm>

Summary

This post demonstrates how XML can be generated from relational data using SQL/XML in Oracle.

It uses a step-by-step example of building an XML output from relational data, using SQL/XML functions XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLAGG, XMLCOMMENT and XMLSERIALIZE.

Want to do the reverse i.e. generate relational data from XML? Read XMLTABLE: Convert XML Data to Relational Form.

For Further Reading

XML DB Developer’s Guide
SQL/XML guide on OraTechInfo

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: