Oct 27 2007

Read XML data from URL and insert into Oracle

Category: URL,XMLittichai @ 8:26 pm

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

Tags: , ,

5 Responses to “Read XML data from URL and insert into Oracle”

  1. Rajkumar says:

    Impressive !

    It is retrieving the whole xml as a bunch which I wouldn’t want it to do. What am I supposed to do if I want to retrieve only the first row. For example:

    one……..
    two……..

    In the above xml, I want only the first entry which has the title one, but statement shouldn’t mention ‘one’ anywhere as the data would be dynamic.

    Please let me know this ASAP.

    Thank You !

  2. BrazilExpat says:

    Hi there, I came across you on google and this is a well written post. I put it in my bookmarks and will be sharing it with a couple of pals. Looking forward to any revisions you might do. Thanks for the great site! Time to head back to this amazing beach!

  3. Dushan says:

    I got error after
    insert into xml_lekari_tab
    select sys.xmltype.createXML(u.url.getClob()) from url_tab u;

    from url: http://www.fzo.org.mk/XML/LekariLista_SitePzz.xml

    error is:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00210: expected ‘<' instead of '
    Error at line 1
    ORA-06512: at "SYS.XMLTYPE", line 5

    suggestion?
    Thanks.

Leave a Reply