Re: Major Help on XML Parsing
From: ExecMan <artmerar_at_yahoo.com>
Date: Wed, 25 Apr 2012 09:10:37 -0700 (PDT)
Message-ID: <fd452497-91fb-484b-9782-28e0bd506ac3_at_m16g2000yqc.googlegroups.com>
On Apr 25, 11:00 am, ExecMan <artme..._at_yahoo.com> wrote:
> On Apr 25, 10:00 am, ExecMan <artme..._at_yahoo.com> wrote:
>
>
>
> > 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);
>
> 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?
Date: Wed, 25 Apr 2012 09:10:37 -0700 (PDT)
Message-ID: <fd452497-91fb-484b-9782-28e0bd506ac3_at_m16g2000yqc.googlegroups.com>
On Apr 25, 11:00 am, ExecMan <artme..._at_yahoo.com> wrote:
> On Apr 25, 10:00 am, ExecMan <artme..._at_yahoo.com> wrote:
>
>
>
> > 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);
>
> 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?
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?? Received on Wed Apr 25 2012 - 11:10:37 CDT