I have to write a program to read an XML file and update DB relational tables.
The background information is this: We (our customer) is an energy retailer Down Under. Retailer sends a request XML to the Australian Energy Market Operator to get their Network Tariff Codes. The AEMO sends the latest list of Network Tariff Codes in the the response XML. I need to read this XML and insert/Update the codes into our DB table (which is a normal relational table).
In our current code we use EXTRACTVALUE. But after reading the documentation I see that this is deprecated in 12c (which we have). Oracle recommends to use XMLTABLE.
I am not good at all with XML processing since we don't get to do this very often.
I have simplified the XML file (by removing unnecessary tags) below:
(1.) The ReplicationParameters -> TableName will give the AEMO table name.
(2.) The <ReplicationBlock tableName="CATSNetworkTariffCodes"> -> <Row> tag will hold the rows for this table in the Code and Description tags..
(3.) The <Transaction> tag can repeat. AEMO has lots of different tables (not just Network Tariff Codes). So, they can send rows of more than one table in one XML.
(4.) Our framework PL/SQL APIs give us the file in to a variable of type XMLDOM.DOMDocument.
What I want is this:
(1.) In my code when I have a cursor with input parameters iTableName which will be 'CATS_NETWORKTARIFF_CODES' from ReplicationParameters -> TableName. So, it's like this:
CURSOR cNTCHEADER IS
SELECT
FROM (XMLTABLE stuff.....) t
WHERE t.tablename = iTableName ;
(2.) The output of the above cursor when I execute in the code is
FROM TO initiatingTransactionID CreationFromDate EventSeverityCode EventSeverityExplanation
NEMMCO NEMPID NEMPID-TNS-20180523103855554523 2017-05-01T00:00:00+10:00 0 Success
This cursor will just output one row.
(3.) I will then look at the EventSeverityCode and then get at the Network Tariff Code rows.
IF cursor.EventSeverityCode = 0 THEN
FOR c IN cNTCROWS LOOP
-- Process each row
-- If code does not match INSERT, ELSE UPDATE.
END LOOP;
END IF;
CURSOR cNTCRows is the cursor which gets the codes:
CURSOR cNTCRows IS
SELECT
FROM (XMLTABLE stuff.....) t
WHERE t.tablename = 'CATS_NETWORKTARIFF_CODES' from ReplicationParameters -> TableName;
(4.) This cNTCRows cursor will output many rows, 2 in this particular case.
CODE DESCRIPTION
EGB09 West Embedded Generator Site Specific
FLVMKW1R New small business seasonal demand anytime energy
I think you get the idea now.
Appreciate if you could show how to do this using XMLTABLE.
THE XML:
<ase:aseXML xmlns:ase="urn:aseXML:r35" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:aseXML:r35 http://www.nemmco.com.au/aseXML/schemas/r35/aseXML_r35.xsd">
<Header>
<From description="Australian Energy Market Operator Limited">NEMMCO</From>
<To description="EnergyAustralia Yallourn Pty Ltd">NEMPID</To>
<MessageID>NEMMCO-MSG-727037031</MessageID>
</Header>
<Transactions>
<Transaction transactionID="CATS-727037031" transactionDate="2018-05-23T10:40:41+10:00" initiatingTransactionID="NEMPID-TNS-20180523103855554523">
<ReplicationNotification version="r10">
<ReplicationParameters>
<TableName>CATS_NETWORKTARIFF_CODES</TableName>
<CreationFromDate>2017-05-01T00:00:00+10:00</CreationFromDate>
</ReplicationParameters>
<ReplicationBlock tableName="CATSNetworkTariffCodes">
<Row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ase:CATSNetworkTariffCodeRow">
<Code>WEGB09</Code>
<Description>West Embedded Generator Site Specific</Description>
</Row>
<Row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ase:CATSNetworkTariffCodeRow">
<Code>FLVMKW1R</Code>
<Description>New small business seasonal demand anytime energy</Description>
</Row>
</ReplicationBlock>
<Event severity="Information">
<Code>0</Code>
<Explanation>Success</Explanation>
</Event>
</ReplicationNotification>
</Transaction>
</Transactions>
</ase:aseXML>