<

Oracle and XML

Published on
10,985 Points
4,185 Views
3 Endorsements
Last Modified:
Approved
Community Pick
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
Comment
Author:Ivo Stoykov
2 Comments
LVL 73

Expert Comment

by:Qlemo
Good flow, easy to follow. Voted "Yes" above!
0
LVL 61

Expert Comment

by:Kevin Cross
Ivo, good article, thank you!
Voted "YES" above.
0

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month