Database Software

How can I create external table for following XML file?

<?xml version="1.0"?>

<DEF Purpose="Req" version="202.1" CreationTime="2008-08-15 15:00:24" >

<DeviceReadings ID="11" Source="Remote" SourceName="Gatherer 1999"  CollectionTime="2008-08-15 15:00:00">

  <Device DeviceID="11" DeviceName="Device 123" Active="true" SrNo="08011824" DeviceType="Gatherer" Description="08011824" InstallDate="2008-07-09 06:00:00" RemovalDate="" />

  <Clock DeviceTime="2008-08-15 15:00:18" ServerTime="2008-08-15 15:00:05" DaylightAdjustmentInEffectAtDevice="true" Season="summer" ATier="C" />

  <CData>

  <CSpec UOM="kWh" Direction="Delivered" TouBucket="Total" MeasurementPeriod="Current" Multiplier="0.000001"/>

  <Reading TimeStamp="2008-08-15 15:00:18" Value="3154.05495"/>

  </CData>

  <MDData>

  <MDSpec UOM="kW" Direction="Delivered" TouBucket="Total" MeasurementPeriod="Current" Multiplier="0.000001"/>

  <Reading TimeStamp="2008-08-15 08:44:00" Value="2.7009"/>

  </MDData>

  <DemandReset TimeStamp="2008-08-15 15:00:18"/>

  <EventData>

  <EventSpec Type="Demand Reset" Category="Miscellaneous"/>

  <Event DiscoveredAt="2008-08-15 15:00:18" Source="Device Status" EventInfo=" "/>

  </EventData>

  <InstrumentationValue Name="Frequency" Value="59.9984" Timestamp="2008-08-15 15:00:19"/>

  <OutageCountSummary>

  <OutageCount ReadingTime="2008-08-15 15:00:05" Value="12" PreviousReadingTime="2008-08-15 14:55:39" PreviousValue="12"/>

  </OutageCountSummary>

</DeviceReadings>

<DeviceReadings ID="6" Source="Remote" SourceName="Gatherer 6847"  CollectionTime="2008-08-15 15:00:00">

  <Device DeviceID="6" DeviceName="Device 3857" Active="true" SrNo="08302275" DeviceType="REX" Description="REX2-D 08302275" InstallDate="2008-07-28 06:00:00" RemovalDate="" />

  <CData>

  <CSpec UOM="kWh" Direction="Delivered" TouBucket="TierC" MeasurementPeriod="Previous" Multiplier="1"/>

  <Reading TimeStamp="2008-08-15 05:00:00" Value="56600"/>

  </CData>

  <CData>

  <CSpec UOM="kWh" Direction="Delivered" TouBucket="TierA" MeasurementPeriod="Previous" Multiplier="1"/>

  <Reading TimeStamp="2008-08-15 05:00:00" Value="7950"/>

  </CData>

  <DemandResetCount Count="37" TimeStamp="2008-08-15 10:00:00" UOM="Times"/>

  <Statuses>

  <Status Id="61" Name="No Voltage Detected with Disconnect Closed" Category="Service" Type="bool" Value="false"/>

  </Statuses>

  <InstrumentationValue Name="Voltage" Value="223.613746" Timestamp="2008-08-15 05:00:00"/>

  <ReverseEnergySummary>

  <ReverseEnergy CurrentValue="0" PreviousValue="0"/>

  </ReverseEnergySummary>

  <OutageCountSummary>

  <OutageCount ReadingTime="2008-08-15 10:00:00" Value="17" PreviousReadingTime="2008-08-14 10:00:00" PreviousValue="17"/>

  </OutageCountSummary>

</DeviceReadings>

</DEF>

I need following columns data -

1. DeviceName = /DeviceReadings/Device/DeviceName

2. cData_desc = .... /Cdata/CSPec/UOM

3. cData_desc = .... /Cdata/CSPec/Direction

3a. Concat cData = Col2||-||Col3

4. Reading_time = .... /Cdata/Reading/TimeStamp

5. Reading_value = .... /Cdata/Reading/Value

So the table value should be -

I created following table, but it has opening and closing tags (  <id>1</id>)for the columns in XML, but my above xml doesnot have same structure. Please help.

------------------------------------------------------------------------------------------------------------------------------------------

<?xml version="1.0"?>

  <start>

  <recordline>

  <id>1</id>

  <textfile>Total KWH.txt</textfile>

  <graphfile>Total KWH.jpg</graphfile>

  </recordline>

  </start>

CREATE TABLE external_table_xml

  (

  IDENTYFIER NUMBER,

  TEXTFILE VARCHAR2(70),

  GRAPHFILE     VARCHAR2(70)

  )

ORGANIZATION EXTERNAL

  (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY EXT

  ACCESS PARAMETERS

  (

  RECORDS DELIMITED BY "</recordline>"

  BADFILE EXT_LOG:'EXTERNAL_TABLE_XML.bad'

  LOGFILE EXT_LOG:'EXTERNAL_TABLE_XML.log'

  FIELDS MISSING FIELD VALUES ARE NULL

  (

  RECORD_LINE CHAR(2000) TERMINATED BY "<recordline>",

  IDENTYFIER CHAR(4) ENCLOSED BY "<id>" AND "</id>",

  TEXTFILE CHAR(70) ENCLOSED BY "<textfile>" AND "</textfile>",

  GRAPHFILE CHAR(70) ENCLOSED BY "<graphfile>" AND "</graphfile>"

  )

  )

  LOCATION ('TEST.XML')

  )

  PARALLEL

  REJECT LIMIT UNLIMITED;

--------------------------------------------------------------------------------------------------------

I am using Oracle 12c Enterprise Edition

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 12 17:43:52 2016

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt

ions