1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use SQL to query the XML node stored on database. PROJECT IS GONNA DUE....HELP

Discussion in 'SQL PL/SQL' started by ocs2me, Jun 10, 2012.

  1. ocs2me

    ocs2me Guest

    Im new to SQL n XML.

    What about if i wish to perform like below.

    <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 EXTRACT(T1.root_cause_analysis, '/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!!
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: How to use SQL to query the XML node stored on database. PROJECT IS GONNA DUE....

    If you just want to extract the value from the PREVENTION tag, use the SQL as below.

    Code (SQL):
    WITH rec AS  (
    SELECT xmltype ('<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>'
    ) xmltag
    FROM dual )
    SELECT EXTRACTVALUE(xmltag, '/RCA/PREVENTION')AS
    RCAXML  FROM  rec;