| How to Select xml node from oracle database.. Help !! Project Is Due Soon....T.T [message #557179] |
Sun, 10 June 2012 05:32  |
 |
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  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
).