XML Database (MOSC)

MOSC Banner

optimal XML query

edited Apr 23, 2012 10:29AM in XML Database (MOSC) 1 commentAnswered
We have a column in a table defined as XMLTYPE stored as BINARY XML.  To query the individual fields in the table including the RESPONSEXML column we run a SELECT statement such as :

select
    si.TRANSACTION_ID as "Transaction Id",
    trunc(si.AUDIT_DATE) as "Audit Request Date",
    si.AUDIT_DATE as "Audit Request Date/Time",
    storedResults."WReference",
    storedResults."PReference",
    response."ICode",
    response."IName"   
FROM SI_TEST_XMLTYPE si ,
     xmltable (
       '/.'
        PASSING xmlType(si.RESPONSEXML)
        COLUMNS
           "WReference" Varchar2(20) PATH 'wReference',
           "PReference" Varchar2(255) PATH 'preference',
           "storedResult" xmltype PATH 'storedResults/storedResult'
      ) storedResults,
     xmltable (
   --    xmlnamespaces('http://fakecompany.co.uk/test'

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center