How to extract data from an XML file using PL / SQL

advertisements

How to extract data from this XML with PL/SQL?

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<!DOCTYPE BPS SYSTEM "bpml.dtd">
<BPS Created="2012-04-24 11:40:41">
<Machine SerialNumber="" Site="" SoftwareRelease="MAP_248204031" VersionInfo="" Name="" Type="BPS200">
  <ParameterSection Number="6" StartTime="2012-04-24 11:23:01" EndTime="1970-01-01 00:00:00">
    <HeadercardUnit HeaderCardID="1706539" DepositID="01706539" StartTime="2012-04-24 11:39:57" MilliSec="0" EndTime="2012-04-24 11:40:40" Rejects="NO">
      <Counter Currency="HRK" DenomID="22550" Value="200" Quality="Fit" Output="Stacked" Number="192"></Counter>
      <Counter Currency="HRK" DenomID="22550" Value="200" Quality="Unfit" Output="Stacked" Number="7"></Counter>
    </HeadercardUnit>
  </ParameterSection>
</Machine>
</BPS>

The XML is in DB kept as CLOB. I need to extract the following values from the XML:

  • currency
  • DenomID
  • value
  • quality
  • output
  • ...

You need to be able to parse that XML in PL/SQL. There are built-in packages for doing exactly that. After you parse the XML, you can use the extract function to get data from it.

DECLARE
  x XMLType := XMLType(
    '<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
     <!DOCTYPE BPS SYSTEM "bpml.dtd">
     <BPS Created="2012-04-24 11:40:41">
         <Machine SerialNumber="" Site="" SoftwareRelease="MAP_248204031" VersionInfo="" Name="" Type="BPS200">
             <ParameterSection Number="6" StartTime="2012-04-24 11:23:01" EndTime="1970-01-01 00:00:00">
                 <HeadercardUnit HeaderCardID="1706539" DepositID="01706539" StartTime="2012-04-24 11:39:57" MilliSec="0" EndTime="2012-04-24 11:40:40" Rejects="NO">
                     <Counter Currency="HRK" DenomID="22550" Value="200" Quality="Fit" Output="Stacked" Number="192"></Counter>
                     <Counter Currency="HRK" DenomID="22550" Value="200" Quality="Unfit" Output="Stacked" Number="7"></Counter>
                 </HeadercardUnit>
             </ParameterSection>
         </Machine>
     </BPS>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(Value(p),'/@Currency') as currency
          ,ExtractValue(Value(p),'/@DenomID') as denomid
          ,ExtractValue(Value(p),'/@Value') as val
    FROM   TABLE(XMLSequence(Extract(x,'/BPS/Machine/ParameterSection/HeaderCardUnit/Counter'))) p
    ) LOOP
    -- you can use values for r.currency, r.denomid, r.val
    NULL;
  END LOOP;
END;