How can i took xml field data form clob clumn directly

i want to insert data in a table directly from clob data type.I have a long xml document.I am useing this way because i want tokeep save the xml in my database table first.


SQL> CREATE TABLE XML_LOAD_IN
2 (XML_CFILE CLOB
3 );

Table created.

SQL>
SQL> INSERT INTO XML_LOAD_IN ( XML_CFILE)
2 VALUES (
3 '
4 1
5 2
6 1500
7 2
8 Bank Asia
9 20090714
10
11 ');

1 row created.

SQL>
SQL> CREATE TABLE CCR_IN
2 (
3 “BundleCount” NUMBER(6) NOT NULL,
4 “ItemWithinCashLetterCount” NUMBER(8) NOT NULL,
5 “CashLetterTotalAmount” NUMBER(14) NOT NULL,
6 “ImagesWithinCashLetterCount” NUMBER(9),
7 “ECEInstitutionName” VARCHAR2(18 BYTE),
8 “SettlementDate” VARCHAR2(8 BYTE)
9 )
10 /

Table created.


CREATE OR REPLACE PROCEDURE mic.dpr_insert_from_xml_ccr IS
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;

TYPE tab_type IS TABLE OF ccr_in%ROWTYPE;

t_tab tab_type := tab_type ();
BEGIN
BEGIN
SELECT xml_cfile
INTO l_clob
FROM xml_load_in;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (-2001, 'Inward XML File Not Found.');
WHEN OTHERS THEN
RAISE;
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), '/CCR');

---FOR ECR
FOR cur_ccr IN 0 .. DBMS_XMLDOM.getlength (l_nl) - 1 LOOP
l_n := DBMS_XMLDOM.item (l_nl, cur_ccr);
t_tab.EXTEND;
--- Use XPATH syntax to assign values to he elements of the collection.
DBMS_XSLPROCESSOR.valueof (l_n,
'BundleCount/text()',
t_tab (t_tab.LAST)."BundleCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'ItemWithinCashLetterCount/text()',
t_tab (t_tab.LAST)."ItemWithinCashLetterCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'CashLetterTotalAmount/text()',
t_tab (t_tab.LAST)."CashLetterTotalAmount"
);
DBMS_XSLPROCESSOR.valueof
(l_n,
'ImagesWithinCashLetterCount/text()',
t_tab (t_tab.LAST)."ImagesWithinCashLetterCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'ECEInstitutionName/text()',
t_tab (t_tab.LAST)."ECEInstitutionName"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'SettlementDate/text()',
t_tab (t_tab.LAST)."SettlementDate"
);
END LOOP;

FOR cur_ccr IN t_tab.FIRST .. t_tab.LAST LOOP
INSERT INTO ccr_in
("BundleCount",
"ItemWithinCashLetterCount",
"CashLetterTotalAmount",
"ImagesWithinCashLetterCount",
"ECEInstitutionName",
"SettlementDate"
)
VALUES (t_tab (cur_ccr)."BundleCount",
t_tab (cur_ccr)."ItemWithinCashLetterCount",
t_tab (cur_ccr)."CashLetterTotalAmount",
t_tab (cur_ccr)."ImagesWithinCashLetterCount",
t_tab (cur_ccr)."ECEInstitutionName",
t_tab (cur_ccr)."SettlementDate"
);
END LOOP;

COMMIT;
DBMS_XMLDOM.freedocument (l_doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_XMLPARSER.freeparser (l_parser);
DBMS_XMLDOM.freedocument (l_doc);
END;
/

Procedure created.

SQL>
SQL> select * From xml_load_in;

XML_CFILE
——————————————————————————–

1
2
SQL>
SQL> exec Dpr_Insert_From_Xml_CCR;

PL/SQL procedure successfully completed.

SQL>
SQL> select * from ccr_in;

BundleCount ItemWithinCashLetterCount CashLetterTotalAmount
———– ————————- ———————
ImagesWithinCashLetterCount ECEInstitutionName Settleme
————————— —————— ——–
1 2 1500
2 20090714

SQL>