Home » Developer & Programmer » JDeveloper, Java & XML » How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T
How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T [message #557179] Sun, 10 June 2012 05:32 Go to next message
ocs2me
Messages: 2
Registered: June 2012
Junior Member
Im new to SQL n XML. And the project is due soon...i really need help from you.


<RCA>

<SUB_PROBLEM_TYPE>
Bla bla bla...............
</SUB_PROBLEM_TYPE>

<SUB_ROOT_CAUSE>
Bla bla bla...............
</SUB_ROOT_CAUSE>

<ELABORATION>
Bla bla bla...............
</ELABORATION>

<PREVENTION>
Bla bla bla...............
</PREVENTION>

</RCA>



I wish to generate in my html form like..


PREVENTION: bla bla bla....

Is there any simple way to query out ?

I try

"SELECT T1.root_cause_analysis.query('/RCA/PREVENTION') as RCAXML from Submission_Record.............."

set rx=server.createobject("adodb.recordset")
rx.open sqlxmltest,con,1,3
while not rx.eof

<table>
<tr>
<td ><% response.write rx("RCAXML")%></td>
</tr>
</table>


BUT IT GIVE ME AN ERROR...HELP!! [ORA-22806: not an object or REF]
Re: How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T [message #557180 is a reply to message #557179] Sun, 10 June 2012 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as ( select
  2  '<RCA>
  3  <SUB_PROBLEM_TYPE>
  4  Bla bla bla...............
  5  </SUB_PROBLEM_TYPE>
  6  <SUB_ROOT_CAUSE>
  7  Bla bla bla...............
  8  </SUB_ROOT_CAUSE>
  9  <ELABORATION>
 10  Bla bla bla...............
 11  </ELABORATION>
 12  <PREVENTION>
 13  Bla bla bla...............
 14  </PREVENTION>
 15  </RCA>' val from dual)
 16  select 'PREVENTION: '||
 17         trim(chr(10) from extractvalue(value(x), '//PREVENTION')) prevention
 18  from data, table(xmlsequence(extract(xmltype(val), '//PREVENTION'))) x
 19  /
PREVENTION
--------------------------------------------------------------------------------
PREVENTION: Bla bla bla...............

Regards
Michel

[Updated on: Sun, 10 June 2012 05:51]

Report message to a moderator

Re: How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T [message #557181 is a reply to message #557179] Sun, 10 June 2012 05:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You must give a lot more information. To tell us "but it give me an error" is no good (by the way, please don't write in all capital letters, it is painful to read). Read the links above and post your question again, and perhaps someone here will be able to assist.
Re: How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T [message #557182 is a reply to message #557181] Sun, 10 June 2012 05:53 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, for once I helped without repeating once more the rules (tired to do it 10 times each day Sad ).

Regards
Michel
Previous Topic: PL/SQL XML Date Time omits Time part
Next Topic: Select from XML
Goto Forum:
  


Current Time: Thu Mar 28 11:39:29 CDT 2024