Oracle 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.
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.
For Further Reading
XML DB Developer’s Guide
SQL/XML guide on OraTechInfo
