Home » Developer & Programmer » JDeveloper, Java & XML » extract from XML (Oracle 10g)
extract from XML [message #413484] Thu, 16 July 2009 00:56 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I have inserted the below in my reports data table .

insert into report_data(report_id,report_parameters) values 
(8,
XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?> 
 <Report>
  <Title>Incident Severity Report - 2009 Q1</Title> 
 <xaxis>
  <Id>INC_YEAR</Id> 
  <Name>Incidence Date - Year</Name> 
  </xaxis>
 <yaxis>
  <Id>COUNT(INCIDENT_ID)</Id> 
  <Name>Incidents</Name> 
  </yaxis>
  <Series>
  <Id>URGENCY_ID</Id> 
  <Name>URGENCY</Name>   
  </Series> 
   </Report>'));


From the above i need to extract the id's and need to frame a query like
select INC_YEAR,COUNT(INCIDENT_ID),
URGENCY_ID from VW_TAB;


When i try with below query to get teh id value it is returning null.
select extract( report_parameters,'//xaxis/id').getstringval() 
from report_data where report_id =8;


Please let me know how can i get a final query like
select INC_YEAR,COUNT(INCIDENT_ID),
URGENCY_ID from VW_TAB;

from above XML.
Re: extract from XML [message #413491 is a reply to message #413484] Thu, 16 July 2009 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2  select XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?>
  3   <Report>
  4    <Title>Incident Severity Report - 2009 Q1</Title>
  5   <xaxis>
  6    <Id>INC_YEAR</Id>
  7    <Name>Incidence Date - Year</Name>
  8    </xaxis>
  9   <yaxis>
 10    <Id>COUNT(INCIDENT_ID)</Id>
 11    <Name>Incidents</Name>
 12    </yaxis>
 13    <Series>
 14    <Id>URGENCY_ID</Id>
 15    <Name>URGENCY</Name>
 16    </Series>
 17     </Report>') val from dual)
 18  select extractvalue(value(t),'/xaxis/Id') val
 19  from data, table(xmlsequence(extract(data.val,'/Report/xaxis'))) t
 20  /
VAL
---------------------------------------------------
INC_YEAR


By the way,
select INC_YEAR,COUNT(INCIDENT_ID),
URGENCY_ID from VW_TAB;

is not a valid query.

[Updated on: Thu, 16 July 2009 01:35]

Report message to a moderator

Re: extract from XML [message #413516 is a reply to message #413491] Thu, 16 July 2009 03:59 Go to previous message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks Michel,
Yes you are right, after i get the required query output from XML.
I will append the Group by clause to the query.
Previous Topic: EJB object - Getting Null Pointer Exception (merged 4)
Next Topic: XML to SDO_GEOMETRY object migration
Goto Forum:
  


Current Time: Thu Mar 28 06:42:48 CDT 2024