Parse XML and Load in oracle Tables

[Tarus]

Dear All,

I have a problem in current environmnet.

Below is the XML file iam using.Iam able to parse and get values for all nodes.

But dont know how to parse the REGIONS nodes and its child nodes and get the value. If somebody can help me it will be of great help.

< ?xml version="1.0"?>
< GetDataResult>
< RDCItem Type="Cash">
< Program> Cash Delivery Allowance< / Program>
< ModelYear> 2008< / ModelYear>
< Model> Aura3< / Model>
< Make> Saturn< / Make>
< ModelCode> ZZS69< / ModelCode>
< Amount> 500< / Amount>
< EndDate> 1/ 10/ 2009< / EndDate>
< Regions>
< Region> BC< / Region>
< Region> AT< / Region>
< Region> AB< / Region>
< Region> QC< / Region>
< Region> PR< / Region>
< Region> ON< / Region>
< / Regions>
< / RDCItem>

< RDCItem Type="Bash">
< Program> Cash Delivery Allowance< / Program>
< ModelYear> 2009< / ModelYear>
< Model> Aura2< / Model>
< Make> Saturn2< / Make>
< ModelCode> ZZS692< / ModelCode>
< Amount> 5000< / Amount>
< EndDate> 11/ 10/ 2009< / EndDate>
< Regions>
< Region> BC< / Region>
< Region> AT< / Region>
< Region> AB< / Region>
< Region> QC< / Region>
< Region> PR< / Region>
< Region> ON< / Region>
< / Regions>
< OptionCodes>
< OptionCode> LMM< / OptionCode>
< / OptionCodes>
< / RDCItem>
< / GetDataResult>

Procedure :-


create or replace procedure proc_rdc
as
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);
l_attrs dbms_xmldom.DOMNamedNodeMap;
l_attr dbms_xmldom.DOMNode;

l_users dbms_xmldom.domnodelist;
l_user dbms_xmldom.domnode;

l_type rdc.type%TYPE;

TYPE tab_type IS TABLE OF rdc%ROWTYPE;

l_region rdc.region1%TYPE;

t_tab tab_type := tab_type();
BEGIN
l_bfile := BFileName('XML_DIR', 'rdc.xml');
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
dbms_lob.close(l_bfile);

dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MM-YYYY''');

l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);

l_users := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(l_doc), '/GetDataResult/RDCItem');

l_attrs := dbms_xmldom.getattributes(l_user);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/GetDataResult/RDCItem');

l_n := dbms_xslprocessor.selectSingleNode(xmldom.makeNode(l_doc),'/GetDataResult/RDCItem');

l_attrs := dbms_xmldom.getattributes(l_n);
l_attr := dbms_xmldom.item(l_attrs, 0);
DBMS_OUTPUT.put_line(dbms_xmldom.getNodeName(l_attr) || '=' || dbms_xmldom.getNodeValue(l_attr));
dbms_output.put_line('hi' || dbms_xmldom.getLength(l_nl));

FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP


dbms_output.put_line('hi inside');
l_n := dbms_xmldom.item(l_nl, cur_emp);

dbms_output.put_line('one');
l_attrs := xmldom.getattributes (l_n);

l_attr := dbms_xmldom.ITEM (l_attrs, 0);

IF dbms_xmldom.getnodename (l_attr) = 'Type' THEN
l_type := dbms_xmldom.getnodevalue (l_attr);
END IF;


t_tab.extend;
--dbms_xslprocessor.valueOf(l_n,'RDCItem/text()',t_tab(t_tab.last).type);
dbms_xslprocessor.valueOf(l_n,'Program/text()',t_tab(t_tab.last).Program);
dbms_xslprocessor.valueOf(l_n,'ModelCode/text()',t_tab(t_tab.last).modelcode);
dbms_xslprocessor.valueOf(l_n,'ModelYear/text()',t_tab(t_tab.last).modelyear);
dbms_xslprocessor.valueOf(l_n,'Model/text()',t_tab(t_tab.last).model);
dbms_xslprocessor.valueOf(l_n,'Amount/text()',t_tab(t_tab.last).amount);
dbms_xslprocessor.valueOf(l_n,'EndDate/text()',t_tab(t_tab.last).enddate);

t_tab(t_tab.last).type := l_type;
END LOOP;
dbms_output.put_line('hi' || t_tab.first || ' ' || t_tab.first );
FOR cur_emp IN t_tab.first .. t_tab.last LOOP
INSERT INTO rdc
(program,
modelcode,
Model,
type,
modelyear,
amount,
enddate
)
VALUES
(t_tab(cur_emp).program,
t_tab(cur_emp).modelcode,
t_tab(cur_emp).model,
t_tab(cur_emp).type,
t_tab(cur_emp).modelyear,
t_tab(cur_emp).amount,
t_tab(cur_emp).enddate
);

END LOOP;
COMMIT;
dbms_xmldom.freeDocument(l_doc);
END;
/

[ales]

Use XSLT (the xmltype.transform function) to extract the Regions node to another XML document and transform it to the oracle canonical form <ROWSET><ROW> ... </ROW><ROW>...</ROW>...</ROWSET>. Then use dbms_xmlstore to insert data into table.
I think you could use the same practice for the rest of the document and avoid the "node_after_node" parsing.