[SOLVED] importing XML data in Oracle DB

importing XML data in Oracle DB

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!

Solution

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.