importing XML data in Oracle DB

Question

I'm new in DBAdministration and I have been asked to design the db structure starting from big (8Gb) xml files. I an building the structure, and it is almost finished.

I'm testing the importing of the data from the XML into the tables. I have stored the content of the file into a column in a table, but when I try to export one column value, I have no results (and no errors).

Here the code:

CREATE TABLE TESTTABLE2 ( xml_file XMLTYPE ) XMLTYPE xml_file STORE as securefile binary xml;

INSERT INTO TESTTABLE2 (xml_file) 
(SELECT XMLTYPE(bfilename('EXPORT_DUMPS','test001.xml'), nls_charset_id('WE8ISO8859P1')) from dual );


SELECT 'CD_UID'
FROM XMLTABLE('XML/records/REC/UID' passing (SELECT xml_file FROM TESTTABLE2) 
           COLUMNS CD_UID VARCHAR2(4000));

The XML starts like this:

<?xml version="1.0" encoding="UTF-8"?> 

<records xmlns="http://xxxxxxxxxxxxxx">

<REC r_id_disclaimer="yyyyy">
<UID>UID_number</UID>

I have also tried extracting all the data directly from the XML file, which I have stored in a folder into the Oracle server using the below code: It also works but without inserted rows.

INSERT INTO TESTTABLE(CD_UID)
WITH t AS (SELECT xmltype(bfilename('EXPORT_DUMPS','test001.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
SELECT
extractValue(value(x),'REC/UID') as CD_UID           
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/records/REC'))) x;

I'm also wondering if the structure of the XML file has any impact on the importing procedure. I mean: in my structure I have a code which is referring to a value which is in another table, but in the XML I have directly the value name..)

I'v also tried using XMLSpy trying to convert and Export to a DB, but it doesn't create any relationships between the tables.

Is here anybody that can help me finding a solution and driving be through it?

Thanks a lot!

So did you commit? What does "select count(*) from TESTTABLE2" return? — OldProgrammer, Jul 14 '15 at 14:41

score 0 · Accepted Answer · answered Aug 12 '15 at 10:11

You can use an XMLTable clause to get the data in a relational form:

select x.cd_uid
from testtable2 t
cross join xmltable(xmlnamespaces(default 'http://xxxxxxxxxxxxxx'),
  '/records/REC'
  passing t.xml_file 
  columns cd_uid varchar2(20) path 'UID'
) x;

CD_UID             
--------------------
UID_number

You've got a namespace in the records node so you need to include that via an xmlnamespaces clause; as you only have one I've made it the default so you don't have to clutter up the XPath with references to it.

Thank you very much. It works. I'm tryng now to add also the other fields to the select, for example f_name (mentioned in the other topic).. — newinIT, Aug 12 '15 at 12:32

importing XML data in Oracle DB

1 Answers1

Linked