Create or replace procedure loadMyXML(dir_name IN varchar2, xmlfile IN varchar2) AS
  l_bfile   	BFILE;
  l_clob    	CLOB;
  l_parser  	dbms_xmlparser.Parser;
  l_doc     	dbms_xmldom.DOMDocument;
  l_nl1     	dbms_xmldom.DOMNodeList;
  l_nl2     	dbms_xmldom.DOMNodeList;
  l_n       	dbms_xmldom.DOMNode;
  node1     	dbms_xmldom.DOMNode;
  l_colName    	VARCHAR2(100);
  
  l_xmltype 	XMLTYPE;
  sub_xmltype 	XMLTYPE;
  
  num_nodes    	    number;
  temp_imei_val		VARCHAR2(15);
  temp_imsi_val		VARCHAR2(15);
  temp_cellid		VARCHAR2(10);
  l_index			PLS_INTEGER;
  l_subIndex 	PLS_INTEGER;
  
  -- creating a Type which is a type of "test_hem" table RowType
  TYPE tab_type IS TABLE OF test_hem%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
 -- "My_Files" directory location is "/home/oracle/TEMP" in oracle system.
 -- Passing the xmlfile and virtual directory name which we gave at the time of directory creation
  l_bfile := BFileName('MY_FILES', xmlfile);
  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);
  
  -- make sure implicit date conversions are performed correctly
  -- dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YY''');
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;
    
  -- Problem occurring
  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);
    
  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);
    
  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl1 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'//mdc/mfh');
  
  -- Loop through the list and create a new record in a tble collection
  FOR cur_sel IN 0 .. dbms_xmldom.getLength(l_nl1) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl1, cur_sel);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'ffv/text()',t_tab(t_tab.last).ffv);
	
	temp_imsi_val := SUBSTR(dbms_xslprocessor.valueOf(l_n,'sn/text()'),6,15);
    temp_imei_val := SUBSTR(dbms_xslprocessor.valueOf(l_n,'sn/text()'),27,15);
	
	  
	t_tab(t_tab.last).imsi := temp_imsi_val;
	t_tab(t_tab.last).imei := temp_imei_val;
	
    dbms_xslprocessor.valueOf(l_n,'st/text()',t_tab(t_tab.last).state);
    dbms_xslprocessor.valueOf(l_n,'vn/text()',t_tab(t_tab.last).vendorname);
    --dbms_xslprocessor.valueOf(l_n,'cbt/text()',t_tab(t_tab.last).startingtime);
	dbms_output.put_line('First for loop is finished');
  END LOOP;
  
   l_n := dbms_xslprocessor.selectSingleNode(dbms_xmldom.makeNode(l_doc),'//mdc/md/neid/nesw');
    dbms_xslprocessor.valueOf(l_n,'nesw/text()',t_tab(t_tab.last).version);
	l_nl1 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'//mdc/md/mi');
	num_nodes := dbms_xmldom.getLength(l_nl1);
    dbms_output.put_line('Calculated Nodes: ' || num_nodes);
	
	l_index := 1;
		FOR cur_sel IN 0 .. dbms_xmldom.getLength(l_nl1) - 1 LOOP
			node1 := dbms_xmldom.item(l_nl1, cur_sel);
			l_xmltype := xmltype(l_bfile, nls_charset_id('AL32UTF8'));
						
				dbms_output.put_line('The list index value is:- ' || l_index);
			IF (l_xmltype.Existsnode('//mdc/md/mi[' || l_index || ']/mt') > 0) Then 
				
				dbms_output.put_line('total mt super node is:- ' || l_index);
				
			 -- dbms_xslprocessor.valueOf(node1,'mts/text()',t_tab(t_tab.last).data_time);
				dbms_xslprocessor.valueOf(node1,'gp/text()',t_tab(t_tab.last).granularity);
				
			 --	temp_cellid := SUBSTR(dbms_xslprocessor.valueOf(node1,'mv/moid/text()'),5);
			 --	t_tab(t_tab.last).cellid := temp_cellid;
				
				dbms_output.put_line('The colName value is:-  '|| l_colName);
				l_subIndex := 1;
				WHILE l_xmltype.Existsnode('//mdc/md/mi[' || l_index ||']/mt[' || l_subIndex || ']') > 0
				 Loop
					
		l_colName := dbms_xslprocessor.valueOf(node1,'mt[' || l_subIndex || ]/text()');

	 dbms_output.put_line('The column name from the table is:- ' || l_colName);

[quote]
-- Here when I am using "t_tab(t_tab.last).l_colName" then it is giving me error {PLS-00302: component ':l_colName' must be declared}. In my xml there is columnName and its value and I am fetching the columnName from the xml and storing into the l_colName variable. How I will convert this variable to components of the table. When I am putting hardcoded columnName then it is working properly...
[/quote]

dbms_xslprocessor.valueOf(node1,'mt[' || l_subIndex || ']/text()',t_tab(t_tab.last).l_colName);
 
		l_colName := '';
		l_subIndex := l_subIndex + 1;
	 End Loop;

-- When I am un-commenting the following three lines everything is working fine, becz col1,col2,col3 is the component of the t_tab which is a type of "test_hem" table.

--dbms_xslprocessor.valueOf(node1,'mt[' || l_subIndex || ']/text()',t_tab(t_tab.last).col1);

--dbms_xslprocessor.valueOf(node1,'mt[' || l_subIndex || ']/text()',t_tab(t_tab.last).col2);

--dbms_xslprocessor.valueOf(node1,'mt[' || l_subIndex || ']/text()',t_tab(t_tab.last).col3);
								
-- Insert data into the real EMP table from the table collection.
				FORALL i IN t_tab.first .. t_tab.last
				INSERT INTO nec_test_hem VALUES t_tab(i);
				
			END IF;
			l_index := l_index + 1;
	
  COMMIT; 
	END LOOP;
		  
	commit;
  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);
  
EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
END;