Migrate Oracle XMLTYPE Data to Postgres

user_13621477 0
Viewed 552

I have a table in Oracle which contains a data type of XMLTYPE. Can anyone suggest a way of extracting this data out to a file and then loading it into Postgres? I'm struggling to find a way as part of my data migration from Oracle to Postgres. Unfortunately there is no connectivity between source and target so I can't use any foreign tables. Using ora2pg as well is a no go as we will potentially have millions of records it just won't perform.

I have set the below up as a test scenario but I just can't get the data out:

CREATE TABLE xml_test (id number, xml xmltype);
INSERT INTO xml_test VALUES (1, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>'));
INSERT INTO xml_test VALUES (11, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>SOME TEXT</chapter></book>'));
INSERT INTO xml_test VALUES (111, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>"SOME MORE TEXT"</chapter></book>'));
INSERT INTO xml_test values (2, xmltype.createxml('<subject><name>test</name><list><li>a</li><li>b</li></list></subject>'));
INSERT INTO xml_test values (3, xmltype.createxml('<subject><name>test</name><list><li>a</li></list></subject>'));
INSERT INTO xml_test VALUES 
  (4, xmltype('<?xml version="1.0"?>
  <Warehouse>
    <WarehouseId>1</WarehouseId>
    <WarehouseName>Southlake, Texas</WarehouseName>
    <Building>Owned</Building>
    <Area>25000</Area>
    <Docks>2</Docks>
    <DockType>Rear load</DockType>
    <WaterAccess>true</WaterAccess>
    <RailAccess>N</RailAccess>
    <Parking>Street</Parking>
    <VClearance>10</VClearance>
  </Warehouse>'));

Now I have this data in my Oracle table, what's the best way to migrate it into my Postgres database?

xmltype data-migration postgresql oracle
2 Answers

try ora2pg the tool support XMLTYPE Migration

user_149298310

One way could be to encode the XML data as base64. Then export it as a csv file and import it back into the Postgres database.

Function to encode base64:

CREATE OR REPLACE FUNCTION get_base64( fil  IN CLOB )
RETURN CLOB
AS
   res CLOB;
   buf VARCHAR2(19200);
   pos PLS_INTEGER := 1;
   amt PLS_INTEGER := 19200;
   b64 VARCHAR2(32767);
BEGIN
   DBMS_LOB.CREATETEMPORARY( res, FALSE );
   DBMS_LOB.OPEN( res, DBMS_LOB.LOB_READWRITE );
   LOOP
      DBMS_LOB.READ( fil, amt, pos, buf );
      pos := pos + amt;
      b64 := UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE( UTL_RAW.CAST_TO_RAW( buf )));
      DBMS_LOB.WRITEAPPEND( res, LENGTH( b64 ), b64 );
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_LOB.CLOSE( res );
      RETURN res;
END get_base64;
/

Export to csv:

DECLARE
   l_file UTL_FILE.FILE_TYPE;
BEGIN
   l_file := UTL_FILE.FOPEN(location => 'TEMP_DIR', filename => 'test.csv', open_mode => 'w', max_linesize => 32767);
   UTL_FILE.PUT_LINE( l_file, '"id"; "xml_base64"' );
   
   FOR cur IN ( SELECT id,
                       get_base64(xmltype.getclobval(xml)) AS xml_base64
                  FROM xml_test )
   LOOP
      UTL_FILE.PUT_LINE( l_file, '"'||cur.id ||'"; "' || cur.xml_base64 ||'"' );
   END LOOP;
   UTL_FILE.FCLOSE( l_file );
EXCEPTION WHEN OTHERS THEN 
   UTL_FILE.FCLOSE( l_file );
END;
/

Import it into Postgres. You need an additional text column. Then decode the base64 encoded text and parse it as XML.

UPDATE xml_test
   SET xml = XMLPARSE(DOCUMENT convert_from(decode(xml_base64, 'base64'), 'UTF8'));

This should work. Please check out if it is also performant enough.

user_118268690
Related