Major Help on XML Parsing
From: ExecMan <artmerar_at_yahoo.com>
Date: Wed, 25 Apr 2012 08:00:51 -0700 (PDT)
Message-ID: <da444113-b390-4b6c-b106-67307e38d2e2_at_p6g2000yqi.googlegroups.com>
Hi, First let me apologize for the long post. I'm trying to parse an XML document in a PL/SQL procedure. It is just not working, and after 6 hours of trying all sorts of examples I am turning to the experts. I display an element of the record, and it is empty. Can anyone see what I am doing wrong?
invalid_ticker EXCEPTION;
Date: Wed, 25 Apr 2012 08:00:51 -0700 (PDT)
Message-ID: <da444113-b390-4b6c-b106-67307e38d2e2_at_p6g2000yqi.googlegroups.com>
Hi, First let me apologize for the long post. I'm trying to parse an XML document in a PL/SQL procedure. It is just not working, and after 6 hours of trying all sorts of examples I am turning to the experts. I display an element of the record, and it is empty. Can anyone see what I am doing wrong?
Here is the PL/SQL procedure. Again, sorry for the long post.
First, ETF document:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<ETF_Report>
<ReportFile>vanguard_financials_etf_(vfh).pdf</ReportFile>
<Title>Vanguard Financials ETF (VFH)</Title>
<ReportType>1</ReportType>
<ReportTypeDescription>ETF Report</ReportTypeDescription>
<Tickers>
<Ticker>VFH</Ticker>
</Tickers>
</ETF_Report>
CREATE OR REPLACE PROCEDURE ETF.load_reports AS
v_count NUMBER; v_report_id VARCHAR2(15); v_filepath VARCHAR2(50) := '/c1/pdf/reports/'; v_temp VARCHAR2(1000); v_file_id UTL_FILE.FILE_TYPE; v_parser xmlparser.Parser; v_doc dbms_xmldom.DOMDocument; v_nl dbms_xmldom.DOMNodeList; v_n dbms_xmldom.DOMNode;
invalid_ticker EXCEPTION;
TYPE xml_record IS RECORD (
ticker VARCHAR2(6), title VARCHAR2(100)); v_rec xml_record;
BEGIN
- Create a parser. v_parser := xmlparser.newParser; xmlparser.setBaseDir(v_parser,'EXTERNAL_DIRECTORY'); xmlparser.parse(v_parser,'/ALPS_EQUAL_SECTOR_WEIGHT_ETF_(EQL).XML'); v_doc := xmlparser.getDocument(v_parser); xmlparser.freeParser(v_parser);
v_nl := xslprocessor.selectNodes(xmldom.makeNode(v_doc),'/ ETF_Report');
- Loop through the list and create a new record in table collection
FOR v_rec IN 0 .. dbms_xmldom.getLength(v_nl) - 1 LOOP
v_n := dbms_xmldom.item(v_nl, v_rec);
- Use XPATH syntax to assign values to he elements of the record. v_rec.title := xslprocessor.valueOf(v_n,'Title'); v_rec.ticker := xslprocessor.valueOf(v_n,'Ticker');
DBMS_OUTPUT.PUT_LINE('HERE: ' || v_rec.ticker);
END LOOP; xmldom.freeDocument(v_doc); Received on Wed Apr 25 2012 - 10:00:51 CDT