XML import to Oracle with SQL Developer -

i import xml file oracle sql sql developer. xml has more 1 node, xml structure:

<"<"spectraexchange>">" <"<"application>">" <"<"sv_sv_id>">"kclong<"<"/sv_sv_id/>">" <"<"ss_ss_id>">"kclong<"<"/ss_ss_id/>">" <"<"ap_name>">"kcstring (64)<"<"/ap_name/>">" <"<"ap_prj_ident>">"kcstring (32)<"<"/ap_prj_ident/>">" <"<"station>">" <"<"tcs_name>">"kcstring (64)<"<"/tcs_name/>">" <"<"tcs_call>">"kcstring (256)<"<"/tcs_call/>">" <"<"horizontal_elevations>">" <"<"horizontal_elevation>">" <"<"he_azimut>">"kcdouble<"<"/he_azimut/>">" <"<"he_elevation>">"kcdouble<"<"/he_elevation/>">" <"<"/horizontal_elevation/>">" <"<"/horizontal_elevations/>">" <"<"transmitter>">" <"<"eqp_equip_name>">"kcstring (128)<"<"/eqp_equip_name/>">" <"<"eqp_equip_type>">"kcstring (16)<"<"/eqp_equip_type/>">" <"<"frequency>">" <"<"efl_freq>">"kcdouble<"<"/efl_freq/>">" <"<"coordinated_frequency>">" <"<"cof_dat>">"kwrdate<"<"/cof_dat/>">" <"<"/coordinated_frequency/>">" <"<"/frequency/>">" <"<"/transmitter/>">" <"<"/station/>">" <"<"/application/>">" <"<"/spectraexchange/>">"  

first created table spectraexchange , want use following commands:

insert tablename(sv_sv_id, ss_ss_id, ap_name, ap_prj_ident ) t (select xmltype(bfilename('test_dir','yourxmlfilename.xml'), nls_charset_id('we8iso8859p1')) xmlcol dual) select extractvalue(value(x),'/application/sv_sv_id') sv_sv_id /* value kclong */ ,extractvalue(value(x),'/application/ss_ss_id') ss_ss_id /* value kclong*/ ,extractvalue(value(x),'application/ap_name') ap_name /* value kcstring (64)*/ ,extractvalue(value(x),'application/ap_prj_ident') ap_prj_ident t,table(xmlsequence(extract(t.xmlcol,'/spectraexchange/application'))) x;   update spectraexchange set tcs_name = extractvalue(value(x),'/application/station/tcs_name'), tcs_call = extractvalue(value(x),'/application/station/tcs_call') t (select xmltype(bfilename('test_dir','yourxmlfilename.xml'), nls_charset_id('we8iso8859p1')) xmlcol dual) t,table(xmlsequence(extract(t.xmlcol,'/spectraexchange/application/station'))) x; 

etc..

and in update session got error message: 00933. 00000 - "sql command not ended"

and tried update session:

update spectraexchange set tcs_name = x.tcs_name, tcs_call = x.tcs_call t (select xmltype(bfilename('test_dir','yourxmlfilename.xml'), nls_charset_id('we8iso8859p1')) xmlcol dual) select extractvalue(value(x),'application/station/tcs_name') tcs_name ,extractvalue(value(x),'application/station/tcs_call') tcs_call t,table(xmlsequence(extract(t.xmlcol,'/spectraexchange/application/station'))) x;  

but unfortunatly doesn't work... can me update part? or give me other tutorial import xml more 1 node. thanks. milan

you're trying update 2 columns subquery, syntax wrong; should more like:

update tablename set (col1 = val1, col2 = val2) select (val1, val 2 ...) 

in case this, assuming you're inserting , updating same table, , passing (modified) raw xml sql*plus var testing:

create table spectraexchange(sv_sv_id varchar2(15), ss_ss_id varchar2(15),     ap_name varchar2(15), ap_prj_ident varchar2(15),     tcs_name varchar2(15), tcs_call varchar2(15));  table created.  insert spectraexchange(sv_sv_id, ss_ss_id, ap_name, ap_prj_ident) select extractvalue(value(x), 'application/sv_sv_id') sv_sv_id,     extractvalue(value(x), 'application/ss_ss_id') ss_ss_id,     extractvalue(value(x), 'application/ap_name') ap_name,     extractvalue(value(x), 'application/ap_prj_ident') ap_prj_ident (     select xmltype(:raw_xml) xmlcol dual ) t cross join table(xmlsequence(extract(t.xmlcol,     '/spectraexchange/application'))) x;  1 row created.  select * spectraexchange;  sv_sv_id        ss_ss_id        ap_name         ap_prj_ident    tcs_name        tcs_call --------------- --------------- --------------- --------------- --------------- --------------- kclong          kclong          kcstring (64)   kcstring (32) 

then update be:

update spectraexchange set (tcs_name, tcs_call) = (     select extractvalue(value(x), 'station/tcs_name'),         extractvalue(value(x), 'station/tcs_call')     (         select xmltype(:raw_xml) xmlcol dual     ) t     cross join table(xmlsequence(extract(t.xmlcol,         '/spectraexchange/application/station'))) x );  1 row updated.  select * spectraexchange;  sv_sv_id        ss_ss_id        ap_name         ap_prj_ident    tcs_name        tcs_call --------------- --------------- --------------- --------------- --------------- --------------- kclong          kclong          kcstring (64)   kcstring (32)   kcstring (64)   kcstring (256) 

if course assumes 1 station per application, otherwise you'll need multiple joined tables hold relationships guess; , 1 application or update need correlated somehow. update seems pointless, on insert:

insert spectraexchange(sv_sv_id, ss_ss_id, ap_name, ap_prj_ident,     tcs_name, tcs_call) select extractvalue(value(x), 'application/sv_sv_id') sv_sv_id,     extractvalue(value(x), 'application/ss_ss_id') ss_ss_id,     extractvalue(value(x), 'application/ap_name') ap_name,     extractvalue(value(x), 'application/ap_prj_ident') ap_prj_ident,     extractvalue(value(x), 'application/station/tcs_name') tcs_name,     extractvalue(value(x), 'application/station/tcs_call') tcs_call (     select xmltype(:raw_xml) xmlcol dual ) t cross join table(xmlsequence(extract(t.xmlcol,     '/spectraexchange/application'))) x; 

... (which works one-to-one relationships) i'm missing picture.

---

based on comments have one-to-many relationships , you're inserting 1 table (!?), can instead:

insert spectra exchange ( ... columns ... ) select a.sv_sv_id, a.ss_ss_id, a.ap_name, a.ap_prj_ident,     s.tcs_name, s.tcs_call,     t.eqp_equip_name, t.eqp_equip_type (select xmltype(:raw_xml) xmlcol dual) r cross join xmltable('/spectraexchange/application' passing r.xmlcol         columns sv_sv_id varchar2(15) path 'sv_sv_id',             ss_ss_id varchar2(15) path 'ss_ss_id',             ap_name varchar2(15) path 'ap_name',             ap_prj_ident varchar2(15) path 'ap_prj_ident',             stations xmltype path 'station'     ) (+) cross join xmltable('/station' passing a.stations         columns tcs_name varchar2(15) path 'tcs_name',             tcs_call varchar2(15) path 'tcs_call',             transmitter xmltype path 'transmitter'     ) (+) s cross join xmltable('/transmitter' passing s.transmitter         columns eqp_equip_name varchar2(15) path 'eqp_equip_name',             eqp_equip_type varchar2(15) path 'eqp_equip_type',             frequency xmltype path 'frequency'     ) (+) t / 

i've gone down level transmitter, , can repeat pattern add more, passing relevant node down each time. outer joins (+) allow things not existing, e.g. if have transmitter hasn't been given frequency yet, or whatever - you'll null in relevant columns.