title: Creating Oracle APEX Restful Services that returns XML and JSON
body: <p>I'm trying to create a Restful service that will return JSON or XML data. The company is using Oracle application express (Restful Services) as the tool for the job.</p>
  
  <p>I've noticed that the latest versions of Application Express the XML option is no longer.?</p>
  
  <p>I was wondering does anyone one have any examples or ideas on how I can can create a Restful service that will return JSON or XML depending on the content type I request ?</p>
  
  <p>I'm currently running Application Express 4.2.5.00.08.</p>
  
  <p>This has always been pretty straight forward in other languages but I need to work it out with oracle data services <a href="http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html" rel="nofollow">ORDS</a></p>
  
  <p>The current setup I have tried is to define a Restful service as follows ;</p>
  
  <p>Resful Service Module :</p>
  
  <pre><code>  URI Template : test
    Method       : GET
    Source       : Query   Format  : JSON   (XML is no longer option)
    Require Secure Access : NO
  </code></pre>
  
  <p>Source :</p>
  
  <pre><code>SELECT RESTful_Testing.GetSampleData(:contentType) FROM DUAL
  </code></pre>
  
  <p>Parameters</p>
  
  <pre><code>Name               : Accept
  Bind Variable Name : contentType
  Access Method      : IN
  Source Type        : HTTP Header
  Parameter Type     : String
  </code></pre>
  
  <p>I have set it up so I can call an Oracle Package and pass in the content type. The problem I have is that the format is already predefined, so any attempt to return XML would be incorrect ( or can I alter the header later).</p>
  
  <p>The Package Code is Very basic and is as follows just for testing ;</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE APEX_EDS.RESTful_Testing AS 
  
      TYPE resultCursor IS REF CURSOR;
  
      function GetSampleData(contentType VARCHAR) return resultCursor;
  
  END RESTful_Testing;
  /
  
  
  CREATE OR REPLACE PACKAGE BODY APEX_EDS.RESTful_Testing AS 
  
      function GetSampleData(contentType VARCHAR) return resultCursor AS
          O_resultCursor  resultCursor;
  
          BEGIN
              -- Check the Content Type
              IF ( contentType = 'application/xml' ) THEN
                  OPEN O_resultCursor FOR
                  select '&lt;ROOT&gt;&lt;NAME&gt;John&lt;/NAME&gt;&lt;SURNAME&gt;Smith&lt;/SURNAME&gt;   
                  &lt;CONTENT_TYPE&gt;application/xml&lt;/CONTENT_TYPE&gt;&lt;/ROOT&gt;' FROM DUAL;
                  return(O_resultCursor);
              ELSE -- Assume JSON so return normal
                  OPEN O_resultCursor FOR
                  select 'John' FirstName, 'Smith' Surname, contentType ContentType FROM DUAL;
                  return(O_resultCursor);
              END IF;
          END;   
  END RESTful_Testing;
  </code></pre>
  
  <p>Hope that further adds to my issue and hope there are some oracle ORDS experts out there :).</p>