Transitioning Oracle’s XMLDOM Functions to Amazon Aurora PostgreSQL Utilizing JSON Format

More Info

Migrating from Oracle to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (RDS) for PostgreSQL can present challenges, especially when applications rely on the DBMS_XMLDOM package for accessing XML type objects. This package is not available in Aurora PostgreSQL or Amazon RDS for PostgreSQL, making it difficult to convert procedures that depend on it. The task of adapting these procedures can be lengthy and complex, depending on how many stored procedures utilize the DBMS_XMLDOM package.

In this article, we propose a solution that involves adopting the JSON data type instead of XML to facilitate the migration of stored procedures from Oracle to PostgreSQL, thereby streamlining the migration process. We selected JSON due to its status as the native format for data in JavaScript and PostgreSQL’s robust support for JSON over XML.

Solution Overview

The DBMS_XMLDOM package in Oracle is designed for work with XML type objects and implements the Document Object Model (DOM), which is an API for HTML and XML documents. Our approach involves creating a schema and wrapper package functions in PostgreSQL that mimic Oracle’s DBMS_XMLDOM functionality using the PLV8 extension. PLV8 is a trusted JavaScript-based procedural language extension for PostgreSQL that you can use for stored procedures, triggers, and more.

This solution leverages the JSON data type to replace Oracle’s DBMS_XMLDOM package in PostgreSQL. It is critical to conduct a thorough assessment of your database and associated applications before implementing this solution to ensure that there are no objections to using the JSON format.

Prerequisites

For this guide, we assume you have operational Oracle and PostgreSQL databases. The first steps include creating necessary database objects in PostgreSQL:

1. Create the DBMS_XMLDOM Schema in PostgreSQL:

   CREATE SCHEMA dbms_xmldom AUTHORIZATION postgres;

2. Install the PLV8 Extension:

   CREATE EXTENSION plv8;

   PLV8 is a shared library that provides a PostgreSQL procedural language powered by the V8 JavaScript engine.

3. Create Wrapper Functions Under the DBMS_XMLDOM Schema:

   Below are examples of wrapper functions that you will create:

   • Function to Add an Element:

     CREATE OR REPLACE FUNCTION dbms_xmldom.addelement(
       tempjson json,
       mykey text,
       myval text)
     RETURNS json
     LANGUAGE 'plv8'
     AS $BODY$
     // JavaScript logic to add an element
     $BODY$;

   • Function to Retrieve Node Attributes:

     CREATE OR REPLACE FUNCTION dbms_xmldom.getattributes(
       nodelist json,
       b text)
     RETURNS json
     LANGUAGE 'plv8'
     AS $BODY$
     // JavaScript logic to get attributes
     $BODY$;

   • Function to Retrieve Children by Tag Name:

     CREATE OR REPLACE FUNCTION dbms_xmldom.getchildrenbytagname(
       nodelist json,
       x text)
     RETURNS json
     LANGUAGE 'plv8'
     AS $BODY$
     // JavaScript logic to get children by tag name
     $BODY$;

These functions will help you effectively transition from Oracle’s XML processing to equivalent JSON handling in PostgreSQL. For further insights, feel free to check out this another blog post that covers similar topics.

Remember, adopting the JSON format may present new opportunities, and for more authoritative guidance on this topic, you might want to visit this resource. For best practices regarding employee onboarding at Amazon, refer to this excellent resource.