This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 20, 2012 2:02 AM by 973769 RSS

Issue after succesfully loading xml into 11gr2 database

973769 Newbie
Currently Being Moderated
Hi All,

I'm currently experiencing an issue with a project I'm working on at a customer:

I have succesfully loaded multiple xml files into the Oracle 11gr2 database using sqlloader and since the intention is to be able to query the loaded data, it needs
to be transferred from the xml table to a 'normal' table, which also went ok.

Since most of the XML files contain multiple records, i've used the fn:string-join option in the insert into....select statement and used a ; as delimiter.
This is where I've encountered the thing that I can't seem to find a solution for.
When there's multiple records in the xml file it inserts all the records into one record.

So say i've got an xml file containing info for a bunch of electricity meters consisting of the fields brand, supplier, type for 3 records (actual files can be uploaded if neccesary)

inserting the data by using the above construction gives me the following data in the destination table:

Brand
-------------------------------
BrandA;BrandB;BrandC

Supplier
--------------------------------
Company1;Company2;Company3

Type
---------------------------------
Type1;Type2;Type3


How do I split up the above info so that every separate record from the imported xml file lands in its own record?

Thanks in advance!

Cheers,
E.
  • 1. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    Hi,
    How do I split up the above info so that every separate record from the imported xml file lands in its own record?
    Easy. Don't store it this way in the first place.

    Post a sample XML, I'll show you how to directly load your target table.
  • 2. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Thanks, I'm curious on how you'll demo it

    Here's the sample xml file:

    <?xml version="1.0" encoding="UTF-8"?>
    <!--XML file generated by Protocol Manager-->
    <tns:Main Company="Company" Supplier="Supplier" xmlns:tns="http://schema.dsmr22plus.com/delivery/v1.0.5" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schema.dsmr22plus.com/delivery/v1.0.5 ..\..\..\xmllib\bla\DeliveryFile.xsd">
    <tns:Header DeviceType="ME382-D1 GPRS" BatchNumber="NC2043W_59" /><tns:DeviceAttributes YearOfManufactory="2011" CoreFirmwareVersion="14DA21B4B6F53C47613466AF8C263209" SerialNumber="50727236" OperationalFirmwareVersion="B330EBC763450ADC3E0C11F9BA0EA732" HardwareVersion="20611978">
    <tns:Emeter>
    <tns:DSMRAttributes DSMRVersion="2.3" BatteryType="Supercap type SG" EquipmentIdentifier=" KAL7005072723611" KemaGastecCode="KAL7" GOVersion="1" />
    <tns:GPRS>
    <tns:DlmsAttributes GlobalEncryptionKeyUnicast="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" GlobalEncryptionKeyBroadcast="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" P0Password1="12345678" AuthenticationKey="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" HDLCPassword="12345678" MasterKey="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" />
    <tns:GPRSCommunicationAttributes>
    <tns:SimCard MSISDN="" ICCID="8931084811080544999" CommunicationProviderBatchId="5765" IMSI="204080800339599" CommunicationProvider="KPN" />
    <tns:Modem IMEI="353471006235660" PPPPassword="xxxxxxxxxxxxxxx" PPPUsername="KAL7005072723611" />
    </tns:GPRSCommunicationAttributes>
    </tns:GPRS>
    </tns:Emeter>
    </tns:DeviceAttributes>
    <tns:DeviceAttributes YearOfManufactory="2011" CoreFirmwareVersion="14DA21B4B6F53C47613466AF8C263209" SerialNumber="50727237" OperationalFirmwareVersion="B330EBC763450ADC3E0C11F9BA0EA732" HardwareVersion="20611978">
    <tns:Emeter>
    <tns:DSMRAttributes DSMRVersion="2.3" BatteryType="Supercap type SG" EquipmentIdentifier=" KAL7005072723711" KemaGastecCode="KAL7" GOVersion="1" />
    <tns:GPRS>
    <tns:DlmsAttributes GlobalEncryptionKeyUnicast="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" GlobalEncryptionKeyBroadcast="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" P0Password1="12345678" AuthenticationKey="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" HDLCPassword="12345678" MasterKey="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" />
    <tns:GPRSCommunicationAttributes>
    <tns:SimCard MSISDN="" ICCID="8931084811080544981" CommunicationProviderBatchId="5765" IMSI="204080800339598" CommunicationProvider="KPN" />
    <tns:Modem IMEI="353471006235450" PPPPassword="xxxxxxxxxxxxxxx" PPPUsername="KAL7005072723711" />
    </tns:GPRSCommunicationAttributes>
    </tns:GPRS>
    </tns:Emeter>
    </tns:DeviceAttributes>
    </tns:Main>

    Edited by: user6357028 on 12-Nov-2012 04:55
  • 3. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    So, after loading the xml file(s), will I be able to do 'normal' selects on this table?

    If so, that would be awesome, I'll patiently await your solution....

    E.
  • 4. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    I guess "DeviceAttributes" is the record?
    SQL> insert into tmp_xml
      2  values (xmltype(bfilename('TEST_DIR','main.xml'), nls_charset_id('AL32UTF8')));
     
    1 row inserted
     
    SQL> 
    SQL> select x.*
      2  from tmp_xml t
      3     , xmltable(
      4         xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
      5       , '/Main/DeviceAttributes'
      6         passing t.object_value
      7         columns SerialNumber        number       path '@SerialNumber'
      8               , DSMRVersion         varchar2(10) path 'Emeter/DSMRAttributes/@DSMRVersion'
      9               , BatteryType         varchar2(30) path 'Emeter/DSMRAttributes/@BatteryType'
     10               , EquipmentIdentifier varchar2(20) path 'Emeter/DSMRAttributes/@EquipmentIdentifier'
     11               , KemaGastecCode      varchar2(10) path 'Emeter/DSMRAttributes/@KemaGastecCode'
     12               , GOVersion           varchar2(10) path 'Emeter/DSMRAttributes/@GOVersion'
     13       ) x
     14  ;
     
    SERIALNUMBER DSMRVERSION BATTERYTYPE                    EQUIPMENTIDENTIFIER  KEMAGASTECCODE GOVERSION
    ------------ ----------- ------------------------------ -------------------- -------------- ----------
        50727236 2.3         Supercap type SG                KAL7005072723611    KAL7           1
        50727237 2.3         Supercap type SG                KAL7005072723711    KAL7           1
     
  • 5. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Yep, DeviceAttributes is indeed the record, the info prior to DeviceAttributes is part of the xmlfiles batch

    dude, this is so awesome! merci!
  • 6. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    Also make sure the XMLType table/column uses Binary XML storage to ensure best performance.
  • 7. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    And Odie, whats the table definition for the tmp_xml table you're using?

    Thanks,
    E.
  • 8. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    So as I can see, the thing i've done wrong is how I've declared the part of the 'record'

    Thanks Odie,

    I can now succesfully finish the project at my customer, sometimes it takes an extra pair of eyes (and brain haha) to fix something

    E.
  • 9. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    whats the table definition for the tmp_xml table you're using?
    create table tmp_xml of xmltype
    xmltype store as securefile binary xml
    ;
    Binary XML is the default format starting with 11.2.0.2.
  • 10. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Hi Odie,

    I've succesfully used your example, but the only thing i'd like to add is that the Main/Company, Main/Supplier and Main/Header/DeviceType & Main/Header/BatchNumber will also be added to every record when querying it (this needs to serve as an insert statement for a database)

    As I'm a total noob in terms of working with XML, your help is greatly appreciated!

    E.
  • 11. Re: Issue after succesfully loading xml into 11gr2 database
    odie_63 Guru
    Currently Being Moderated
    You can extract those additional columns in a first XMLTable, then pass the sequence of DeviceAttributes to a second XMLTable :
    SQL> select h.Company
      2       , h.Supplier
      3       , h.DeviceType
      4       , h.BatchNumber
      5       , x.*
      6  from tmp_xml t
      7       , xmltable(
      8         xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
      9       , '/Main'
     10         passing t.object_value
     11         columns Company           varchar2(20) path '@Company'
     12               , Supplier          varchar2(20) path '@Supplier'
     13               , DeviceType        varchar2(20) path 'Header/@DeviceType'
     14               , BatchNumber       varchar2(20) path 'Header/@BatchNumber'
     15               , DeviceAttributes  xmltype      path 'DeviceAttributes'
     16       ) h
     17     , xmltable(
     18         xmlnamespaces(default 'http://schema.dsmr22plus.com/delivery/v1.0.5')
     19       , '/DeviceAttributes'
     20         passing h.DeviceAttributes
     21         columns SerialNumber        number       path '@SerialNumber'
     22               , DSMRVersion         varchar2(10) path 'Emeter/DSMRAttributes/@DSMRVersion'
     23               , BatteryType         varchar2(30) path 'Emeter/DSMRAttributes/@BatteryType'
     24               , EquipmentIdentifier varchar2(20) path 'Emeter/DSMRAttributes/@EquipmentIdentifier'
     25               , KemaGastecCode      varchar2(10) path 'Emeter/DSMRAttributes/@KemaGastecCode'
     26               , GOVersion           varchar2(10) path 'Emeter/DSMRAttributes/@GOVersion'
     27       ) x
     28  ;
     
    COMPANY              SUPPLIER             DEVICETYPE           BATCHNUMBER          SERIALNUMBER DSMRVERSION BATTERYTYPE                    EQUIPMENTIDENTIFIER  KEMAGASTECCODE GOVERSION
    -------------------- -------------------- -------------------- -------------------- ------------ ----------- ------------------------------ -------------------- -------------- ----------
    Company              Supplier             ME382-D1 GPRS        NC2043W_59               50727236 2.3         Supercap type SG                KAL7005072723611    KAL7           1
    Company              Supplier             ME382-D1 GPRS        NC2043W_59               50727237 2.3         Supercap type SG                KAL7005072723711    KAL7           1
     
  • 12. Re: Issue after succesfully loading xml into 11gr2 database
    973769 Newbie
    Currently Being Moderated
    Ah I see, thanks!

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

    Just a couple more questions, I can honestly say that I'm learning a lot from your examples:

    - Is it possible to build in a check to see if the 'BatchNumber' is already existing in the database?
    - Would it be possible to let the query process multiple XML files from the same type?

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

    Just a couple more questions, I can honestly say that I'm learning a lot from your examples:

    - Is it possible to build in a check to see if the 'BatchNumber' is already existing in the database?
    - Would it be possible to let the query process multiple XML files from the same type?

    Thanks,
    E.

    Edited by: user6357028 on 15-Nov-2012 23:52
1 2 Previous Next

Legend

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