Loading XML into Oracle’s Autonomous Data Warehouse

  1. XML data is landed/copied in to OCI Object Storage
  2. Copy the Object Storage data to a Binary XML table for this I used the DBMS_CLOUD.GET_OBJECT procedure and an INSERT SQL statement into the binary XML table. This DBMS_CLOUD.GET_OBJECT procedure supports many different URIs including Oracle Cloud Infrastructure, Microsoft Axure, Amazon S3, see the docs here; https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/file-uri-formats.html#GUID-5D3E1614-ADF2-4DB5-B2B2-D5613F10E4FA
  3. Create views on XML using XMLTable — I manually designed them but these could be generated or custom built depending on the use case and what you are trying to do
CREATE TABLE STAGE_XML (xml_col XMLTYPE) XMLTYPE xml_col 
STORE AS SECUREFILE BINARY XML;
-- Insert XML into Binary XML Data Table - the Oracle database will store as binary XML and eliminate reprocessing of the textdeclare  data BLOB;begin  data := DBMS_CLOUD.GET_OBJECT(    credential_name =>'YOUR_CREDENTIAL',      object_uri =>
'https://objectstorage..........oraclecloud.com/n/yournamespace/b/yourbucket/o/yourxml.xml');
execute immediate 'INSERT into STAGE_XML select XMLType(:blobData,873) from dual' USING data;end;
— Query / create views on data - this would be similar to reading PersonDetail etc from HCM extractSELECT proteins.*  FROM STAGE_XML,  XMLTABLE('/ProteinDatabase/ProteinEntry'    PASSING STAGE_XML.xml_col    COLUMNS      "id" varchar2(30) PATH '@id') proteins;
SELECT proteins.*, authors.*  FROM HOLDS_XML,  XMLTABLE('/ProteinDatabase/ProteinEntry'    PASSING STAGE_XML.xml_col    COLUMNS      "id" varchar2(30) PATH '@id',      "name" varchar2(30) PATH 'protein/name',      "organism_source" varchar2(30) PATH 'organism/source',      "organism_common" varchar2(30) PATH 'organism/common',      "authors" XMLTYPE PATH 'reference/refinfo/authors'  ) proteins,  XMLTABLE('authors/author'    PASSING proteins."authors"    COLUMNS      "author" varchar2(30) PATH '.'  ) authors
It’s easy to integrate from lots of different object storage systems.
A dataflow using the views on the XML in Oracle Cloud Infrastructure’s Data Integration

--

--

--

Architect at @Oracle developing cloud services for data. Connect on Twitter @i_m_dave

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

CSS | Display properties

Having me as a critical care medicine specialist

Python and C++ on Windows10

The Great Fleece: Coin Distraction, Part 1

What’s happening with HSE Manager

Difference between Primitive and Non-Primitive data types in JAVA

360° IT Check #2 — LinkedIn’s Security Breach, The Kaseya Hack, Kubernetes Spending, GitHub’s AI…

Is Coding Bootcamp Worth It In 2020?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
David Allan

David Allan

Architect at @Oracle developing cloud services for data. Connect on Twitter @i_m_dave

More from Medium

How to Automate Data Validating and Data Validation Testing

Quickstart for Installing Operator and Deploy Armory Enterprise

Is there a right and wrong in choosing tools to implement an architecture? — Part 1

How Machine Advertising visualises billions of events per day with ClickHouse