Migrate Oracle’s XMLDOM package functions to Amazon Aurora PostgreSQL using JSON format

Migrating from Oracle to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL may be challenging if the application is using the DBMS_XMLDOM package to access XML type objects. This is because the DBMS_XMLDOM package isn’t supported in Aurora PostgreSQL or Amazon RDS for PostgreSQL. Moreover, converting procedures to Aurora PostgreSQL or Amazon RDS for PostgreSQL could be a cumbersome and time-consuming process depending on the amount of stored procedures dependent on the DBMS_XMLDOM package.

In this post, we discuss a solution that uses the JSON data type instead of XML to migrate stored procedures from Oracle to PostgreSQL and reduce migration efforts. We chose JSON because JSON is the native format for data in JavaScript, and PostgreSQL has extensive support of JSON compared to XML.

Overview of solution

The DBMS_XMLDOM package in Oracle is used to access XML type objects and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents.

In this solution, we create the DBMS_XMLDOM schema and wrapper package functions equivalent to Oracle in a PostgreSQL database using the PLV8 extension. PLV8 is a trusted JavaScript language extension for PostgreSQL that you can use for stored procedures, triggers, and more.

This solution uses the JSON data type instead of XML to convert Oracle’s DBMS_XMLDOM package to PostgreSQL. Therefore, make sure to complete a thorough assessment of the database and its dependent applications before proceeding with this solution and make sure there are no objections or concerns to using the JSON data type.

Prerequisites

Assuming that you have an Oracle and a PostgreSQL databases up and running, proceed with the following steps.

Before getting started, you have to create several database objects in PostgreSQL:

Create the DBMS_XMLDOM schema in PostgreSQL:

CREATE SCHEMA dbms_xmldom AUTHORIZATION postgres;

Install the plv8 extension:
```
CREATE EXTENSION plv8;
```
PLV8 is a shared library that provides a PostgreSQL procedural language powered by the V8 JavaScript engine.

Create the following wrapper functions under the DBMS_XMLDOM schema:

Wrapper function dbms_xmldom.addelement (this function adds new element into the document):

CREATE OR REPLACE FUNCTION dbms_xmldom.addelement(
  tempjson json,
  mykey text,
  myval text)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
  let tempObj = {};
  tempObj[keyName] = val;
  myjson[Object.keys(myjson)[0]] = tempObj;
}

else if(typeof(tj) === 'object'){
  myjson[Object.keys(myjson)[0]][keyName] = val;
}

else if(typeof(tj) === 'string'){
let tempObj1 = {};
tempObj1['#text'] = tj;
tempObj1[keyName] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}

return myjson;
$BODY$;

Wrapper function dbms_xmldom.getattributes (this function retrieves the attributes of the node):

CREATE OR REPLACE FUNCTION dbms_xmldom.getattributes(
    nodelist json,
    b text)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let response = {};

if (nodelist == null) {
    return null
}   
else
    {
        let attrJson=Object.values(nodelist)[0];
        Object.entries(attrJson).forEach(([keyname,val])=>{
                                         if(keyname.indexOf(b) !== -1)
                                         { keyname = keyname.substring(1);
                                         response[keyname]=val;}
                                     });
    }

return response;
$BODY$;

Wrapper function dbms_xmldom.getchildrenbytagname (returns the children of the document):

CREATE OR REPLACE FUNCTION dbms_xmldom.getchildrenbytagname(
  nodelist json,
  x text)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let res = {};
  let nodename = x;
  for (const [keyname, val] of Object.entries(nodelist)) {
    if(keyname === nodename){
    res[keyname] = val;
    return res;
    }
  }
$BODY$;

Wrapper function dbms_xmldom.getelementbytagname (returns the element in the subtree by the tag name):

CREATE OR REPLACE FUNCTION dbms_xmldom.getelementbytagname(
    nodelist json,
    b text)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let response = {};

if (nodelist == null) {
    return null
}
else {
    let attrJson=Object.values(nodelist)[0];
    Object.entries(attrJson).forEach(([keyname,val])=>{
                                     if(keyname.indexOf(b) !== -1)
                                     {response[keyname]=val;}
                                 });
}                                
return response;
$BODY$;

Wrapper function dbms_xmldom.getlength (retrieves the number of items in the map):

CREATE OR REPLACE FUNCTION dbms_xmldom.getlength(
    nodelist json)
    RETURNS integer
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
if (nodelist == null)
   return 0
else 
    return Object.keys(nodelist).length
$BODY$;

Wrapper function dbms_xmldom.getnodename (retrieves the name of the node):

CREATE OR REPLACE FUNCTION dbms_xmldom.getnodename(
    nodelist json)
    RETURNS text
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
return Object.keys(nodelist)[0];
$BODY$;

Wrapper function dbms_xmldom.getnodevalue (retrieves the value of the node):

CREATE OR REPLACE FUNCTION dbms_xmldom.getnodevalue(
    nodelist json)
    RETURNS text
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
return Object.values(nodelist)[0];
$BODY$;

Wrapper function dbms_xmldom.item (retrieves the item given the index in the map):

CREATE OR REPLACE FUNCTION dbms_xmldom.item(
    nodelist json,
    index integer)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let Obj = {};
let keyName = Object.keys(nodelist)[index-1];
let val =  Object.values(nodelist)[index-1];
Obj[keyName] = val;
return Obj;
$BODY$;

Wrapper function dbms_xmldom.setattribute (sets the attribute specified by name; the function is overloaded with TEXT, INTEGER, and DOUBLE PRECISION data types):

CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
  tempjson json,
  mykey text,
  myval text)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
  let tempObj = {};
  tempObj[`@${keyName}`] = val;
  myjson[Object.keys(myjson)[0]] = tempObj;
}

else if(typeof(tj) === 'object'){
  myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}

else if(typeof(tj) === 'string'){
let tempObj1 = {};
tempObj1['#text'] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}

return myjson;
$BODY$;

CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
  tempjson json,
  mykey text,
  myval integer)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
  let tempObj = {};
  tempObj[`@${keyName}`] = val;
  myjson[Object.keys(myjson)[0]] = tempObj;
}

else if(typeof(tj) === 'object'){
  myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}

else if(typeof(tj) === 'string'){
let tempObj1 = {};
tempObj1['#text'] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}

return myjson;
$BODY$;

CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
  tempjson json,
  mykey text,
  myval double precision)
    RETURNS json
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
  let tempObj = {};
  tempObj[`@${keyName}`] = val;
  myjson[Object.keys(myjson)[0]] = tempObj;
}

else if(typeof(tj) === 'object'){
  myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}

else if(typeof(tj) === 'string'){
let tempObj1 = {};
tempObj1['#text'] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}

return myjson;
$BODY$;

Example 1: Compare number of nodes

Let’s use the DBMS_XMLDOM.GETLENGTH function to return the number of nodes in the list and compare the output between Oracle and PostgreSQL.

Run the following code block in Oracle:

DECLARE
    xmlDocument  dbms_xmldom.DOMDocument;
    xmlParams    xmltype := xmltype('<Deptartments>
                                         <Dept DeptID="10">
                                             <Name>Administration</Name>
                                             <Location>Seattle</Location>
                                         </Dept>
                                         <Dept DeptID="20">
                                            <Name>Marketing</Name>
                                            <Location>Toronto</Location>
                                         </Dept>
                                    </Deptartments>');
    domNode     dbms_xmldom.DOMNode; 
    domElement  dbms_xmldom.DOMElement;
    nodeList    dbms_xmldom.DOMNodeList;
    v_getlenght int;
BEGIN
    xmlDocument := dbms_xmldom.newDOMDocument(xmlParams); 
    domElement  := dbms_xmldom.getdocumentelement(xmlDocument);  
    domNode     := dbms_xmldom.makeNode(domElement); 
    nodeList    := dbms_xmldom.getchildnodes(domNode);  
    select dbms_xmldom.getlength(nodeList) INTO v_getlenght from dual;
    
    dbms_output.put_line('v_getlenght = '||to_char(v_getlenght)); 
END;

You get the following output.

In order to call the DBMS_XMLDOM.GETLENGTH function in PostgreSQL, we have to first convert the XML to JSON format.

XML JSON

<Deptartments>
     <Dept DeptID="10">
         <Name>Administration</Name>
         <Location>Seattle</Location>
     </Dept>
   <Dept DeptID="20">
         <Name>Marketing</Name>
         <Location>Toronto</Location>
      </Dept>
  </Deptartments>

[{
    "@DeptID": "10",
      "Name": "Administration",
      "Location": "Seattle"
    },
    {
    "@DeptID": "20",
      "Name": "Marketing",
      "Location": "Toronto"
    }]

Run the following code in PostgreSQL:

DO $$ 
<<first_block>>
DECLARE
    xmlParams  json := '[
                        {
                            "@DeptID": "10",
                            "Name": "Administration",
                            "Location": "Seattle"
                        },
                        {
                            "@DeptID": "20",
                            "Name": "Marketing",
                            "Location": "Toronto"
                        }
                        ]';
    v_getlenght int;                                
BEGIN
    select dbms_xmldom.getlength(xmlParams) into v_getlenght;
    raise notice 'v_getlenght=%', v_getlenght;

END first_block $$;

You get the following output.

Example 2: Generate an XML document

Let’s create a stored procedure to generate an XML document using the DBMS_XMLDOM package function.

This procedure covers the following DBMS_XMLDOM package functions:

dbms_xmldom.getdocumentelement
dbms_xmldom.makeNode
dbms_xmldom.makeNode
dbms_xmldom.createElement
dbms_xmldom.appendChild
dbms_xmldom.setattribute

The following Oracle procedure code generates the XML document:

CREATE OR REPLACE NONEDITIONABLE FUNCTION FN_DateToPlSqlXml (theDate DATE) RETURN VARCHAR2 IS
BEGIN 
  RETURN(to_char(theDate, 'yyyy-mm-dd') || 'T' || to_char(theDate, 'hh24:mi:ss')); 
END;

CREATE OR REPLACE PROCEDURE Generate_XML 
(
  tranID             NUMBER,  
  transDate          DATE,
  transAmt           NUMBER,            
  blobText1          VARCHAR2,
  blobText2          VARCHAR2,
  xmlParams          CLOB,
  xmlDocument        IN OUT NOCOPY dbms_xmldom.DOMDocument
) IS
cdataNode          dbms_xmldom.DOMCdataSection; 
xmlNode            dbms_xmldom.DOMNode; 
nodeList           dbms_xmldom.DOMNodeList; 

mydocNode          dbms_xmldom.DOMNode; 
mydocElement       dbms_xmldom.DOMElement; 

queueNode          dbms_xmldom.DOMNode; 
queueElement       dbms_xmldom.DOMElement; 

blobNode           dbms_xmldom.DOMNode; 
blobElement        dbms_xmldom.DOMElement; 

BEGIN 
 /* Create main document with root tag */ 

  IF NOT dbms_xmldom.isnull(xmlDocument) THEN 
    mydocElement := dbms_xmldom.getdocumentelement(xmlDocument); 
    mydocNode    := dbms_xmldom.makeNode(mydocElement); 
  ELSIF xmlParams IS NOT NULL THEN 
    xmlDocument    := dbms_xmldom.newDOMDocument(xmlParams); 
    mydocElement := dbms_xmldom.getdocumentelement(xmlDocument); 
    mydocNode    := dbms_xmldom.makeNode(mydocElement); 
  ELSE 
    xmlDocument    := dbms_xmldom.newDOMDocument; 
    xmlNode        := dbms_xmldom.makeNode(xmlDocument); 
    mydocElement := dbms_xmldom.createElement(xmlDocument, 'MYDOC'); 
    mydocNode    := dbms_xmldom.appendChild(xmlNode, dbms_xmldom.makeNode(mydocElement));  
  END IF; 

  /*--------------------------*/ 
  /* Create XML structure */ 
  /*--------------------------*/ 
  nodeList := dbms_xmldom.getchildrenbytagname(mydocElement, 'PROCESS_QUEUE'); 
  IF dbms_xmldom.getlength(nodeList) = 0 THEN 
    queueElement := dbms_xmldom.createElement(xmlDocument, 'PROCESS_QUEUE'); 
    queueNode    := dbms_xmldom.appendChild(mydocNode, dbms_xmldom.makeNode(queueElement));  
  ELSE 
    queueNode    := dbms_xmldom.item(nodeList, 0); 
    queueElement := dbms_xmldom.makeelement(queueNode); 
  END IF; 

  IF tranID IS NOT NULL THEN 
    dbms_xmldom.setattribute(queueElement, 'tranID', tranID); 
  END IF; 
  IF transDate IS NOT NULL THEN 
    dbms_xmldom.setattribute(queueElement, 'transDate', FN_DateToPlSqlXml(transDate)); 
  END IF; 
  IF transAmt IS NOT NULL THEN 
    dbms_xmldom.setattribute(queueElement, 'transAmt', transAmt); 
  END IF; 

  IF blobText1 IS NOT NULL THEN 
    blobElement := dbms_xmldom.createElement(xmlDocument, 'BLOB_TEXT1'); 
    blobNode    := dbms_xmldom.appendChild(queueNode, dbms_xmldom.makeNode(blobElement));  
    cdataNode   := dbms_xmldom.createcdatasection(xmlDocument, blobText1); 
    xmlNode     := dbms_xmldom.appendChild(blobNode, dbms_xmldom.makeNode(cdataNode)); 
  END IF; 

  IF blobText2 IS NOT NULL THEN 
    blobElement := dbms_xmldom.createElement(xmlDocument, 'BLOB_TEXT2'); 
    blobNode    := dbms_xmldom.appendChild(queueNode, dbms_xmldom.makeNode(blobElement));  
    cdataNode   := dbms_xmldom.createcdatasection(xmlDocument, blobText2); 
    xmlNode     := dbms_xmldom.appendChild(blobNode, dbms_xmldom.makeNode(cdataNode)); 
  END IF; 

  Return; 
END;

The following is the equivalent PostgreSQL procedure code:

CREATE OR REPLACE FUNCTION fn_datetoplsqlxml(
    thedate timestamp )
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    RETURN concat(TO_CHAR(current_date, 'yyyy-mm-dd'),'T', to_char(current_timestamp, 'HH24:MI:SS'));
END;
$BODY$;

CREATE OR REPLACE PROCEDURE Generate_XML(
    tranID                   integer,
    transDate             timestamp,
    transAmt              double precision,
    blobtext1             varchar(200),
    blobtext2             varchar(200),
    xmlparams           text,
    INOUT xmldocument   json)
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE jsndocument JSON;
BEGIN
    IF dbms_xmldom.getlength(xmldocument) > 0 THEN
        jsndocument := xmldocument ;
    ELSEIF xmlParams IS NOT NULL THEN
        jsndocument := json(xmlParams);
    ELSE
        jsndocument := '{}';
    end IF;

    jsndocument := dbms_xmldom.getchildrenbytagname(jsndocument,'PROCESS_QUEUE');
    If dbms_xmldom.getlength(jsndocument) = 0 THEN
        jsndocument := '{"PROCESS_QUEUE" : ""}';
    end IF;

    IF tranID IS NOT NULL THEN 
        jsndocument := dbms_xmldom.setattribute(jsndocument, 'tranID', tranID); 
    END IF; 
    IF transDate IS NOT NULL THEN 
        jsndocument = dbms_xmldom.setattribute(jsndocument, 'transDate', fn_datetoplsqlxml(transDate)); 
    END IF; 
    IF transAmt IS NOT NULL THEN 
        jsndocument := dbms_xmldom.setattribute(jsndocument,  'transAmt', transAmt); 
    END IF; 

    IF blobText1 IS NOT NULL THEN 
        jsndocument := dbms_xmldom.addelement(jsndocument,'BLOB_TEXT1',blobText1);
    END if; 
    IF blobText2 IS NOT NULL THEN 
        jsndocument := dbms_xmldom.addelement(jsndocument,'BLOB_TEXT2',blobText2);
    END IF; 

    xmldocument := jsndocument;
    RETURN;
END;
$BODY$;

Test case 1

In the following Oracle call statement, xmlParams is NULL:

set serveroutput on;

declare     
    tranID NUMBER           := 11011;
    transDate DATE          := sysdate;
    transAmt NUMBER         := 999.99;       
    blobText1 VARCHAR(200)  := 'Credit Card <Refer charges below>';
    blobText2 VARCHAR(200)  := '#SBI Bank Charges @2.25%, Others @3.95%';
    xmlParams CLOB          := NULL;
    xmlDocument dbms_xmldom.DOMDocument;
    vClob         CLOB;
begin    
    Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
   
    --print the xml 
    dbms_lob.createtemporary(vClob, false); 
    dbms_xmldom.writetoclob( doc => xmlDocument, cl => vClob); 
    dbms_output.put_line(vClob); 
end ;

The following screenshot shows our output.

The following is the equivalent PostgreSQL call statement:

DO $$ 
<<first_block>>
    declare tranID integer             := 11011;
    declare transDate timestamp        := current_date;
    declare transAmt double precision  := 999.99;       
    declare blobText1 varchar(200)     := 'Credit Card <Refer charges below>';
    declare blobText2 varchar(200)     := '#SBI Bank Charges @2.25%, Others @3.95%';
    declare xmlParams text             := NULL;
    declare xmlDocument json;
    declare jsndocument json;

BEGIN
    call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2, xmlParams, jsndocument);
    raise notice '%',jsndocument;

END first_block $$;

You get the following output.

Test case 2

In the following Oracle call statement, xmlParams contains a value:

set serveroutput on;

declare     
    tranID       NUMBER        := 11022;
    transDate    DATE          := '04-Jul-2022';
    transAmt     NUMBER        := 888.88;       
    blobText1    VARCHAR(200)  := 'Credit Card <Refer charges below>';
    blobText2    VARCHAR(200)  := '#SBI Bank Charges @2.25%, Others @3.95%';
    xmlParams    CLOB          := '<ROOT companyID = "9999" >
                                    <PROCESS_QUEUE totalAmt = "1000">
                                    </PROCESS_QUEUE>
                                </ROOT>';
    xmlDocument dbms_xmldom.DOMDocument;
    vClob        CLOB;
begin    
    Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
   
    --print the xml 
    dbms_lob.createtemporary(vClob, false); 
    dbms_xmldom.writetoclob( doc => xmlDocument, cl => vClob); 
    dbms_output.put_line(vClob); 
end ;

The following screenshot shows our output.

The following code is the equivalent PostgreSQL call statement:

DO $$ 
<<first_block>>
    declare tranID integer             := 11022;
    declare transDate timestamp        := '04-Jul-2022';
    declare transAmt double precision  := 888.88;       
    declare blobText1 varchar(200)     := 'Credit Card <Refer charges below>';
    declare blobText2 varchar(200)     := '#SBI Bank Charges @2.25%, Others @3.95%';
    declare xmlParams text             := '{"@companyID": "9999",
                                             "PROCESS_QUEUE": {"@totalAmt": "1000"}
                                            }';
    declare xmlDocument json;
    declare jsndocument json;

BEGIN
    call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2, 
         xmlParams, jsndocument);
    raise notice '%',jsndocument;

END first_block $$;

We get the following output.

Example 3: Process the XML document

Let’s create another stored procedure to process the XML document. This procedure accepts the XML document created by the Generate_XML procedure as the input parameter, reads all the items, and stores them in the TRANSACTION_DETAILS table.

This procedure covers the following DBMS_XMLDOM package functions:

dbms_xmldom.getattributes
dbms_xmldom.getnodevalue
dbms_xmldom.item
dbms_xmldom.getelementbytagname
dbms_xmldom.getlength

Let’s create the TRANSACTION_DETAILS table in our databases.

Oracle PostgreSQL

CREATE  TABLE TRANSACTION_DETAILS
(
    tranID      NUMBER NOT NULL PRIMARY KEY,
    transDate   DATE NOT NULL,
    transAmt    NUMBER,
    totalAmt    NUMBER,
    blobText1   CHAR, 
    blobText2   CHAR,
    modified_time timestamp default sysdate 
);

CREATE  TABLE TRANSACTION_DETAILS
(
    tranID         INTEGER NOT NULL PRIMARY KEY,
    transDate   DATE NOT NULL,
    transAmt    DOUBLE PRECISION,
    totalAmt    DOUBLE PRECISION,
    blobText1   TEXT, 
    blobText2   TEXT,
    modified_time TIMESTAMP default CURRENT_TIMESTAMP 
);

The following Oracle code creates the Process_XML procedure:

CREATE OR REPLACE PROCEDURE Process_XML
    (
        xmlDocument     IN dbms_xmldom.DOMDocument,
        errCode         OUT NUMBER,
        errMessage      OUT VARCHAR2
    ) AS

xactdocElement       dbms_xmldom.DOMElement; 
xactdocNode          dbms_xmldom.DOMNode; 
queueNode            dbms_xmldom.DOMNode; 
blob1Node            dbms_xmldom.DOMNode; 
blob2Node            dbms_xmldom.DOMNode; 

attributeList         dbms_xmldom.DOMNamedNodeMap; 
attributeIndex        NUMBER; 
attributeNode         dbms_xmldom.DOMNode; 

nodeList              dbms_xmldom.DOMNodeList; 
nodeIndex             NUMBER; 
nodeNode              dbms_xmldom.DOMNode; 

v_tranID              NUMBER;
v_transDate           DATE;
v_transAmt            NUMBER;
v_totalAmt            NUMBER;   
v_blobText1           VARCHAR2(200);
v_blobText2           VARCHAR2(200);
 
BEGIN 
  xactdocElement := dbms_xmldom.getdocumentelement(xmlDocument); 
  xactdocNode    := dbms_xmldom.makeNode(xactdocElement); 
 
  nodeList := dbms_xmldom.getchildnodes(xactdocNode); 
 
  FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP 
    nodeNode      := dbms_xmldom.item(nodeList, nodeIndex -1); 
    attributeList := dbms_xmldom.getattributes(nodeNode); 
   
    CASE dbms_xmldom.getnodename(nodeNode) 
    WHEN 'PROCESS_QUEUE' THEN 
      queueNode := nodeNode; 
 
      FOR attributeIndex IN 1..dbms_xmldom.getlength(attributeList) LOOP 
        attributeNode := dbms_xmldom.item(attributeList, attributeIndex-1); 
 
        CASE dbms_xmldom.getnodename(attributeNode) 
        WHEN 'tranID' THEN 
            v_tranID := dbms_xmldom.getnodevalue(attributeNode); 
        WHEN 'totalAmt' THEN 
            v_totalAmt := dbms_xmldom.getnodevalue(attributeNode);            
        WHEN 'transDate' THEN 
            v_transDate := XN_DateFromPlSqlXml(dbms_xmldom.getnodevalue(attributeNode)); 
        WHEN 'transAmt' THEN 
            v_transAmt := dbms_xmldom.getnodevalue(attributeNode); 
        ELSE 
          NULL; 
        END CASE; 
      END LOOP; 
    ELSE 
      NULL; 
    END CASE; 
  END LOOP; 
 
  /*----------------------*/ 
  /* Get the CLOBs */ 
  /*----------------------*/
  IF NOT dbms_xmldom.isnull(queueNode) THEN 
    nodeList := dbms_xmldom.getchildnodes(queueNode); 
 
    FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP 
      nodeNode      := dbms_xmldom.item(nodeList, nodeIndex -1); 
      attributeList := dbms_xmldom.getattributes(nodeNode); 
 
      CASE dbms_xmldom.getnodename(nodeNode) 
      WHEN 'BLOB_TEXT1' THEN 
        blob1Node := nodeNode; 
      WHEN 'BLOB_TEXT2' THEN 
        blob2Node := nodeNode; 
      ELSE 
        NULL; 
      END CASE; 
    END LOOP; 
  END IF; 

  IF NOT dbms_xmldom.isnull(blob1Node) THEN 
    nodeList := dbms_xmldom.getchildnodes(blob1Node); 
    FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP 
      nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1); 
      IF dbms_xmldom.getnodetype(nodeNode) = dbms_xmldom.CDATA_SECTION_NODE THEN 
         v_blobText1  := Substr(dbms_xmldom.getnodevalue(nodeNode),1,200); 
         EXIT; 
      END IF; 
    END LOOP; 
  END IF;  
  
  IF NOT dbms_xmldom.isnull(blob2Node) THEN 
    nodeList := dbms_xmldom.getchildnodes(blob2Node); 
    FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP 
      nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1); 
      IF dbms_xmldom.getnodetype(nodeNode) = dbms_xmldom.CDATA_SECTION_NODE THEN 
         v_blobText2  := Substr(dbms_xmldom.getnodevalue(nodeNode),1,200); 
         EXIT; 
      END IF; 
    END LOOP; 
  END IF;    
   
  
  INSERT INTO TRANSACTION_DETAILS(tranID,transDate,transAmt,totalAmt,blobText1,blobText2)
  VALUES (v_tranID,v_transDate,v_transAmt,v_totalAmt,v_blobText1,v_blobText2);
  
  errCode       := SQLCODE;
  errMessage    := SUBSTR(SQLERRM, 1 , 64);
 
  RETURN; 
END;

The following procedure code is the PostgreSQL equivalent:

CREATE OR REPLACE PROCEDURE public.process_xml
(
    xmldocument            JSON,
    INOUT err_code         INTEGER,
    INOUT err_message      TEXT
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    jsndocument            JSON;
    attributelist          JSON;
    attributeNode          JSON;
    nodelist               JSON;

    v_tranID               INTEGER;
    v_transDate            TIMESTAMP;
    v_transAmt             DOUBLE PRECISION;
    v_totalAmt             DOUBLE PRECISION;   
    v_blobText1            VARCHAR(200);
    v_blobText2            VARCHAR(200);
BEGIN
    attributelist := dbms_xmldom.getattributes(xmldocument, '@');

    FOR attributeIndex IN 1..dbms_xmldom.getlength(attributeList) LOOP 
        attributeNode := dbms_xmldom.item(attributeList, attributeIndex);
      CASE dbms_xmldom.getnodename(attributeNode) 
          WHEN 'tranID' THEN 
            v_tranID := dbms_xmldom.getnodevalue(attributeNode); 
          WHEN 'transDate' THEN 
            v_transDate := dbms_xmldom.getnodevalue(attributeNode); 
          WHEN 'transAmt' THEN 
            v_transAmt := dbms_xmldom.getnodevalue(attributeNode); 
          WHEN 'totalAmt' THEN 
            v_totalAmt := dbms_xmldom.getnodevalue(attributeNode); 
          ELSE 
            NULL; 
      END CASE; 
    END LOOP; 
        
    nodelist := dbms_xmldom.getelementbytagname(xmldocument,'BLOB_TEXT1');
    IF nodelist IS NOT NULL Then 
      v_blobText1 := Substr(dbms_xmldom.getnodevalue(nodelist),1,200); 
    END IF;  
    
    nodelist := dbms_xmldom.getelementbytagname(xmldocument,'BLOB_TEXT2');
    IF nodelist IS NOT NULL Then 
      v_blobText2 := Substr(dbms_xmldom.getnodevalue(nodelist),1,200); 
    END IF; 
    
    BEGIN
      INSERT INTO TRANSACTION_DETAILS(tranID,transDate,transAmt,totalAmt,
                                      blobText1,blobText2)
      VALUES (v_tranID,v_transDate,v_transAmt,v_totalAmt,v_blobText1,v_blobText2);
      err_code := 0; 
      err_message := 'Record inserted successfully';
    
      EXCEPTION
            WHEN unique_violation THEN
            err_code := 23505;
          GET STACKED DIAGNOSTICS
            err_message := MESSAGE_TEXT;
    END;

    RETURN;
END;
$BODY$;

Test case 3

In the following Oracle call statement, xmlParams is NULL:

set serveroutput on;

declare     
    tranID          NUMBER        := 11011;
    transDate       DATE          := sysdate;
    transAmt        NUMBER        := 999.99;       
    blobText1       VARCHAR(200)  := 'Credit Card <Refer charges below>';
    blobText2       VARCHAR(200)  := '#SBI Bank Charges @2.25%, Others @3.95%';
    xmlParams       CLOB          := NULL;
    xmlDocument     dbms_xmldom.DOMDocument;
    o_errCode       NUMBER;
    o_errMessage    VARCHAR2(64); 
begin    
    Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
    Process_XML (xmlDocument,o_errCode,o_errMessage);   
   
    DBMS_OUTPUT.PUT_LINE('o_errCode:= ' || o_errCode || ', o_errMessage := ' || o_errMessage);    
  
end ;

We get the following output.

The following code is the PostgreSQL equivalent:

DO $$ 
<<first_block>>
    declare tranID integer             := 11011;
    declare transDate timestamp        := current_date;
    declare transAmt double precision  := 999.99;       
    declare blobText1 varchar(200)     := 'Credit Card <Refer charges below>';
    declare blobText2 varchar(200)     := '#SBI Bank Charges @2.25%, Others @3.95%';
    declare xmlParams text             := NULL;
    declare xmlDocument json;
    declare jsndocument json;
    
    declare o_err_code integer;
    declare o_err_message text;

BEGIN
    call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2, 
                       xmlParams, jsndocument);
    call Process_XML (jsndocument, o_err_code,o_err_message);
    raise notice 'o_err_code = %, o_errmessage=%',o_err_code,o_err_message ;
    
END first_block $$;

We get the following output.

Test case 4

In the following Oracle call statement, xmlParams contains a value:

set serveroutput on;

declare     
    tranID             NUMBER             := 11022;
    transDate       DATE                    := '04-Jul-2022';
    transAmt        NUMBER             := 888.88;       
    blobText1       VARCHAR(200)  := 'Credit Card <Refer charges below>';
    blobText2       VARCHAR(200)  := '#SBI Bank Charges @2.25%, Others @3.95%';
    xmlParams     CLOB                    := '<ROOT companyID = "9999" >
                                                                 <PROCESS_QUEUE totalAmt = "1000">
                                                                 </PROCESS_QUEUE>
                                                               </ROOT>';
    xmlDocument     dbms_xmldom.DOMDocument;
    o_errCode           NUMBER;
    o_errMessage    VARCHAR2(64); 
begin    
    Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
    Process_XML (xmlDocument,o_errCode,o_errMessage);   
   
    DBMS_OUTPUT.PUT_LINE('o_errCode:= ' || o_errCode || ', o_errMessage := ' || o_errMessage);    
end ;

The following screenshot shows our output.

To verify the table record in Oracle, run the following query:

SELECT * FROM TRANSACTION_DETAILS;

You get the following output.

The following call statement is the PostgreSQL equivalent:

DO $$ 
<<first_block>>
    declare tranID integer             := 11022;
    declare transDate timestamp        := '04-Jul-2022';
    declare transAmt double precision  := 888.88;       
    declare blobText1 varchar(200)     := 'Credit Card <Refer charges below>';
    declare blobText2 varchar(200)     := '#SBI Bank Charges @2.25%, Others @3.95%';
    declare xmlParams text             := '{"@companyID": "9999",
                                             "PROCESS_QUEUE": {"@totalAmt": "1000"}
                                            }';
    declare jsndocument json;                                           
    declare o_err_code integer;
    declare o_err_message text;

BEGIN
    call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2, 
                      xmlParams, jsndocument);
    call Process_XML (jsndocument, o_err_code,o_err_message);
    raise notice 'o_err_code = %, o_errmessage=%',o_err_code,o_err_message ;
    
END first_block $$;

We get the following output.

Verify the table record in PostgreSQL with the following query:

SELECT * FROM TRANSACTION_DETAILS;

You get the following output.

Conclusion

In this post, we showed you a solution to migrate Oracle’s DBMS_XMLDOM package functions to Aurora PostgreSQL or Amazon RDS for PostgreSQL using the JSON data type.

If you have any questions or comments, share your thoughts in the comment section.

About the Authors

Jitendra Kumar is a Lead Database Migration Consultant with AWS Professional Services. He helps customers migrate and modernize workloads in the AWS Cloud, with a special focus on modern application architectures and development best practices.

Sai Parthasaradhi is a Database Migration Specialist with AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.

AWS Database Blog

Migrate Oracle’s XMLDOM package functions to Amazon Aurora PostgreSQL using JSON format

Overview of solution

Prerequisites

Example 1: Compare number of nodes

Example 2: Generate an XML document

Test case 1

Test case 2

Example 3: Process the XML document

Test case 3

Test case 4

Conclusion

About the Authors

Resources

Blog Topics

Follow