Using SQL XML with the XMLP data engine ...

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)

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today