importing XML data in Oracle DB -

i'm new in dbadministration , have been asked design db structure starting big (8gb) xml files. building structure, , finished.

i'm testing importing of data xml tables. have stored content of file column in table, when try export 1 column value, have no results (and no errors).

here code:

create table testtable2 ( xml_file xmltype ) xmltype xml_file store securefile binary xml;  insert testtable2 (xml_file)  (select xmltype(bfilename('export_dumps','test001.xml'), nls_charset_id('we8iso8859p1')) dual );   select 'cd_uid' xmltable('xml/records/rec/uid' passing (select xml_file testtable2)             columns cd_uid varchar2(4000));            

the xml starts this:

<?xml version="1.0" encoding="utf-8"?>   <records xmlns="http://xxxxxxxxxxxxxx">  <rec r_id_disclaimer="yyyyy"> <uid>uid_number</uid> 

i have tried extracting data directly xml file, have stored in folder oracle server using below code: works without inserted rows.

insert testtable(cd_uid) t (select xmltype(bfilename('export_dumps','test001.xml'), nls_charset_id('we8iso8859p1')) xmlcol dual) select extractvalue(value(x),'rec/uid') cd_uid            t,table(xmlsequence(extract(t.xmlcol,'/records/rec'))) x; 

i'm wondering if structure of xml file has impact on importing procedure. mean: in structure have code referring value in table, in xml have directly value name..)

i'v tried using xmlspy trying convert , export db, doesn't create relationships between tables.

is here can me finding solution , driving through it?

thanks lot!

you can use an xmltable clause data in relational form:

select x.cd_uid 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 namespace in records node need include via xmlnamespaces clause; have 1 i've made default don't have clutter xpath references it.