XML Parsing Question

[ExecMan]

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?

[dombrooks]

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;

[ExecMan]

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

[dombrooks]

Yes / It depends.

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

[ExecMan]

On May 1, 9:06 am, dombrooks <dombro...@hotmail.com> wrote:
> Yes / It depends.
>
> The above is just a demo using with/your_xml to simulate the source of a valid doc/snippet of xml.

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;