Read XML data from URL and insert into Oracle

There are lot of articles (example) talking about how to read a physical XML file and insert into Oracle database. Here is a little bit different. This shows you how to read XML directly from web’s URL then insert into a database table without involving a physical XML file.

To manage the list of URLs, I create a table containing list of URLs. There will be only one URL in this example. But you can use this table to manage multiple URLs if needed.

CREATE TABLE url_tab
(
  URL_NAME VARCHAR2(100),
  URL      SYS.URIType
);

Insert the URL into this table. Note that URL will go to where column type is SYS.URIType.

SQL> INSERT INTO url_tab VALUES
('This is a test URL',
sys.UriFactory.getUri('http://www.domain.com/test.xml')
);

SQL> commit;

This is the sample of the test.xml file.

The XML_DATA_TAB is a target table containing a XMLTYPE column where data will be loaded into.

SQL> CREATE TABLE xml_data_tab ( xml_data xmltype );

Table created.

If there are multiple URLs, obviously you have to loop through all in the list to load data one URL at the time. But in this case, there is only one URL, so here is how to read XML data from an URL and then convert into XMLType data.

insert into xml_data_tab
select sys.xmltype.createXML(u.url.getClob()) from url_tab u;

* Note that if you’re running on the 11g database, you might run into an error on ACL. Please consult Metalink note 453786.1. Watch out for “&nbs” keyword in step #5 box #2. Remove this typo before execution.

SQL> insert into xml_data_tabselect sys.xmltype.createXML(u.url.getClob()) from url_tab u;

select sys.xmltype.createXML(u.url.getClob()) from url_tab u
             *
ERROR at line 2:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.HTTPURITYPE", line 34

After the fix –

SQL>  insert into xml_data_tabselect sys.xmltype.createXML(u.url.getClob()) from url_tab u;
1 row created.

SQL> commit;
Commit complete.

Note that there is no mechanism for any error checking. You can add yourself if needed. So it is assumed that XML is well-formed.

These are sample queries to extract data from xml_data column.

Retrieve value of attribute –

SQL> select extractvalue(xml_data, '/crm_data/@product') product_name from xml_data_tab;

PRODUCT_NAME
-----------------------------------
electronics

Retrieve value of element –

SQL> select
extractvalue(xml_data, '/crm_data/customer/first_name') first_name,
extractvalue(xml_data, '/crm_data/customer/last_name') last_name,
extractvalue(xml_data, '/crm_data/customer/address') address
from xml_data_tab;

FIRST_NAME           LAST_NAME            ADDRESS
-------------------- -------------------- --------------------
John                 Doe                  12345 Lala lane