The Oracle Autonomous Database (ADB) is truly a multi-model database, making it the ideal place to store and retrieve all types of data your business may use. Last year, I wrote a post about data loading, walking through examples of moving various types of unstructured and semi-structured data, lying in the Oracle object store, into your ADB. In recent weeks, we have had users reach out asking for similar examples loading XML data into ADB via the object store, so here we go...
The steps for loading semi-structured XML type data are easy and similar to our previous example for loading JSON data into Autonomous Database using the DBMS_CLOUD package.
Before we get into the example, some prerequisites to follow the example below are:
Make sure you have a running ADB instance with a little storage space and a user with object store access. If you haven’t done this already you can follow Lab 1 in the ADB Quickstart workshop.
Click here to download the example XML datafile we will use below, and upload this file to a bucket in your Object Store. If you need exact steps on how to upload files to the Oracle Object Store, follow Lab 3 Step 4 in the ADB Quickstart workshop. You may also use AWS or Azure object stores if you prefer, and may refer to the documentation for more information on this.
You will provide the URL of the XML file lying in your object store to the DBMS_CLOUD function call. If you already created your object store bucket’s URL in the lab you may use that, else you may copy the URL from your "Object Details" in the Object Store bucket's UI console. The format, if you would prefer to construct the URL yourself, is below; replace the placeholders <region_name>, <tenancy_name> and <bucket_name> with your object store bucket’s region, tenancy and bucket names.
Since my previous post we released SQL Developer Web, which makes it quick and easy to connect to your database and start running scripts. On your database's UI console, go to the "Tools" tab and click SQL Developer Web and login to your database as we proceed.
We begin by authenticating your database to connect to the object store, with a user credential. We create one using the DBMS_CLOUD.CREATE_CREDENTIAL procedure. Please refer this article for details on where to generate an auth token for your user.
--Please fill in your OCI Object Store username and auth token below
credential_name => 'OBJ_STORE_CRED',
username => 'firstname.lastname@example.org',
password => 'm4F;.]:2JxJO..wepofwMUvg'
Have a look at the "xmlfile.xml" datafile we uploaded to our object store bucket (in the pre-requisites above). Notice, it contains a purchase order document of a user "Sam" and several line items.
We use the DBMS_CLOUD package to create an external table (here we name it "STAGING_TABLE"), with a column of type CLOB, over our datafile in the object store. We input the object store URL in the "file_uri_list" parameter.
If your file has multiple XML documents, each document should be separated by a unique delimiter; in our script below we use the unique recorddelimiter "|" (pipe).
set define on
table_name =>'STAGING_TABLE', credential_name =>'OBJ_STORE_CRED',
format => json_object('recorddelimiter' value '''|'''),
file_uri_list =>' https://objectstorage.ca-toronto-1.oraclecloud.com/n/adwc4pm/b/xmldata/o/xmlfile.xml',
column_list => 'xml_document clob',
field_list => 'xml_document CHAR(80000)'
We can now immediately query our XML file lying in the object store via the external table CLOB column, using native database XML features such as XPATH expressions.
SELECT EXTRACTVALUE(XMLTYPE(xml_document),'/PurchaseOrder/Actions/Action/User') as Users from STAGING_TABLE;
Lastly, we can proceed to move the XML data into a column in ADB, by simply extracting each XML document into a table with an XMLTYPE (or CLOB) column, and using the powerful XML features in the database to parse and query them.
--Saving the XML data into a into an XMLTYPE column
CREATE TABLE xml_table (xml_document xmltype);
INSERT INTO xml_table (SELECT XMLTYPE(xml_document) from STAGING_TABLE);
Note: We currently support XMLType columns, not tables, in ADB on Shared Infrastructure. Registering XML schema is not supported. Refer any XML DB restrictions here.
This example should give the steps you need to quickly ingest and query your XML data in ADB. The Autonomous Database also support spatial data and Oracle Text allowing you to full-text index, optimize and analyze your semi-structured data and get the business value you want out of it.
See you in the next one!