Community Pick: Many members of our community have endorsed this article.

Oracle and XML

Published:
Updated:
Get XML from Oracle
by Ivo Stoykov

Oracle has great and powerful tools for XML manipulation allowing doing most of the work during data retrieval, which eliminates necessity for further data transformation in most of the cases and speeds up the response.

For the samples describing how to fetch data as a fully functional XML document I'm going to use the two very famous tables DEPT and EMP residing in Scott schema. They look like:
SELECT deptno, dname FROM dept; 
                          DEPTNO DNAME
                      ---------- --------------
                              10 ACCOUNTING
                              20 RESEARCH
                              30 SALES
                              40 OPERATIONS 
                      
                      SELECT 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 

Open in new window


XMLGen

The simplest way to generate a XML document from data is XMLGen (or DMS_XMLGEN) package. Its purpose is to convert a SQL query results to a XML. The package expects SQL query as input, converts it to XML format, and returns the result as a CLOB. (This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel.)
set serveroutput on long 5000 wrap off linesize 120;
                      set long 2000;
                      SELECT dbms_xmlgen.getXML(
                        'SELECT empno "EMP_NO"
                              , ename "NAME"
                              , deptno "DEPT_NO"
                           FROM emp
                          WHERE deptno = 10'
                        , 0
                        ) as resXML FROM dual;

Open in new window


As a result Oracle returns root element named  Rowset that contains rows elements with row number attribute. Each row contains elements named as selected columns has been named.

Here is the result of the select above query:
RESXML
                      --------------------------------------------------------------------------------
                      <?xml version="1.0"?>
                      <ROWSET>
                       <ROW>
                        <EMP_NO>7782</EMP_NO>
                        <NAME>CLARK</NAME>
                        <DEPT_NO>10</DEPT_NO>
                       </ROW>
                       <ROW>
                        <EMP_NO>7839</EMP_NO>
                        <NAME>KING</NAME>
                        <DEPT_NO>10</DEPT_NO>
                       </ROW>
                       <ROW>
                        <EMP_NO>7934</EMP_NO>
                        <NAME>MILLER</NAME>
                        <DEPT_NO>10</DEPT_NO>
                       </ROW>
                      </ROWSET>
                       
                      SQL> 

Open in new window


Please note that when the query is executed in PL/SQL, then the returned temporary LOBs automatically get freed at the end of a PL/SQL program block. If a temporary LOB has been previously defined it can also be explicitly freed any time. In OCI and Java, the returned temporary LOB must be freed by the user explicitly.

There is a good discussion with brilliant samples by Tom Kyte on this topic at AskTom.

XML SQL Utility (XSU) PL/SQL API

The XML SQL Utility (XSU) is a set of relational tools to work with XML, allowing the following:
* Transform data retrieved from object-relational database tables or views into XML.
* Extract data from an XML document, and using a canonical mapping, insert the data into appropriate columns
   or attributes of a table or a view.
* Extract data from an XML document and apply this data to updating or deleting values of the appropriate
   columns or attributes.

XSU features are:
* XML documents generation from any SQL query. Supports all the Oracle9i datatypes
* Dynamic generation of DTDs (Document Type Definitions).
* Registration of a XSLT and apply to XML on the fly.
* Transformations during generation (i.e. modifying the default tag names).
* Generation of XML documents in their string or DOM representations.
* Insert/update/delete XML into database tables/views.
* Generation of complex nested XML.

The two packages DBMS_XMLQuery and DBMS_XMLSave correspond with the functions in the Java classes called OracleXMLQuery and OracleXMLSave. Both of these packages use a context handle associated with them which is required. Context is created by one of the constructor-like functions which in turn is getting a handle. This handle is then used in all subsequent calls.
set serveroutput on long 5000 wrap off linesize 120;
                      declare
                        queryCtx DBMS_XMLquery.ctxType;
                        result CLOB;
                        xmlstr varchar2(32767);
                        line varchar2(2000);
                      begin
                      
                        -- set up the query context and get handle...!
                        queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 3');
                       
                        -- get the result..!
                        result := DBMS_XMLQuery.getXML(queryCtx);
                        xmlstr := dbms_lob.SUBSTR(result,32767); -- extract part of it suitable to output buffer
                        -- ready to show result or
                        -- to use the result to put it in tables/send as messages..
                        loop
                          exit when xmlstr is null;
                          line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
                          dbms_output.put_line('| '||line);
                          xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
                        end loop;
                        DBMS_XMLQuery.closeContext(queryCtx);  -- you must close the query handle..
                      end;
                      /

Open in new window


Result is:
| <?xml version = '1.0'?>
                      | <ROWSET>
                      |    <ROW num="1">
                      |       <EMPNO>7369</EMPNO>
                      |       <ENAME>SMITH</ENAME>
                      |       <JOB>CLERK</JOB>
                      |       <MGR>7902</MGR>
                      |       <HIREDATE>12/17/1980 0:0:0</HIREDATE>
                      |       <SAL>800</SAL>
                      |       <DEPTNO>20</DEPTNO>
                      |    </ROW>
                      |    <ROW num="2">
                      |       <EMPNO>7499</EMPNO>
                      |       <ENAME>ALLEN</ENAME>
                      |       <JOB>SALESMAN</JOB>
                      |       <MGR>7698</MGR>
                      |       <HIREDATE>2/20/1981 0:0:0</HIREDATE>
                      |       <SAL>1600</SAL>
                      |       <COMM>300</COMM>
                      |       <DEPTNO>30</DEPTNO>
                      |    </ROW>
                      | </ROWSET>

Open in new window


XSU PL/SQL API allows easy changing the ROW and the ROWSET tag names.
set serveroutput on long 5000 wrap off linesize 120;
                      declare
                        queryCtx DBMS_XMLquery.ctxType;
                        result CLOB;
                        xmlstr varchar2(32767);
                        line varchar2(2000);
                      begin
                         queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 3');
                       
                         DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
                         DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name
                      
                         result := DBMS_XMLQuery.getXML(queryCtx);
                      
                        xmlstr := dbms_lob.SUBSTR(result,32767);
                        loop
                          exit when xmlstr is null;
                          line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
                          dbms_output.put_line('| '||line);
                          xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
                        end loop;
                         DBMS_XMLQuery.closeContext(queryCtx); end;
                      /

Open in new window


And output will be:
| <?xml version = '1.0'?>
                      | <EMPSET>
                      |    <EMP num="1">
                      |       <EMPNO>7369</EMPNO>
                      |       <ENAME>SMITH</ENAME>
                      |       <JOB>CLERK</JOB>
                      |       <MGR>7902</MGR>
                      |       <HIREDATE>12/17/1980 0:0:0</HIREDATE>
                      |       <SAL>800</SAL>
                      |       <DEPTNO>20</DEPTNO>
                      |    </EMP>
                      |    <EMP num="2">
                      |       <EMPNO>7499</EMPNO>
                      |       <ENAME>ALLEN</ENAME>
                      |       <JOB>SALESMAN</JOB>
                      |       <MGR>7698</MGR>
                      |       <HIREDATE>2/20/1981 0:0:0</HIREDATE>
                      |       <SAL>1600</SAL>
                      |       <COMM>300</COMM>
                      |       <DEPTNO>30</DEPTNO>
                      |    </EMP>
                      | </EMPSET>

Open in new window


Another interesting capability is pagination. For the purpose XSU has two functions - setMaxRows() and setSkipRows()

setMaxRows() allows us to set the maximum number of rows to be converted to XML. Affected rows are relative to the current row position from which the last result was generated.

setSkipRows() limits the number of rows transformed to XML. It specifies how much rows to skip before beginning of XML generation.

Transformation of the previous query we include into XML document last two rows:
set serveroutput on long 5000 wrap off linesize 120;
                      declare
                        queryCtx DBMS_XMLquery.ctxType;
                        result CLOB;
                        xmlstr varchar2(32767);
                        line varchar2(2000);
                      begin
                         queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 10');
                       
                         DBMS_XMLQuery.setSkipRows(queryCtx,7); -- set the number of rows to skip
                         DBMS_XMLQuery.setMaxRows(queryCtx,9); -- set the max number of rows per fetch
                      
                         result := DBMS_XMLQuery.getXML(queryCtx);
                      
                        xmlstr := dbms_lob.SUBSTR(result,32767);
                        loop
                          exit when xmlstr is null;
                          line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
                          dbms_output.put_line('| '||line);
                          xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
                        end loop;
                         DBMS_XMLQuery.closeContext(queryCtx); end;
                      /

Open in new window


Which will return
| <?xml version = '1.0'?>
                      | <ROWSET>
                      |    <ROW num="8">
                      |       <EMPNO>7788</EMPNO>
                      |       <ENAME>SCOTT</ENAME>
                      |       <JOB>ANALYST</JOB>
                      |       <MGR>7566</MGR>
                      |       <HIREDATE>4/19/1987 0:0:0</HIREDATE>
                      |       <SAL>3000</SAL>
                      |       <DEPTNO>20</DEPTNO>
                      |    </ROW>
                      |    <ROW num="9">
                      |       <EMPNO>7839</EMPNO>
                      |       <ENAME>KING</ENAME>
                      |       <JOB>PRESIDENT</JOB>
                      |       <HIREDATE>11/17/1981 0:0:0</HIREDATE>
                      |       <SAL>5000</SAL>
                      |       <DEPTNO>10</DEPTNO>
                      |    </ROW>
                      | </ROWSET>

Open in new window


One of most important techniques when retrieving data from Oracle is binding the variables in the queries, so this will be the next. The best source of info on this topic is AskTom again.


Binding Values in XSU

The XSU allows using bind variables. The SQL can contain named bind variables. The must is variables to be prefixed with a colon (:) so as to be bind variables. The usage is:

1. Get context handler using bind variables:
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < :rn');

Open in new window

2. Clear variable list if appropriate.
DBMS_XMLQuery.clearBindValues(queryCtx);

Open in new window

3. Bind each variable's value
DBMS_XMLQuery.setBindValue(queryCtx,'rn',10);

Open in new window

4. Get result:
result := DBMS_XMLQuery.getXML(queryCtx);

Open in new window


Step 3 & 4 could be repeated if necessary. Following is the previous anonymous block transformed to use bind variables:
set serveroutput on long 5000 wrap off linesize 120;
                      declare
                        queryCtx DBMS_XMLquery.ctxType;
                        result CLOB;
                        xmlstr varchar2(32767);
                        line varchar2(2000);
                      begin
                        queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < :rn');
                       
                      --DBMS_XMLQuery.clearBindValues(queryCtx);
                        DBMS_XMLQuery.setBindValue(queryCtx,'rn',3);
                      
                        result := DBMS_XMLQuery.getXML(queryCtx);
                      
                        xmlstr := dbms_lob.SUBSTR(result,32767);
                        loop
                          exit when xmlstr is null;
                          line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
                          dbms_output.put_line('| '||line);
                          xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
                        end loop;
                         DBMS_XMLQuery.closeContext(queryCtx); 
                      end;
                      /

Open in new window


Result is same as above but performance with different values will be much better.

Further reading
* Oracle XML Reference Guide
* Oracle Application Developer's Guide - XML
* XML-SQL Utility (XSU)
3
4,822 Views

Comments (2)

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Good flow, easy to follow. Voted "Yes" above!
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Ivo, good article, thank you!
Voted "YES" above.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.