Home » Developer & Programmer » JDeveloper, Java & XML » Issue in xml parsing through pl/sql (Oracle 10g)
icon4.gif  Issue in xml parsing through pl/sql [message #532168] Mon, 21 November 2011 03:03 Go to next message
greekguy
Messages: 3
Registered: November 2011
Location: Bangalore
Junior Member
Hi All,

I am new to this forum as well as in oracle PL/SQL. I am a java resource but company gave me this work.

I have almost finished this xml parsing task but their is one problem. Actually in our table there are more than 70-80 columns & due to that only I don't want to put the hard coded column name in my procedure, because if I will do that, the unnecessary procedure size will be increase(means line of code).

Here is our procedure



 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;



Thanks in advance and Any kind of help is really appreciated.

Re: Issue in xml parsing through pl/sql [message #532219 is a reply to message #532168] Mon, 21 November 2011 08:07 Go to previous messageGo to next message
greekguy
Messages: 3
Registered: November 2011
Location: Bangalore
Junior Member
Anyone can help me on this...

Its bit urgent

please help me...
Re: Issue in xml parsing through pl/sql [message #532225 is a reply to message #532219] Mon, 21 November 2011 10:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what is this???

t_tab(t_tab.last).l_colName

l_colName value is attribute name. So referencing t_tab(t_tab.last).l_colName would be same as select emp.l_colName from emp where l_colName value is, for example ename. What you are trying to do is dynamic sql.

SY.
Re: Issue in xml parsing through pl/sql [message #532319 is a reply to message #532225] Tue, 22 November 2011 00:18 Go to previous message
greekguy
Messages: 3
Registered: November 2011
Location: Bangalore
Junior Member
Hi Syakobson,

Thanks for your reply...

As I have mention in my first post that I am new to PL/SQL programming.
In xml file there are all columns name and I just want to use those columns name with "t_tab(t_tab.last)".

Those columns name I am taking in a variable l_colName and want to use those variable with t_tab(t_tab.last), whereas t_tab is the rowtype of table test_hem which contains around 60-70 columns.

I know that l_colName is not a components of t_tab due to that only it is throwing error. But Is there any way to use while loop for getting the column name & its values from the xml file.

Again thanks in advance and any kind of help is really appreciated.
Previous Topic: JDeveloper10 - jradiobutton not showing dot
Next Topic: file to base64
Goto Forum:
  


Current Time: Mon Jun 01 18:34:38 CDT 2020