How can i took xml field data form clob clumn directly ?

How can i took xml field data form clob clumn directly ?
SQL>
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.

SQL>
SQL> CREATE OR REPLACE procedure Dpr_Insert_From_Xml_CCR
2 is
3 l_bfile BFILE;
4 l_clob CLOB;
5 l_parser dbms_xmlparser.Parser;
6 l_doc dbms_xmldom.DOMDocument;
7 l_nl dbms_xmldom.DOMNodeList;
8 l_n dbms_xmldom.DOMNode;
9
10 TYPE tab_type IS TABLE OF CCR_IN%ROWTYPE;
11 t_tab tab_type := tab_type();
12
13 BEGIN
14
15 Begin
16 select xml_cfile
17 into l_clob
18 from xml_load_in;
19 Exception
20 When no_data_found then
21 raise_application_error(-2001,'Inward XML File Not Found.');
22 When others then raise;
23 End;
24
25 l_parser := dbms_xmlparser.newParser;
26 dbms_xmlparser.parseClob(l_parser, l_clob);
27 l_doc := dbms_xmlparser.getDocument(l_parser);
28 dbms_xmlparser.freeParser(l_parser);
29 l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/CCR');
30
31 –FOR ECR
32 FOR CUR_CCR IN 0 .. dbms_xmldom.getLength(l_nl) – 1 LOOP
33 l_n := dbms_xmldom.item(l_nl, CUR_CCR);
34 t_tab.extend;
35 — Use XPATH syntax to assign values to he elements of the collection.
36 dbms_xslprocessor.valueOf(l_n,'BundleCount/text()' ,t_tab (t_tab.last).”BundleCount” );
37 dbms_xslprocessor.valueOf(l_n,'ItemWithinCashLetterCount/text()' ,t_tab(t_tab.last).”ItemWithinCashLetterCount” );
38 dbms_xslprocessor.valueOf(l_n,'CashLetterTotalAmount/text()' ,t_tab(t_tab.last).”CashLetterTotalAmount” );
39 dbms_xslprocessor.valueOf(l_n,'ImagesWithinCashLetterCount/text()' ,t_tab(t_tab.last).”ImagesWithinCashLetterCount” );
40 dbms_xslprocessor.valueOf(l_n,'ECEInstitutionName/text()' ,t_tab(t_tab.last).”ECEInstitutionName” );
41 dbms_xslprocessor.valueOf(l_n,'SettlementDate/text()' ,t_tab(t_tab.last).”SettlementDate” );
42 END LOOP;
43
44
45 FOR CUR_CCR IN t_tab.first .. t_tab.last LOOP
46
47 INSERT INTO CCR_IN
48 (”BundleCount” ,
49 “ItemWithinCashLetterCount” ,
50 “CashLetterTotalAmount” ,
51 “ImagesWithinCashLetterCount” ,
52 “ECEInstitutionName” ,
53 “SettlementDate”
54 )
55 VALUES
56 (t_tab(CUR_CCR).”BundleCount” ,
57 t_tab(CUR_CCR).”ItemWithinCashLetterCount” ,
58 t_tab(CUR_CCR).”CashLetterTotalAmount” ,
59 t_tab(CUR_CCR).”ImagesWithinCashLetterCount” ,
60 t_tab(CUR_CCR).”ECEInstitutionName” ,
61 t_tab(CUR_CCR).”SettlementDate”
62 );
63
64 END LOOP;
65
66 COMMIT;
67
68 dbms_xmldom.freeDocument(l_doc);
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 dbms_xmlparser.freeParser(l_parser);
73 dbms_xmldom.freeDocument(l_doc);
74 END;
75 /

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 Bank Asia 2009071