Those of you that have used the data extraction engine will know that it can generate structured XML when executing a data template, with all the bells and whistles, event triggers, group filters, summary columns, etc. The engine can also accept a simple sql statement too including parameters. Passing say:
select JOB,
ENAME,
DEPTNO
from EMP
Will render the following XML.
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
<ROW>
<JOB>CLERK</JOB>
<ENAME>SMITH</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<JOB>SALESMAN</JOB>
<ENAME>ALLEN</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<JOB>SALESMAN</JOB>
<ENAME>WARD</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>
Thats all very nice but what if you wanted to extract the data in a structured format with out having to develop a data template. Well now you can, the latest release of the engine supports SQL XML. This sql format allows you to extract hierarchical XML from the Oracle database, the XMLP data engine can now support this too.
So the following statement:
SELECT XMLELEMENT("Department", XMLAttributes(deptno AS "deptno"),
XMLAgg(XMLElement("Employee", e.job||' '||e.ename))).getClobVal()
AS "Dept_list"
FROM emp e
GROUP BY e.deptno
will generate the following XML:
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
<ROW>
<Dept_list>
<Department deptno="10">
<Employee>MANAGER CLARK</Employee>
<Employee>PRESIDENT KING</Employee>
<Employee>CLERK MILLER</Employee>
</Department>
</Dept_list>
</ROW>
<ROW>
<Dept_list>
<Department deptno="20">
<Employee>CLERK SMITH</Employee>
<Employee>ANALYST FORD</Employee>
<Employee>CLERK ADAMS</Employee>
<Employee>ANALYST SCOTT</Employee>
<Employee>MANAGER JONES</Employee>
</Department>
</Dept_list>
</ROW>
...
</ROWSET>
Notice, still rowset/row but there is now heirarchy in the sub elements ... no more regrouping for me in my template :o)
For the XMLP engine to sucessfully extract the data using the SQL XML format we need to append the .getClobVal() function to the end of the statement.
So, the world is your oyster when it comes to using SQL with the XMLP data engine .. so start shucking:o)
