Hi,

I have a procedure that parses an XML file.  It works fine.  I use the
following notations:

v_etf_tab(v_etf_tab.LAST).title  := xslprocessor.valueOf(v_n,'/
ETF_Report/Title');
v_etf_tab(v_etf_tab.LAST).ticker := xslprocessor.valueOf(v_n,'/
ETF_Report/Tickers/Ticker');

However, they have just introduced a new XML file where the TICKER
element can occur more than once:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<ETF_Report>
<ReportFile>etf_research_mcg.pdf</ReportFile>
<Title>ETF_RESEARCH</Title>
<ReportType>3</ReportType>
<ReportTypeDescription>Style Box ETF Report</ReportTypeDescription>
<Tickers>
<Ticker>IWP</Ticker>
<Ticker>IVOG</Ticker>
<Ticker>MDYG</Ticker>
<Ticker>RFG</Ticker>
<Ticker>FNY</Ticker>
<Ticker>IJK</Ticker>
<Ticker>PXMG</Ticker>
<Ticker>VOT</Ticker>
<Ticker>JKH</Ticker>
<Ticker>FVL</Ticker>
</Tickers>
</ETF_Report>

How can I go through all the TICKER tags and get those values using
the XML parsing that Oracle provides?  Is there some looping
constructor?

Use SQL.

with your_xml as
(select xmltype('<?xml version="1.0" encoding="ISO-8859-1" ?>
<ETF_Report>
<ReportFile>etf_research_mcg.pdf</ReportFile>
<Title>ETF_RESEARCH</Title>
<ReportType>3</ReportType>
<ReportTypeDescription>Style Box ETF Report</ReportTypeDescription>
<Tickers>
<Ticker>IWP</Ticker>
<Ticker>IVOG</Ticker>
<Ticker>MDYG</Ticker>
<Ticker>RFG</Ticker>
<Ticker>FNY</Ticker>
<Ticker>IJK</Ticker>
<Ticker>PXMG</Ticker>
<Ticker>VOT</Ticker>
<Ticker>JKH</Ticker>
<Ticker>FVL</Ticker>
</Tickers>
</ETF_Report>') xml
from dual)
select extractvalue(value(x), '/Ticker') ticker
from   xmltable('/*/Tickers/Ticker'  
       passing (select xml  
                from   your_xml)) x;

On May 1, 4:24 am, dombrooks <dombro...@hotmail.com> wrote:

Will this work is "your_xml" is read from a file?  I am parsing XML
files.

Yes / It depends.

The above is just a demo using with/your_xml to simulate the source of a valid doc/snippet of xml.

On May 1, 9:06 am, dombrooks <dombro...@hotmail.com> wrote:

Well, being new at this XML thingy I'm not sure I am getting the
code.  Here is what I currently have in place.  This works fine,
except now we are getting multiple of the TICKER node, and I need to
loop and process those.

  v_xml_id := OPEN_FILES(p_xml_dir,p_xml_files,'r');

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_xml_id,v_xml_file);

      v_parser := xmlparser.newParser;
      dbms_xmlparser.setBaseDir(v_parser,p_xml_dir);
      xmlparser.parse(v_parser,v_xml_file);
      v_doc := xmlparser.getDocument(v_parser);
      xmlparser.freeParser(v_parser);

      v_nl := xslprocessor.selectNodes(xmldom.makeNode(v_doc),'/
ETF_Report');

      -- Loop through the document and create a new record in table
collection
      FOR v_rec IN 0 .. dbms_xmldom.getLength(v_nl) - 1 LOOP
        BEGIN
          v_n := dbms_xmldom.item(v_nl, v_rec);

          v_etf_tab.EXTEND;
          v_etf_tab(v_etf_tab.LAST).title  :=
xslprocessor.valueOf(v_n,'/ETF_Report/Title');
          v_etf_tab(v_etf_tab.LAST).ticker :=
xslprocessor.valueOf(v_n,'/ETF_Report/Tickers/Ticker');

          DBMS_OUTPUT.PUT_LINE('HERE: ' ||
v_etf_tab(v_etf_tab.LAST).title);
          DBMS_OUTPUT.PUT_LINE('HERE: ' ||
v_etf_tab(v_etf_tab.LAST).ticker);

          EXCEPTION
          WHEN OTHERS THEN
             NULL;
        END;
      END LOOP;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        UTL_FILE.FCLOSE_ALL;
        EXIT;
    END;

    xmldom.freeDocument(v_doc);
  END LOOP;