This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 20, 2012 2:02 AM by 973769 Go to original post RSS
  • 15. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    - Is it possible to build in a check to see if the 'BatchNumber' is already existing in the database?
    Sure, how about :
    select h.Company
         , h.Supplier
         , h.DeviceType
         , h.BatchNumber
         , x.*
    from tmp_xml t
         , xmltable(
           xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
         , '/Main'
           passing t.object_value
           columns Company           varchar2(20) path '@Company'
                 , Supplier          varchar2(20) path '@Supplier'
                 , DeviceType        varchar2(20) path 'Header/@DeviceType'
                 , BatchNumber       varchar2(20) path 'Header/@BatchNumber'
                 , DeviceAttributes  xmltype      path 'DeviceAttributes'
         ) h
       , xmltable(
           xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
         , '/DeviceAttributes'
           passing h.DeviceAttributes
           columns SerialNumber        number       path '@SerialNumber'
                 , DSMRVersion         varchar2(10) path 'Emeter/DSMRAttributes/@DSMRVersion'
                 , BatteryType         varchar2(30) path 'Emeter/DSMRAttributes/@BatteryType'
                 , EquipmentIdentifier varchar2(20) path 'Emeter/DSMRAttributes/@EquipmentIdentifier'
                 , KemaGastecCode      varchar2(10) path 'Emeter/DSMRAttributes/@KemaGastecCode'
                 , GOVersion           varchar2(10) path 'Emeter/DSMRAttributes/@GOVersion'
         ) x
    where not exists (
      select null
      from dest_table d
      where d.batchnumber = h.batchnumber
    );
    - Would it be possible to let the query process multiple XML files from the same type?
    Yes, just load as many files as you want to process in the XMLType table, then run the query.
  • 16. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Hey thanks, thats what I was looking for.
    For some reason I actually thought I'd have to do that in another new query instead of in the same 'import' query.

    E.
  • 17. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Hi Odie,

    I've tried to use the solution you offered regarding selecting the header AND the record in one select statement. This works brilliantly.
    But, when I create an insert statement with this select script, It messes up all the field values for some reason:

    insert into TEMPORARY_TABLE_01
    (
    company,
    supplier,
    devicetype,
    devicebatchnumber,
    yearofmanufactory,
    serialnumber,
    hardwareversion,
    moduleactivefirmwareversion,
    coreactivefirmwareversion,
    communicationprovider,
    communicationproviderbatchid,
    iccid,
    imsi,
    imei,
    pppauthenticationuser,
    pppauthenticationpassword,
    equipmentidentifier,
    kemagasteccode,
    dsmrversion,
    masterkey,
    globalencryptionunicastkey,
    authenticationkey,
    goversion,
    p0password1,
    hdlcpassword
    )
    select h.Company
    , h.Supplier
    , h.DeviceType
    , h.BatchNumber
    , x.*
    from iskra_tmp_xml t
    , xmltable(
    xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
    , '/Main'
    passing t.object_value
    columns company varchar2(20) path '@Company'
    , Supplier varchar2(20) path '@Supplier'
    , DeviceType varchar2(20) path 'Header/@DeviceType'
    , BatchNumber varchar2(20) path 'Header/@BatchNumber'
    , DeviceAttributes xmltype path 'DeviceAttributes'
    ) h
    , xmltable(
    xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
    , '/DeviceAttributes'
    passing h.DeviceAttributes
    columns yearofmanufactory varchar2(4) path '@YearOfManufactory'
    , coreactivefirmwareversion varchar2(50) path '@CoreFirmwareVersion'
    , serialnumber number path '@SerialNumber'
    , hardwareversion varchar2(50) path '@HardwareVersion'
    , dsmrversion varchar2(10) path 'Emeter/DSMRAttributes/@DSMRVersion'
    , equipmentidentifier varchar2(20) path 'Emeter/DSMRAttributes/@EquipmentIdentifier'
    , kemagasteccode varchar2(10) path 'Emeter/DSMRAttributes/@KemaGastecCode'
    , goversion varchar2(10) path 'Emeter/DSMRAttributes/@GOVersion'
    , globalencryptionunicastkey clob path 'Emeter/GPRS/DlmsAttributes/@GlobalEncryptionKeyUnicast'
    , p0password1 clob path 'Emeter/GPRS/DlmsAttributes/@P0Password1'
    , authenticationkey clob path 'Emeter/GPRS/DlmsAttributes/@AuthenticationKey'
    , hdlcpassword clob path 'Emeter/GPRS/DlmsAttributes/@HDLCPassword'
    , masterkey clob path 'Emeter/GPRS/DlmsAttributes/@MasterKey'
    , msisdn varchar2(20) path 'Emeter/GPRS/GPRSCommunicationAttributes/SimCard/@MSISDN'
    , iccid varchar2(50) path 'Emeter/GPRS/GPRSCommunicationAttributes/SimCard/@ICCID'
    , communicationproviderbatchid varchar2(4) path 'Emeter/GPRS/GPRSCommunicationAttributes/SimCard/@CommunicationProviderBatchId'
    , imsi varchar2(50) path 'Emeter/GPRS/GPRSCommunicationAttributes/SimCard/@IMSI'
    , communicationprovider varchar2(20) path 'Emeter/GPRS/GPRSCommunicationAttributes/SimCard/@CommunicationProvider'
    , imei varchar2(50) path 'Emeter/GPRS/GPRSCommunicationAttributes/Modem/@IMEI'
    , ppppassword clob path 'Emeter/GPRS/GPRSCommunicationAttributes/Modem/@PPPPassword'
    , pppusername clob path 'Emeter/GPRS/GPRSCommunicationAttributes/Modem/@PPPUsername'
    ) x
    ;

    A select on this temporary table results in output with different values for each field, for instance, SerialNumber contains the values of the CoreActiveFirmwareVersion and vice versa (and a couple of others)
    Can you perhaps tell me what could be the reason of this?

    Thanks in advance!
    E.
  • 18. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    I've fixed the issue from my last post, it seemed that I messed up the field order in the insert query hence inserting the wrong values in the wrong fields

    E.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points