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?

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