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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.