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%>


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.

5 Years
Discussion Span
Last Post by gs98

are you confusing your rs and rq recordsets - do you need two recordsets (and connection strings)? You are using the first SQL in the second SQL so you should only have the one recordset (rq) and read through that - you are currently using both rq and rs in the while loop

and you are doing <% and %> for comments which is a bit strange...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.