Good day!
Here is the setup. I'll be given an xml file from another source that will be calling a stored procedure in my database (which I'm supposed to create). This is a first for me, I've never done pl/sql development or worked with xml. :(
This is what I was hoping to accomplish, if there is a better way I'm open to it.
In short, have a stored proceedure that takes the xml file (passed by third party via the call, not url has to be a file), creates a temporary table and populates the two columns needed. Then the SP will do a select join with the temp table and other data available in another schema. After the select is complete the table will be truncated and dropped. SP closes.
Details:
DB: Oracle 11.2.0.1 RAC.
Charset: AL32UTF8
XML sample: (close to 10,000 rows like this in production)
<Report MerchantID="########" Name="Conversion Detail Report" ReportStartDate="2011-09-27 12:00:00GMT" ReportEndDate="2011-09-27 21:38:13GMT" Version="1.1"><Conversion MerchantReferenceNumber="1234567891234567D" ConversionDate="2011-09-27 13:39:02" RequestID="1234567891234567891234"><OriginalDecision>REVIEW</OriginalDecision><NewDecision>ACCEPT</NewDecision><Reviewer>testusr</Reviewer><ReviewerComments>No reason to reject.</ReviewerComments><Notes><Note Date="2011-09-27 13:39:02" AddedBy="testusr" Comment="Took ownership." /></Notes><Queue>Digital Review</Queue><Profile>Digital Orders QA</Profile></Conversion>
...
</Report>
What I have tried.
I found a tutorial: http://it.toolbox.com/blogs/oracle-guide/oracle-and-xml-in-action-a-real-world-example-5319 Which I tried and was able to make it work. But I could not duplicate the results with my xml file. So I looked elsewhere and have had no success. :( I'm not a developer so I'm really struggling...
I found a support note: (How to Load an XML File Into a Database Table Using DBMS_XMLSave without Using Bfile [ID 227476.1]) and could not get that to work.
I found some xml threads online then from Odie and tried those but fumbled and couldn't get my versions of those great answers to work for me.
So... I'm here now and hoping someone could walk me through this.
I have cobbled together this SP (it doesn't have to be this way, suggestions are greatly appreciated)
create or replace procedure xml_to_table(call_1 call_2) as
begin
execute immediate 'CREATE GLOBAL TEMPORARY TABLE IMRPT OF XMLType
-- (constraint imrpt_pk primary key (MerchantReferenceNumber))
xmltype store as basicfile binary xml
-- (compress high)
virtual columns
(MerchantReferenceNumber as (xmlcast(xmlquery('/Conversion/MerchantReferenceNumber' passing object_value returning content) as VARCHAR2(32)))
,NewDecision as (xmlcast(xmlquery('/Conversion/NewDecision' passing object_value returning content) as VARCHAR2(10)))
)
-- ON COMMIT PRESERVE ROWS'
execute immediate 'store xml as file on the local system?'
insert into IMRPT (MerchantReferenceNumber, NewDecision)
execute immediate 'select join statement'
execute immediate 'truncate table IMRPT'
execute immediate 'drop table IMRTP'
end;
/
On my last try I attempted to just select from the xml file using this:
DECLARE
indomdoc DBMS_XMLDOM.DOMDocument;
innode DBMS_XMLDOM.DOMNode;
myParser DBMS_XMLPARSER.parser;
buf VARCHAR2(2000);
BEGIN
myParser := DBMS_XMLPARSER.newParser;
DBMS_XMLPARSER.setBaseDir(myparser, 'XML_DIR');
DBMS_XMLPARSER.parse(myParser, 'test.xml');
indomdoc := DBMS_XMLPARSER.getDocument(myParser);
-- just to test the content
innode := DBMS_XMLDOM.makeNode(indomdoc);
DBMS_XMLDOM.writeToBuffer(innode, buf);
DBMS_OUTPUT.put_line(buf);
dbms_xmldom.freeDocument(indomdoc);
dbms_xmlparser.freeParser(myParser);
END;
/
That was another crash and burn.
Any help would be appreciated.
Thanks