Read XML data from a CLOB file which is already stored into Database

CREATE TABLE XML_LOAD_IN
(
FILE_ID VARCHAR2(13 BYTE),
FILE_NAME VARCHAR2(200 BYTE),
XML_CFILE CLOB,
);


ALTER TABLE XML_LOAD_IN ADD (PRIMARY KEY (FILE_ID));

--==========================================================================


CREATE TABLE BCR
(
"ItemsWithinBundleCount" NUMBER(4) NOT NULL,
"BundleTotalAmount" NUMBER(12) NOT NULL,
"MICRValidTotalAmount" NUMBER(12),
"ImagesWithinBundleCount" NUMBER(5),
"UserField" VARCHAR2(5 BYTE)
);

--==========================================================================

/*** We assume that a XML file is Stored into your table ***/

CREATE OR REPLACE procedure Dpr_Read_Xml_Data(p_file_id in varchar2)
is
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;

TYPE tab_type IS TABLE OF BCR%ROWTYPE;
t_tab tab_type := tab_type();

BEGIN

Begin

select xml_cfile
into l_clob
from xml_load_in
where file_id = p_file_id;


Exception
When no_data_found then
raise_application_error(-2001,'XML File Not Found.');
When others then null;
End;


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

dbms_xmlparser.freeParser(l_parser);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/OCE/CashLetter/Bundle/BundleControl');
/* Edit above xml node as your required */

FOR CUR_BCR IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, CUR_BCR);
t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.

dbms_xslprocessor.valueOf(l_n,'ItemsWithinBundleCount/text()' ,t_tab(t_tab.last)."ItemsWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'BundleTotalAmount/text()' ,t_tab(t_tab.last)."BundleTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'MICRValidTotalAmount/text()' ,t_tab(t_tab.last)."MICRValidTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'ImagesWithinBundleCount/text()' ,t_tab(t_tab.last)."ImagesWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'UserField/text()' ,t_tab(t_tab.last)."UserField" );
END LOOP;


FOR CUR_BCR IN t_tab.first .. t_tab.last LOOP

INSERT INTO BCR
("ItemsWithinBundleCount" ,
"BundleTotalAmount" ,
"MICRValidTotalAmount" ,
"ImagesWithinBundleCount" ,
"UserField"
)
VALUES
(t_tab(CUR_BCR)."ItemsWithinBundleCount" ,
t_tab(CUR_BCR)."BundleTotalAmount" ,
t_tab(CUR_BCR)."MICRValidTotalAmount" ,
t_tab(CUR_BCR)."ImagesWithinBundleCount" ,
t_tab(CUR_BCR)."UserField"
);

END LOOP;

COMMIT;

dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;
/