Below is my scripts:
<%
connstring ="Provider=MSDAORA;Data Source=(DESCRIPTION="&_
"(ADDRESS=(PROTOCOL=TCP)(HOST=cqcmdb1.corp.mot.com) (PORT=1521))"&_
"(CONNECT_DATA=(SID=CQPD2)));User ID=report_ccmpd_ro;Password=report"
set con = server.CreateObject("ADODB.Connection")
con.open connstring
set conn = server.CreateObject("ADODB.Connection")
conn.open connstring
sqltest = "select T1.screen_failure, DBMS_LOB.SUBSTR(T1.ROOT_CAUSE_ANALYSIS,9000,1) AS RCATT from Submission_Record T1 where T1.dbid <> 0 and (T1.Headline like '%[O2O7]%')"
set rs=server.createobject("adodb.recordset")
rs.open sqltest ,con,1,3
sqltestonly = "select xmltype(RCATT).extract('/RCA/SUB_PROBLEM_TYPE/text()').getStringVal() as SPT, xmltype(RCATT).extract('/RCA/PREVENTION/text()').getStringVal() as PRE from (" & (sqltest) & ")"
set rq=server.createobject("adodb.recordset")
rq.open sqltestonly ,conn,1,3
while not rs.eof
IF rq.EOF THEN %> ' if doesnt contain xml, write from normal string database
<%response.write rs("RCATT")%> 'normal string data retrieve
<%ELSE ' if doesnt contain string, write from xml database
response.write rq("PRE")%> 'xml data retrieve
<%END IF%>
<%
rs.movenext
wend
%>
my purpose is retrieve out the data from oracle database and filter them into xml data type and normal string datatype.
For first IF statement, it will prompt out the string data, else it will prompt out xml data and loop the whole database. The problem is everytime it just give me the string type only. It cant filter the xml.