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
5 thoughts on “Read XML data from URL and insert into Oracle”
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:
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 !
Are you looking any row? You can try to have where rownum < 2;
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!
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
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
Have you tried to validate this XML? I tried this using http://www.validome.org/xml/validate/ and got error.