SQL & PL/SQL

I am working with our small team to extract data out of a CLOB filed where the data is stored in a non standard XML format where the tag name is within the general tag. I am new to Oracle and am out of my element on how to extract parts of the XML so we can use the data. This is part of a large 4 million row legacy table that we are trying to convert.

RECORD # 1

C_ID:         @123456

DATE:       '03/31/2019 11:04:57 AM'

IMAGE:

<?xml version="1.0"?>

<record name="ImportPaymentFile">

<field name="Status">PAID</field>

<field name="FileName">Batch03.31.2019.csv</field>

<field name="RecordsTotalDollarValue">22111.99</field>

<field name="RecordsDuplicated">0</field>

<field name="RecordsTotal">10</field>

<field name="RecordsRejected">0</field>

<field name="RecordsImported">10</field>

<field name="RuleName">INTER DAY - Payment File</field>

</record>

I assumed that all of the XML formats would be the same so I used this method.

SELECT

,C_ID

,DATE

          --,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[1]/text()').getStringVal() AS Status

          --,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[2]/text()').getStringVal() AS FileName

          --,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[3]/text()').getStringVal() AS RecordsTotalDollarValue

          --,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[5]/text()').getStringVal() AS RecordsDuplicated

,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[6]/text()').getStringVal() AS RecordsTotal

,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[7]/text()').getStringVal() AS RecordsImported

,XMLTYPE(alb.BEFOREIMAGE).EXTRACT('//field[8]/text()').getStringVal() AS RuleName

FROM AUDITLOG

Then I discovered one row that did not have the same XML format

RECORD # 1117

C_ID:                     123456

DATE:                    '03/31/2019 9:37:19 AM'

IMAGE:

<?xml version="1.0"?>

<record name="ImportPaymentFile">

   <field name="FileName"> DailyPayments20190319REG.csv </field>

   <field name="RuleName"> UP CO CP v01</field>

   <field name="RecordsTotal">195</field>

   <field name="RecordsTotalDollarValue">22111.99</field>

   <field name="RecordsDuplicated">0</field>

   <field name="RecordsRejected">0</field>

   <field name="RecordsImported">195</field>

</record>

What I am trying to accomplish is a single method to I can extract the field name and the value and then return the results into a table like this below:

Any help in extracting the field name from within the XML generic ‘field’ tag?

Thanks for the help.

C--