Major Help on XML Parsing

[ExecMan]

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);

[ExecMan]

Ok, I changed one of the lines:

xmlparser.parse(v_parser,v_xml_file);

I pass in the file name, and I get these errors:

ORA-31001: Invalid resource handle or path name
"ALPS_EQUAL_SECTOR_WEIGHT_ETF_(EQL).XML"
ORA-06512: at "SYS.XDBURITYPE", line 11
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 142
ORA-29280: invalid directory path
ORA-29280: invalid directory path
ORA-29280: invalid directory path

Any help there?

[ExecMan]

Ok, this problem solved. So, I am back to the issue that I am not
getting any data parsed from my XML. Why does Oracle make this so
difficult??

[Gerard H. Pille]

> FOR v_rec IN 0 .. dbms_xmldom.getLength(v_nl) - 1 LOOP

Is this a joke? Look at your definition of v_rec.