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.
