Creating XML Documents with PL/SQL
Christoph Gächter, Akadia AG,
Information Technology, CH-3604 Thun
Phone: +41 33 335 86 21 / Fax:
+41 33 335 86 25 /
EMail: christoph.gaechter@akadia.com
Overview
The following article shows some of the possibilities how XML documents can be created with PL/SQL and Oracle 8i or 9i. The discussed topics are:
XMLGEN Package
XML-SQL Utility for PL/SQL
XMLDOM Package
The examples use the well known tables DEPT (the master table) and EMP (the detail table): Every employee has exactly one department assigned. One department has no, one, or more employees.
SELECT deptno, dname FROM dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONSSELECT empno, ename, deptno FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
XMLGEN Package
The simplest way to generate an XML document is the usage of the package XMLGEN. The function getXml() takes the parameters for the SQL query and the meta data type (such as DTD) and returns a CLOB containing the XML document.
SELECT xmlgen.getXml(
'SELECT empno "EMP_NO"
, ename "NAME"
, deptno "DEPT_NO"
FROM emp
WHERE deptno = 10'
, 0
) FROM dual;<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMP_NO>7782</EMP_NO>
<NAME>CLARK</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
<ROW num="2">
<EMP_NO>7839</EMP_NO>
<NAME>KING</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
<ROW num="3">
<EMP_NO>7934</EMP_NO>
<NAME>MILLER</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
</ROWSET>The result is a root element named ROWSET, which contains a list of ROW-elements. Each ROW-element has the row number as attribute and each ROW-element contains the elements EMP_NO, NAME, and DEPT_NO. Note that the element names of the ROW-elements are slightly different from the column names of the EMP table!
Using XML-SQL Utility (XSU)
XML-SQL Utility (XSU) provides a simple way of achieving data transformation by mapping any SQL query to XML and vice versa. XSU provides the basic functionality to get and put data to and from a database.
The DBMS_XMLQUERY and DBMS_XMLSAVE are two packages that support XML generation and storage through XML. Here we will focus on XML generation.
Generating XML by invoking getXML() function results in a CLOB that contains the XML document. A context handle needed in most subsequent calls is created in the first step.
DECLARE
queryCtx dbms_xmlquery.ctxType;
result CLOB;
BEGIN
-- set up the query context
queryCtx := dbms_xmlquery.newContext(
'SELECT empno "EMP_NO"
, ename "NAME"
, deptno "DEPT_NO"
FROM emp
WHERE deptno = :DEPTNO'
);If the DTD or XML schema definition has explicitly defined tag names others than the column names then you can change the ROW and ROWSET tag names easily:
dbms_xmlquery.setRowTag(
queryCtx
, 'EMP'
);
dbms_xmlquery.setRowSetTag(
queryCtx
, 'EMPSET'
);Before executing the query you must bind the values to the SQL statement. The named bind variables have to start with a ’:’ in front.
dbms_xmlquery.setBindValue(
queryCtx
, 'DEPTNO'
, 10
);Ok, now you are ready to run the query and generate the XML result as CLOB. A simple procedure printClobOut() supports printing out a CLOB to screen. Finally the query handle must be closed to free the resources.
result := dbms_xmlquery.getXml(queryCtx);
printClobOut(result);
dbms_xmlquery.closeContext(queryCtx);
END;
/The result is something like
<?xml version = '1.0'?>
<EMPSET>
<EMP num="1">
<EMP_NO>7782</EMP_NO>
<NAME>CLARK</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
<EMP num="2">
<EMP_NO>7839</EMP_NO>
<NAME>KING</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
<EMP num="3">
<EMP_NO>7934</EMP_NO>
<NAME>MILLER</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
</EMPSET>Moreover you can use further functionality provided by the DBMS_XMLQUERY such as
Paginating by using setMaxRows() and setSkipRows().
Setting and applying stylesheets by using setStylesheetHeader() and useStylesheet(). The utility generates the XML document, calls the parser, applies the stylesheet, and generates the result which is a huge performance win.
The package DBMS_XMLQUERY supports XML generation. In a similar way you can use DBMS_XMLSAVE to store XML data directly in the database. Possible operations are insertXML(), updateXML(), and deleteXML().
XMLDOM Package
The XMLDOM package implements the Document Object Model Interface (DOM Interface) as defined by W3C XML recommendations. Some of the interfaces are:
DOM Attribute interface
DOM Document interface
DOM DocumentType interface
DOM DOMImplementation interface
DOM Element interface
DOM Node interface
DOM NodeList interface
DOM Notation interface
DOM ProcessingInstruction interface
DOM Text interface
Let’s go through a simple example to discuss the usage of the XMLDOM package. Give special privileges to the user running this code. A private synonym simplifies the access to the package. For example, for user scott:
GRANT javauserpriv to scott;
GRANT javasyspriv to scott;
GRANT EXECUTE ON xmldom TO scott;
CREATE SYNONYM scott.xmldom FOR SYS.xmldom;In the declaration section, you need a set of DOM references. The DOMDocument handle is the most important. It will be used in most subsequent calls. Moreover you need different DOM node handles to reference the main node, the root node, the user node, and the item node for each element. The cursor selects the employees for a given department.
DECLARE
doc xmldom.DOMDocument;
main_node xmldom.DOMNode;
root_node xmldom.DOMNode;
user_node xmldom.DOMNode;
item_node xmldom.DOMNode;
root_elmt xmldom.DOMElement;
item_elmt xmldom.DOMElement;
item_text xmldom.DOMText;
CURSOR get_users(p_deptno NUMBER) IS
SELECT empno
, ename
, deptno
, rownum
FROM emp
WHERE deptno = p_deptno;First of all you create a new document handle. Next you create the main node for this document. The root element is named EMPSET and appended as child node to the main node. The returned handle is used as root node for subsequent calls.
BEGIN
doc := xmldom.newDOMDocument;
main_node := xmldom.makeNode(doc);
root_elmt := xmldom.createElement(
doc
, 'EMPSET'
);
root_node := xmldom.appendChild(
main_node
, xmldom.makeNode(root_elmt)
);For every record found in the query a new element named EMP is created. Analogue to the previous samples, the row number is added as attribute to the element. This element is appended as child node to the root node. The returned handle is used as user node for subsequent calls.
FOR get_users_rec IN get_users(10) LOOP
item_elmt := xmldom.createElement(
doc
, 'EMP'
);
xmldom.setAttribute(
item_elmt
, 'num'
, get_users_rec.rownum
);
user_node := xmldom.appendChild(
root_node
, xmldom.makeNode(item_elmt)
);Now the text elements can be added to the DOM document. In the first step a new element named EMP_NO is created. This element is appended as child node to the user node. In the second step a text node is created which contains the record data, in this case the employee number. This text node is appended as child node to the item node.
item_elmt := xmldom.createElement(
doc
, 'EMP_NO'
);
item_node := xmldom.appendChild(
user_node
, xmldom.makeNode(item_elmt)
);
item_text := xmldom.createTextNode(
doc
, get_users_rec.empno
);
item_node := xmldom.appendChild(
item_node
, xmldom.makeNode(item_text)
);The same can be done with the text elements NAME and DEPT_NO.
After all records have been processed and all data has been loaded into the DOM document it can be e.g. filed out and its resources can be released:
END LOOP;
xmldom.writeToFile(
doc
, '/tmp/xml/docSample.xml'
);
xmldom.freeDocument(doc);
END;
/Note that the XMLDOM package is able to write the XML file in every location accessible by OS user oracle, regardless of the current initialisation parameter UTL_FILE_DIR. The resulting file contains the following lines:
<?xml version = '1.0' encoding = 'UTF-8'?>
<EMPSET>
<EMP num="1">
<EMP_NO>7782</EMP_NO>
<NAME>CLARK</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
<EMP num="2">
<EMP_NO>7839</EMP_NO>
<NAME>KING</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
<EMP num="3">
<EMP_NO>7934</EMP_NO>
<NAME>MILLER</NAME>
<DEPT_NO>10</DEPT_NO>
</EMP>
</EMPSET>Even if the usage of XMLDOM package seems to be complex at the beginning, the structure of the DOM document is build up in a simple way: Every element or attribute must be added as a node to a parent node. The same approach with equivalent methods is used within Java and its XML implementation.
For easier handling of the common functionality you may write your own XML library to simplify for example adding text nodes to parent nodes.
Some limitations: Keep in mind, that the whole DOM tree is kept in memory until the document is freed. Depending on database memory configuration the total number of nodes is limited, e.g. with JAVA_POOL_SIZE of 10’0000'000 bytes we could add up to 20'000 nodes.
Another limitation is a memory leak in the Java implementation of the DOM interface and XML parser. This bug is known under Sun Solaris and can be fixed with Oracle 8.1.7.3 and bug fix 2104071 or with Oracle 9.2.
Conclusion
The XMLGEN package can be used for simple XML document generation. The data can be fetched from a single SQL statement.
XSU with the two packages DBMS_XMLQUERY and DBMS_XMLSAVE supports XML generation and storage through XML. Additional functionality allows the developer to bind variables, to paginate the result set, or to apply stylesheets directly.
The most sophisticated approach is the XMLDOM package. Here you have the full flexibility provided by the DOM interface. Every given DTD or XML schema definition can be implemented using complex elements, attributes and more.
Links and Documents
Further articles about generating XML documents with PL/SQL
Oracle XML Reference Guide
Oracle Application Developer's Guide - XML
Download Scripts