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.