Oracle Apex : RESTful Web Service with GET handler which will return response in XML format

One of the great feature of Oracle APEX is a RESTful Web Service.  You can create a RESTful Web Service and publish it very easily within few minutes. You can create four type of the method for the Resource Handler

• GET
• PUT
• POST
• DELETE

GET method will execute a SQL Query or an anonymous PL/SQL block define in the source section and transforms the result set into the selected format. There are only two format available in the Oracle APEX. 

• JSON
• CSV

Recently, I have been working of a project that requires to transforms the result set into the XML format. This functionality is not available out of the box in the Oracle APEX. But there is a way around for this. You can select the PL/SQL for the Source Type and define an anonymous PL/SQL block  or packaged procedure/ function that will transforms the result set into XML format. 

Let's do it together.

This is the XML format which we want as a response from the GET method.

<employeesinfo>
    <department>
        <deptno>10</deptno>
        <dname>ACCOUNTING</dname>
        <location>NEW YORK</location>
        <employee>
            <empno>7839</empno>
            <ename>KING</ename>
            <job>PRESIDENT</job>
            <mgr></mgr>
            <hiredate>1981-11-17</hiredate>
            <sal>5000</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7934</empno>
            <ename>MILLER</ename>
            <job>CLERK</job>
            <mgr>7782</mgr>
            <hiredate>1982-01-23</hiredate>
            <sal>1300</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7782</empno>
            <ename>CLARK</ename>
            <job>MANAGER</job>
            <mgr>7839</mgr>
            <hiredate>1981-06-09</hiredate>
            <sal>2450</sal>
            <comm></comm>
        </employee>
    </department>
    <department>
        <deptno>20</deptno>
        <dname>RESEARCH</dname>
        <location>DALLAS</location>
        <employee>
            <empno>7566</empno>
            <ename>JONES</ename>
            <job>MANAGER</job>
            <mgr>7839</mgr>
            <hiredate>1981-04-02</hiredate>
            <sal>2975</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7876</empno>
            <ename>ADAMS</ename>
            <job>CLERK</job>
            <mgr>7788</mgr>
            <hiredate>1983-01-12</hiredate>
            <sal>1100</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7369</empno>
            <ename>SMITH</ename>
            <job>CLERK</job>
            <mgr>7902</mgr>
            <hiredate>1980-12-17</hiredate>
            <sal>800</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7902</empno>
            <ename>FORD</ename>
            <job>ANALYST</job>
            <mgr>7566</mgr>
            <hiredate>1981-12-03</hiredate>
            <sal>3000</sal>
            <comm></comm>
        </employee>
        <employee>
            <empno>7788</empno>
            <ename>SCOTT</ename>
            <job>ANALYST</job>
            <mgr>7566</mgr>
            <hiredate>1982-12-09</hiredate>
            <sal>3000</sal>
            <comm></comm>
        </employee>
    </department>
</employeesinfo>

1. From the application builder select RESTful Services under SQL Workshop

2. Select oracle.example.hr module. For this demo I am using this sample module available in Apex.

3. Select +Create Template to create new Resource Template

4. Enter empinfoxml/ for URI Template and click on Create

5. Resource Template is created and it will appear in the left side. Click on +Resource Handler     under the empinfoxml/ Resource Template to create new resource handler

6. On the Resource Handler page, select GET for method, PL/SQL for Source Type and No for Require Secure Access. Enter the following anonymous block in the Source section and click on Create

BEGIN
  get_employees_info;
END;

get_employees_info is a procedure that will transforms employees information into XML format. Following is the code of the procedure.

CREATE OR REPLACE PROCEDURE get_employees_info IS
    L_response CLOB;
BEGIN    
    SELECT XMLELEMENT("employeesinfo",
                      XMLAGG(
                            XMLELEMENT("department",
                                      XMLFOREST(d.deptno as "deptno",
                                                d.dname as "dname",
                                                d.loc   as "location"
                                               ),
                                         XMLAGG(
                                               XMLELEMENT("employee",
                                                          XMLELEMENT("empno",e.empno),
                                                          XMLELEMENT("ename",e.ename),
                                                          XMLELEMENT("job",e.job),
                                                          XMLELEMENT("mgr",e.mgr),
                                                          XMLELEMENT("hiredate",e.hiredate),
                                                          XMLELEMENT("sal",e.sal),
                                                          XMLELEMENT("comm",e.comm)
                                                         )
                                               )            
                                      )
                                )
                ).getClobVal()  
    INTO L_response                               
    FROM dept d, emp e
    WHERE d.deptno= e.deptno
    GROUP BY d.deptno,d.dname,d.loc
    ORDER BY d.deptno,e.empno;
    htp.prn(L_response);
END get_employees_info;

7.  Now test the webservice using request URL. The result is rendered in XML format.