XML document generator

Create an XML document from an SQL query in 9i.

Output in XML format is very useful for data tranformation in internet-based applications. In general, if you have

XML formatted data then it is easier to convert it to any other format. Using a new package in Oracle 9i allows you to create a XML document from a SQL query. The following example shows you how. (Change the tagname according to your business requirements.)

1. Create table xm_tb.

  CREATE TABLE xm_tb(result CLOB);

2. Write a PL/SQL block to convert emp table data into XML; i.e., the XML data is stored in the xm_tb table.

DECLARE
 qryCtx DBMS_XMLGEN.ctxHandle;
 result CLOB;
BEGIN
 qryCtx := dbms_xmlgen.newContext('SELECT * from emp');
 DBMS_XMLGEN.setRowTag(qryCtx,'EMP');
 DBMS_XMLGEN.setMaxRows(qryCtx,5);
 LOOP
  result := DBMS_XMLGEN.getXML(qryCtx);
 EXIT WHEN
 DBMS_XMLGEN.getNumRowsProcessed(qryCtx)=0;
 INSERT INTO xm_tb VALUES(result);
 END LOOP;
END;
/

3. XML result can be found via:

 SELECT * FROM xm_tb;

4. XML output

<?xml version="1.0"?>
<ROWSET>
 <EMP>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23-MAY-87</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </EMP>
 <EMP>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>03-DEC-81</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
 </EMP>
 <EMP>
 .....
 ....
</ROWSET>

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in November 2002

Dig deeper on Oracle XML

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close