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

PSPCL Recruitment 2022 — Apply Online for 1690 Posts

How To Fix A Slow WordPress Admin

What to expect in your first week as a Software Developer

Deploying Odoo 14 Using Docker

Amazon EKS Anywhere (K8s)

Raise your hand if you want to quit drinking coffee. Why, though???

KYVE. Test network.

Making Azure Synapse Diagnostics data insightful — Data Collection

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

Databricks with MLflow — Easy to use for implementing end-to-end data science pipeline

Democratizing Data Preparation with AWS Glue DataBrew

The ultimate Machine Learning platform: Github

Sparkify — Churn Prediction with PySpark