Oracle REST APIs and your XML

Can ORDS work with XML? You bet it can! The Database has supported XML for decades, and ORDS has as well from day 1. Your REST APIs delievered via ORDS can handle the data in your database, regardless of how it’s stored or organized.

This is part of the Converged Database philosophy that is Oracle Database. Not 9 databases to serve up 9 types of data, but one engine, one SQL, one PL/SQL, and one REST API rig to serve up all 9 in a single instance.

But back to the XML. Let’s say you have a relational table.

And one of the columns in this relational table just happens to be defined as XML, via SYS.XMLTYPE.

When starting to build the REST APIs for your data with ORDS, one of the first questions to tackle is do you use AUTOREST, or do we write our own REST APIs?

Let’s look at both routes!

Building your own REST Modules

What if you were going to build your own set of RESTful Web Services that allowed you to create some rows, upload some XML, get that XML back out, change or even remove said XML?

I’m going to build a table and show a few different ways to work with the data and XML content. I won’t be building a full CRUD API, but there will be enough here to get a great idea of your options.

What I WILL show you…

• get a list of all of our EMPLOYEEs
• add an XML doc for our employee
• get a single EMPLOYEE, with flattened out XML attributes
• get a single EMPLOYEE, with a link for the raw XML content

Our table is again, ‘the Good Place’ level basic – HR.EMPLOYEES plus a ‘garbage’ XML column, stored as a CLOB:

CREATE TABLE emps_xml
	AS
		SELECT *
		  FROM employees;
 
ALTER TABLE EMPS_XML ADD (
	XML_SCRATCHPAD SYS.XMLTYPE
) XMLTYPE XML_SCRATCHPAD STORE AS CLOB;
 
COMMENT ON COLUMN EMPS_XML.XML_SCRATCHPAD IS
	'whatever you want';

There a few options for how you want to store your XML, and that could have an impact on how you build REST Services for it, so keep that in mind. My code is for this particular ‘STORE AS CLOB’ variety.

Are you looking for great content around XML? Tim has been providing that since 9i came out!

Getting a list of employees with links to their XML content

There’s some chicken and egg maths here – do we build the REST API for getting the collection first, with broken links, or do we build the ‘child nodes’ first, and then double back to do the collection?

I’m going to build the emps/ collection FIRST.

And if we call that –

Nothing special here…what about the actual XML? I don’t want XML bundled up in my JSON, I want JUST the XML.

Wait Jeff, I DO WANT THE XML BUNDLED UP WITH THE JSON! No worries, I’ll show a way to do that next.

Getting the XML content for an employee

This is also fairly simple. We’re going to be using the Media Resource template. This GET handler requires that the first column tell the client what the Mime Type is. And the second column will be the data we stream back, unmolested, directly to the client…in this case our browser (Chrome).

My browser knows it’s XML because we’re telling it to expect XML.

Including the XML elements in our emps/ GET service

It’s just SQL, so we can do whatever we want, right? I know my ‘XML Schema’, so I can hard code this in, something like this.

And calling it gives something like this –

Adding XML content to an existing record

Ok, so I need to add some potpourri data to employee 101 – how do I do that? Well, we’re going to POST up an XML doc, read it in as a CLOB, and then do an UPDATE.

This is great, but writing code is for suckers…what about AUTOREST???

AUTOREST Enabling the TABLE (with XML)

We’re not going to see the XML content on the GET requests. But, we’re going to be able to add and update the records.

We can add a record, complete with XML though!

We’re going to POST up a new record to the collection, and include the XML payload as part of the JSON document.

Takeaways

Yes, ORDS can work with XML. For retrieving it, either use SQL to pull out the required elements for your JSON payloads, OR use the Media Resource template, and return the raw XML back to your clients.

If you’re using AUTOREST on a table, you’ll need to enhance the API with a Module to retrieve your XML content, but you can INSERT and UPDATE records having XML columns just fine.

The Code

Here’s the module you can follow along with my examples, you’ll just need that EMPLOYEES table.

-- Generated by ORDS REST Data Services 21.1.0.r0840007
-- Schema: HR  Date: Wed Apr 21 02:45:49 2021 
-- run this in a REST Enabled Schema, as that USER

BEGIN 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'emps.xml',
      p_base_path      => '/emps_xml/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'employees table with an additional XML column, full CRUD on the records and their accompanying XML docs');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'employee by their ID');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/:id',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select employee_id,
       first_name,
       LAST_NAME,
       EMAIL,
       PHONE_NUMBER,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       COMMISSION_PCT,
       employee_id || ''/XML'' "$xml_content" from EMPS_XML
where EMPLOYEE_ID = :id');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps_flat/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'take the xml elements, flatten them out as individual attributes');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps_flat/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT EMPLOYEE_ID
     , FIRST_NAME
     , LAST_NAME
     , EMAIL
     , PHONE_NUMBER
     , HIRE_DATE
     , JOB_ID
     , SALARY
     , COMMISSION_PCT
     , MANAGER_ID
     , DEPARTMENT_ID
     , EXTRACTVALUE(e.xml_scratchpad, ''/etc/hobbies'') "Hobbies"
     , EXTRACTVALUE(e.xml_scratchpad, ''/etc/originally_from'') "BornIn"
     , EXTRACTVALUE(e.xml_scratchpad, ''/etc/twitter_handle'') "twitter"
     , ''../emps/'' || employee_id || ''/XML'' "$xml_doc"
  FROM emps_xml e
 WHERE xml_scratchPad IS NOT NULL');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'all of the employees');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select employee_id,
       first_name,
       LAST_NAME,
       EMAIL,
       PHONE_NUMBER,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       COMMISSION_PCT,
       employee_id || ''/XML'' "$xml_content" 
  from EMPS_XML
 order by EMPLOYEE_ID asc');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/:id/XML',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'the XML content from the scratchpad column the developers insisted on for ad hoc employee details');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/:id/XML',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select ''application/xml'', xml_scratchpad
from EMPS_XML
where employee_id = :id');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps.xml',
      p_pattern        => 'emps/:id/XML',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
 l_xmlType XMLTYPE;

begin
  l_xmltype := XMLTYPE.createXML(:body_text);
update EMPS_XML
set xml_scratchpad = l_xmltype
where employee_id = :id;

commit;

end;');

    
        
COMMIT;

END;