Home » Developer & Programmer » JDeveloper, Java & XML » XML Processing (Oracle 10g)
icon9.gif  XML Processing [message #474367] Sun, 05 September 2010 23:49 Go to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Hi Guys,

i have a table which has a CLOB data type column that holds a XML. i need select the some of the nodes from it.. sample XML looks like this.. there are actually 100 or more nodes totally but i want to select some 4 of them as a coloumn for my select query. please help me out in this guys.

<?xml version="1.0" encoding="UTF-8"?><alert>
 
	<tab0 comment="section in XSL component" name="Initial Information">
		<details>	
			<Priority>0001</Priority>
			<DateVRUClaimInitiated>2010-06-29</DateVRUClaimInitiated>
		
		</details>
		<contacts comment="grid in XSL component">
			many child nodes here
		</contacts>			
	</tab0>
 
	<tab1 comment="section in XSL component" name="Additional Information">
		<details comment="list collection in XSL component">
			Many child nodes here
		</details>
		<IPAddresses>
			Many child nodes here
		</IPAddresses>		
		<ANIPhones>
			Many child nodes here
		</ANIPhones>
	</tab1>
	
	<tab2 name="Loss Avoidance">
		<losstable>
			Many child nodes here	
		</losstable>
		<summary>
			Many child nodes here	
		</summary>
		<accounts>
			Many child nodes here
		</accounts>
	</tab2>
	
 
	<tab3 name="R &amp; R">		
		<narrative>
			Many child nodes here	
		</narrative>
	</tab3>
 
</alert>


Re: XML Processing [message #474368 is a reply to message #474367] Mon, 06 September 2010 00:16 Go to previous messageGo to next message
sujeethkannan
Messages: 10
Registered: August 2010
Location: CHENNAI
Junior Member
Use the following options for XML parsing

for i in (
select STORE ,EMP
from xmltable (
'STOREEMP'
passing xmltype (l_clob_xml_msg).extract ('//STOREEMP')
columns STORE NUMBER , EMP number )) loop
insert into test_data values (i.STORE,i.EMP,'test','est',SYSDATE);
end loop;
end;

OPTION 2:
following info is useful
http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php

Thanks,
Sujeeth kannan
Re: XML Processing [message #474371 is a reply to message #474368] Mon, 06 September 2010 00:29 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
i have tried these two types both of them fail if i have where condition
select alert_internal_id,x.* from alerts, xmltable ('.' passing XMLTYPE(xml)
             columns prio varchar2 (5) path '/alert/tab0/details/Priority',
                     dt varchar2 (15) path '/alert/type0/details/DateVRUClaimInitiated',
                     type_comment varchar2 (25) path '/alert/tab0/@comment') x where x.dt is not null; 


select alert_internal_id,extractvalue( xmltype(HTML_FILE_KEY),'/alert/tab0/details/DateVRUClaimInitiated') 
       as check1 
from alerts 
where extractvalue( xmltype(HTML_FILE_KEY),'/alert/tab0/details/DateVRUClaimInitiated') 
        is not null;



ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '['
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1

[Updated on: Mon, 06 September 2010 00:49] by Moderator

Report message to a moderator

Re: XML Processing [message #474375 is a reply to message #474371] Mon, 06 September 2010 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works for me:
SQL> with alerts as (
  2   select 1 alert_internal_id,
  3          '<?xml version="1.0" encoding="UTF-8"?><alert>
  4   <tab0 comment="section in XSL component" name="Initial Information">
  5    <details> 
  6     <Priority>0001</Priority>
  7     <DateVRUClaimInitiated>2010-06-29</DateVRUClaimInitiated>
  8    </details>
  9    <contacts comment="grid in XSL component">
 10     many child nodes here
 11    </contacts>   
 12   </tab0>
 13   <tab1 comment="section in XSL component" name="Additional Information">
 14    <details comment="list collection in XSL component">
 15     Many child nodes here
 16    </details>
 17    <IPAddresses>
 18     Many child nodes here
 19    </IPAddresses>  
 20    <ANIPhones>
 21     Many child nodes here
 22    </ANIPhones>
 23   </tab1>
 24   <tab2 name="Loss Avoidance">
 25    <losstable>
 26     Many child nodes here 
 27    </losstable>
 28    <summary>
 29     Many child nodes here 
 30    </summary>
 31    <accounts>
 32     Many child nodes here
 33    </accounts>
 34   </tab2>
 35   <tab3 name="R &amp; R">  
 36    <narrative>
 37     Many child nodes here 
 38    </narrative>
 39   </tab3>
 40  </alert>' HTML_FILE_KEY
 41    from dual
 42    )
 43  select alert_internal_id,extractvalue( xmltype(HTML_FILE_KEY),'/alert/tab0/details/DateVRUClaimInitiated') 
 44         as check1 
 45  from alerts 
 46  where extractvalue( xmltype(HTML_FILE_KEY),'/alert/tab0/details/DateVRUClaimInitiated') 
 47          is not null;
ALERT_INTERNAL_ID CHECK1
----------------- --------------------
                1 2010-06-29

1 row selected.

Regards
Michel
Re: XML Processing [message #474378 is a reply to message #474375] Mon, 06 September 2010 01:07 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
found this it works if i have less number of rows in the table, any other way ??..

select alert_internal_id,x.* from alerts, xmltable ('.' passing XMLTYPE(xml)
             columns prio varchar2 (5) path '/alert/tab0/details/Priority',
                     dt varchar2 (15) path '/alert/type0/details/DateVRUClaimInitiated',
                     type_comment varchar2 (25) path '/alert/tab0/@comment') x 
where alert_internal_id < 7000  and x.dt is not null; 
Query returns rows


select alert_internal_id,x.* from alerts, xmltable ('.' passing XMLTYPE(xml)
             columns prio varchar2 (5) path '/alert/tab0/details/Priority',
                     dt varchar2 (15) path '/alert/type0/details/DateVRUClaimInitiated',
                     type_comment varchar2 (25) path '/alert/tab0/@comment') x 
where alert_internal_id < 8000  and x.dt is not null; 
 
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '['
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1

[Updated on: Mon, 06 September 2010 01:13] by Moderator

Report message to a moderator

Re: XML Processing [message #474383 is a reply to message #474378] Mon, 06 September 2010 01:29 Go to previous messageGo to next message
sujeethkannan
Messages: 10
Registered: August 2010
Location: CHENNAI
Junior Member
Can you try XML parsing using PL/SQL XDK

declare
p xmlparser.parser;
doc xmldom.DOMDocument;

nl xmldom.DOMNodeList;
len1 number;
len2 number;
n xmldom.DOMNode;
e xmldom.DOMElement;
nnm xmldom.DOMNamedNodeMap;
attrname varchar2(100);
attrval varchar2(100);

l_c_lob clob ;
l_v_var varchar2(32000) := '<?xml version="1.0" encoding="utf-8"?>
<EMPDETAILS>
<EMP>
<NUM>7369</NUM>
<SAL>180</SAL>
</EMP>
<EMP>
<NUM>7369</NUM>
<SAL>180</SAL>
</EMP>
<EMP>
<NUM>7369</NUM>
<SAL>180</SAL>
</EMP>
</EMPDETAILS>';


begin
dbms_lob.createtemporary(l_c_lob,TRUE);
dbms_lob.OPEN(l_c_lob,dbms_lob.lob_readwrite);
DBMS_LOB.WRITE (l_c_lob, LENGTH (l_v_var), 1,l_v_var);
-- new parser
p := xmlparser.newParser;

xmlparser.setValidationMode(p, FALSE);
xmlparser.parseClob(p,l_c_lob);

doc := xmlparser.getDocument(p);

nl := xmldom.getElementsByTagName(doc, '*');
len1 := xmldom.getLength(nl);

-- loop through elements
for j in 0..len1-1 loop
n := xmldom.item(nl, j);
e := xmldom.makeElement(n);

attrname := xmldom.getNodeName(n);
attrval:=xmldom.getNodeValue(xmldom.getFirstChild(n));

dbms_output.put_line(attrname||' = '||attrval);

end loop;



EXCEPTION
when OTHERS then
RAISE;
end;
/


Thanks,
Sujeeth kannan
Re: XML Processing [message #474384 is a reply to message #474383] Mon, 06 September 2010 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

2/
Quote:
EXCEPTION when OTHERS then RAISE;

This is one of the most STUPID thing I ever read in PL/SQL code.

Regards
Michel

[Updated on: Mon, 06 September 2010 01:40]

Report message to a moderator

Re: XML Processing [message #474387 is a reply to message #474383] Mon, 06 September 2010 01:49 Go to previous messageGo to next message
sujeethkannan
Messages: 10
Registered: August 2010
Location: CHENNAI
Junior Member
If its works for you can handle exception.

when xmldom.INDEX_SIZE_ERR then
raise_application_error(-20120, 'Index Size error');

when xmldom.DOMSTRING_SIZE_ERR then
raise_application_error(-20120, 'String Size error');

when xmldom.HIERARCHY_REQUEST_ERR then
raise_application_error(-20120, 'Hierarchy request error');

when xmldom.WRONG_DOCUMENT_ERR then
raise_application_error(-20120, 'Wrong doc error');

when xmldom.INVALID_CHARACTER_ERR then
raise_application_error(-20120, 'Invalid Char error');

when xmldom.NO_DATA_ALLOWED_ERR then
raise_application_error(-20120, 'Nod data allowed error');

when xmldom.NO_MODIFICATION_ALLOWED_ERR then
raise_application_error(-20120, 'No mod allowed error');

when xmldom.NOT_FOUND_ERR then
raise_application_error(-20120, 'Not found error');

when xmldom.NOT_SUPPORTED_ERR then
raise_application_error(-20120, 'Not supported error');

when xmldom.INUSE_ATTRIBUTE_ERR then
raise_application_error(-20120, 'In use attr error');
Re: XML Processing [message #474388 is a reply to message #474387] Mon, 06 September 2010 01:56 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
My problem is i cannot use procedures.. it's gotta be a select query Sad
Re: XML Processing [message #474389 is a reply to message #474387] Mon, 06 September 2010 02:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sujeethkannan wrote on Mon, 06 September 2010 08:49
If its works for you can handle exception.

when xmldom.INDEX_SIZE_ERR then
raise_application_error(-20120, 'Index Size error');

when xmldom.DOMSTRING_SIZE_ERR then
raise_application_error(-20120, 'String Size error');

when xmldom.HIERARCHY_REQUEST_ERR then
raise_application_error(-20120, 'Hierarchy request error');

when xmldom.WRONG_DOCUMENT_ERR then
raise_application_error(-20120, 'Wrong doc error');

when xmldom.INVALID_CHARACTER_ERR then
raise_application_error(-20120, 'Invalid Char error');

when xmldom.NO_DATA_ALLOWED_ERR then
raise_application_error(-20120, 'Nod data allowed error');

when xmldom.NO_MODIFICATION_ALLOWED_ERR then
raise_application_error(-20120, 'No mod allowed error');

when xmldom.NOT_FOUND_ERR then
raise_application_error(-20120, 'Not found error');

when xmldom.NOT_SUPPORTED_ERR then
raise_application_error(-20120, 'Not supported error');

when xmldom.INUSE_ATTRIBUTE_ERR then
raise_application_error(-20120, 'In use attr error');

And what does this add to the original exception except it hides where it comes from and the complementary information it gives?

If you have nothing to do with an exception then let it go, it is stupid to trap it.

Regards
Michel

[Updated on: Mon, 06 September 2010 13:38]

Report message to a moderator

Previous Topic: DOM parsing
Next Topic: reason for java.sql.SQLException: Closed Connection
Goto Forum:
  


Current Time: Thu Mar 28 15:53:58 CDT 2024