XML import to Oracle

Question

I would like to import an XML file to Oracle SQL with SQL developer. The XML has more than one node, my 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>
<RECEIVER>
<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>
</RECEIVER>
</STATION>
</APPLICATION>
</SPECTRAEXCHANGE> 

First I created the table SPECTRAEXCHANGE and than I used @Alex Poole's solution:

insert into 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
from (select xmltype(:raw_xml) xmlcol from 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'
    ) (+) a
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
/

And the station has transmitter and receiver path and how can I import the RECEIVER path also in one table?

i created one table, and i want to store everything in one table. one application can contain more station, one station can contain more transmitter and one trasmitter can have more freqvency. i created one table, and i want to store everything in one table

Answer 1

I mentioned in the previous answer that you could go down another further levels

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 tran_equip_name, t.eqp_equip_type tran_equip_type,
    tf.efl_freq tran_efl_freq,
    r.eqp_equip_name recv_equip_name, r.eqp_equip_type recv_equip_type,
    rf.efl_freq recv_efl_freq
from (select xmltype(:raw_xml) xmlcol from 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'
    ) (+) a
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',
            receiver xmltype path 'RECEIVER'
    ) (+) 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
cross join xmltable('/FREQUENCY' passing t.frequency
        columns efl_freq varchar2(15) path 'EFL_FREQ'
    ) (+) tf
cross join xmltable('/RECEIVER' passing s.receiver
        columns eqp_equip_name varchar2(15) path 'EQP_EQUIP_NAME',
            eqp_equip_type varchar2(15) path 'EQP_EQUIP_TYPE',
            frequency xmltype path 'FREQUENCY'
    ) (+) r
cross join xmltable('/FREQUENCY' passing t.frequency
        columns efl_freq varchar2(15) path 'EFL_FREQ'
    ) (+) rf
/

Which with your sample data (admittedly with one-to-one relationships so you'd need to test to make sure it does what you want):

SV_SV_ID        SS_SS_ID        AP_NAME         AP_PRJ_IDENT    TCS_NAME        TCS_CALL        TRAN_EQUIP_NAME TRAN_EQUIP_TYPE TRAN_EFL_FREQ   RECV_EQUIP_NAME RECV_EQUIP_TYPE RECV_EFL_FREQ
--------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
kClong          kClong          kCstring (64)   kCstring (32)   kCstring (64)   kCstring (256)  kCstring (128)  kCstring (16)   kCdouble        kCstring (128)  kCstring (16)   kCdouble

So it's basically the same as the previous one, except at station level it's getting receiver as well as transmitter and then expanding that through another join r; and I've expanded the frequency section for both with tf and rf.

If you need the coordinated frequency you can include that XML object in the frequency section and add another cross-join below each one. You just need to be a bit careful with naming and aliases to avoid confusion; my single-letter aliases were kind of OK originally but you probably want something more descriptive now it's getting more complicated.

(Storing everything in one flat table still seems an odd thing to do if there are one-to-many relationships...)