使用JSON格式将Oracle的XMLDOM包函数迁移到Amazon Aurora PostgreSQL

如果应用程序使用DBMS_XMLDOM包访问XML类型的对象，那么从Oracle迁移到Amazon Aurora PostgreSQL Compatible Edition或Amazon Relational Database Service（Amazon RDS）for PostgreQL可能会很困难。这是因为Aurora PostgreSQL或Amazon RDS for PostgreQL不支持DBMS_XMLDOM包。此外，根据依赖于DBMS_XMLDOM包的存储过程的数量，将过程转换为Aurora PostgreSQL或Amazon RDS for PostgreQL可能是一个繁琐且耗时的过程。

在本文中，我们讨论了一种使用JSON数据类型而不是XML将存储过程从Oracle迁移到PostgreSQL并减少迁移工作量的解决方案。我们选择JSON是因为JSON是JavaScript中数据的本地格式，与XML相比，PostgreSQL对JSON有广泛的支持。

解决方案概述

Oracle中的DBMS_XMLDOM包用于访问XML类型的对象，并实现文档对象模型（DOM），这是HTML和XML文档的应用程序编程接口。

在这个解决方案中，我们使用PLV8扩展在PostgreSQL数据库中创建与Oracle等效的DBMS_XMLDOM模式和包装程序包函数。PLV8是PostgreSQL的可信JavaScript语言扩展，可以用于存储过程、触发器等。

此解决方案使用JSON数据类型而不是XML来将Oracle的DBMS_XMLDOM包转换为PostgreSQL。因此，在继续使用此解决方案之前，请确保彻底评估数据库及其相关应用程序，并确保没有人反对或担心使用JSON数据类型。

先决条件

假设您已经启动并运行了Oracle和PostgreSQL数据库，请继续执行以下步骤。

在开始之前，您必须在PostgreSQL中创建几个数据库对象：

1.在PostgreSQL中创建DBMS_XMLDOM模式：

CREATE SCHEMA dbms_xmldom AUTHORIZATION postgres;

2.安装plv8扩展：

CREATE EXTENSION plv8;

PLV8是一个共享库，它提供了由V8 JavaScript引擎支持的PostgreSQL过程语言。

3.在DBMS_XMLDOM模式下创建以下包装函数：

• a.包装器函数dbms_xmldom。addelement（此函数将新元素添加到文档中）：

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$;

• b.包装器函数dbms_xmldom.getattributes（此函数用于检索节点的属性）：

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$;

• c.包装器函数dbms_xmldom.getchildrenbytagname（返回文档的子级）：

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$;

• d.包装器函数dbms_xmldom.getelementbytagname（按标记名返回子树中的元素）：

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$;

• e.包装器函数dbms_xmldom.getlength（检索地图中的项目数）：

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$;

• f.包装器函数dbms_xmldom.getnodename（检索节点的名称）

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$;

• g.包装器函数dbms_xmldom.getnodevalue（检索节点的值）：

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$;

• h.包装器函数dbms_xmldom.item（检索给定映射中索引的项）：

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$;

• i.包装器函数dbms_xmldom.setattribute（设置由名称指定的属性；函数由TEXT、INTEGER和DOUBLE PRECISION数据类型重载）：

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$;

示例1：比较节点数

让我们使用DBMS_XMLDOM。GETLENGTH函数返回列表中的节点数，并比较Oracle和PostgreSQL之间的输出。

在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;

将得到以下输出:

为了在PostgreSQL的GETLENGTH函数中调用DBMS_XMLDOM.GETLENGTH，我们必须首先将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"
    }]

在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 $$;

将得到以下输出：

示例2：生成XML文档

让我们创建一个存储过程，使用DBMS_XMLDOM包函数生成XML文档。

此过程涵盖以下DBMS_XMLDOM包函数：

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

以下Oracle过程代码生成XML文档：

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;

以下是等效的PostgreSQL过程代码：

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$;

测试用例1

在以下Oracle调用语句中，xmlParams为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 ;

以下屏幕截图显示了我们的输出。

以下是等效的PostgreSQL调用语句：

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 $$;

将得到以下输出：

测试用例2

在以下Oracle调用语句中，xmlParams包含一个值：

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 ;

以下屏幕截图显示了我们的输出。

以下代码是等效的PostgreSQL调用语句：

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 $$;

我们得到以下输出。

示例3：处理XML文档

让我们创建另一个存储过程来处理XML文档。此过程接受Generate_XML过程创建的XML文档作为输入参数，读取所有项，并将其存储在TRANSACTION_DETAILS表中。

此过程涵盖以下DBMS_XMLDOM包函数：

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

让我们在数据库中创建TRANSACTION_DETAILS表。

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 
);

以下Oracle代码创建Process_XML过程：

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;

以下过程代码是PostgreSQL的等效代码：

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$;

测试用例3

在以下Oracle调用语句中，xmlParams为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 ;

我们得到以下输出。

以下代码是PostgreSQL的等效代码：

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 $$;

我们得到以下输出。

测试用例4

在以下Oracle调用语句中，xmlParams包含一个值：

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 ;

以下屏幕截图显示了我们的输出。

要验证Oracle中的表记录，请运行以下查询：

SELECT * FROM TRANSACTION_DETAILS;

您将得到以下输出。

以下call语句是PostgreSQL的等效语句：

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 $$;

我们得到以下输出。

使用以下查询验证PostgreSQL中的表记录：

SELECT * FROM TRANSACTION_DETAILS;

您将得到以下输出。

结论

在本文中，我们向您展示了使用JSON数据类型将Oracle的DBMS_XMLDOM包函数迁移到Aurora PostgreSQL或Amazon RDS for PostgreQL的解决方案。

如果您有任何问题或评论，请在评论部分分享您的想法。

关于作者

Jitendra Kumar是AWS专业服务的首席数据库迁移顾问。他帮助客户迁移AWS Cloud中的工作负载并使其现代化，特别关注现代应用程序架构和开发最佳实践。

Sai Parthasaradhi是AWS专业服务的数据库迁移专家。他与客户密切合作，帮助他们迁移AWS上的数据库并使其现代化。

原文标题：Migrate Oracle’s XMLDOM package functions to Amazon Aurora PostgreSQL using JSON format
原文作者：Jitendra Kumar and Sai Parthasaradhi
原文链接：https://aws.amazon.com/blogs/database/migrate-oracles-xmldom-package-functions-to-amazon-aurora-postgresql-using-json-format/