2 Replies Latest reply: Jan 17, 2013 9:42 AM by user12011892 RSS

    Searching xmltype column and listing the contents in relational format

    user12011892
      Hi All,

      I have a 11gR1 database table which contains xmltype column. I am trying to write a query which displays xml conetents in relational format.
      SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jan 17 11:45:06 2013
      
      Copyright (c) 1982, 2008, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE 11.1.0.7.0 Production
      TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - Production

      SQL> Desc BULKMESSAGES;
      Name Null Type
      --------------- ---- -------------
      TRANSFER_ID VARCHAR2(100)
      TRANSFER_LOGS XMLTYPE()
      TRANSFER_RESULT VARCHAR2(10)

      SQL>select * from BULKMESSAGES
      where TRANSFER_RESULT ='REJECTED'
      /
      TRANSFER_ID TRANSFER_LOGS TRANSFER_RESULT
      ---------------- ---------------------------------------------------------------- ---------------
      T3217 <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"> REJECTED



      TRNASFER_LOGS xmltype columns stores SOAP responses. Sample soap response (dummied) from TRANSFER_LOGS xml column is below:
      <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
           <env:Header xmlns:env="http://www.w3.org/2003/05/soap-envelope"/>
           <env:Body xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                <env:Fault xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                     <env:Code xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                          <env:Value xmlns:env="http://www.w3.org/2003/05/soap-envelope">env:Receiver</env:Value>
                          <env:Subcode xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                               <env:Value xmlns:fault="http://www.es.com/soapfaults" xmlns:env="http://www.w3.org/2003/05/soap-envelope">fault:MessageBlocked</env:Value>
                          </env:Subcode>
                     </env:Code>
                     <env:Reason xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                          <env:Text lang="en" xmlns:env="http://www.w3.org/2003/05/soap-envelope">connection rejected</env:Text>
                     </env:Reason>
                     <env:Detail xmlns:fault="http://www.es.com/soapfaults" fault:type="faultDetails" fault:messageId="0000013a6" xmlns:env="http://www.w3.org/2003/05/soap-envelope">
                          <fault:path policy="Request Message">
                               <fault:filter status="Fail" name="Call 'Process request '">
                                    <fault:path policy="Process xml ">
                                         <fault:filter status="Fail" name="Call 'Call 3rdparty service'">
                                              <fault:path policy="Call external service">
                                                   <fault:filter status="Pass" name="Extract message"/>
                                              </fault:path>
                                         </fault:filter>
                                    </fault:path>
                               </fault:filter>
                          </fault:path>
                          <fault:attributes>
                               <fault:attribute name="monitoring.enabled" value="true"/>
                               <fault:attribute name="requestor" value="none"/>
                               <fault:attribute name="circuit.failure.reason" value="connection rejected"/>
                               <fault:attribute name="message_protocol" value="SOAP"/>
                               <fault:attribute name="user_id" value="31274556"/>
                               <fault:attribute name="ws_name" value="PUSHDOC"/>
                               <fault:attribute name="user.enabled" value="N"/>
                               <fault:attribute name="http.request.clientaddr" value="/10.254.123.44:43840"/>
                               <fault:attribute name="soap_action" value="pushDocument"/>
                               <fault:attribute name="message.reception_time" value="1349769769742"/>
                               <fault:attribute name="user.api_logging_flag" value="Y"/>
                               <fault:attribute name="circuit.exception" value="com.es.circuit.CircuitAbortException: connection rejected"/>
                               <fault:attribute name="end_point" value="https://es.live.com/pdfs"/>
                               <fault:attribute name="message.protocol.type" value="http"/>
                               <fault:attribute name="user.incoming_date" value="09-11-2013 09:02:49"/>
                               <fault:attribute name="http.destination.host" value="es.live.com"/>
                               <fault:attribute name="message.local.address" value="/100.25.40.82:8085"/>
                               <fault:attribute value="N"/>
                          </fault:attributes>
                     </env:Detail>
                </env:Fault>
           </env:Body>
      </env:Envelope>
      My goal is to search TRANSFER_LOGS documents for rows which contain fault:attribute name="circuit.exception". Then report “fault:attribute name” and theirs values in relational format. For example:
      TRANSFER_ID    TRANSFER_RESULT FAULT_FAIL_REASON    FAULT_CIRCUIT_EXCEPTION                               FAULT_WS_NAME   FAULT_DESTINATION_HOST
      ----------     --------------- ----------------     -------------------------------------------------------- -------------   ---------------------
      T3217          REJECTED       CONNECTION REJECTED  com.es.circuit.CircuitAbortException: connection rejected  PUSHDOC       es.live.com
      I am new to XML oracle sql querying. Documents in TRANSFER_LOGS xmltype column have lot duplicate namespaces and the query I have been trying is not working. So any help or pointers to search and extract TRANSFER_LOGS in above format is greatly appreciated.

      Many thanks in advance.

      VC
        • 1. Re: Searching xmltype column and listing the contents in relational format
          odie_63
          Hi,

          You may use something like this :
          SQL> select t.transfer_id, t.transfer_result, x.*
            2  from bulkmessages t
            3     , xmltable(
            4         xmlnamespaces(
            5           'http://www.w3.org/2003/05/soap-envelope' as "e"
            6         , 'http://www.es.com/soapfaults' as "f"
            7         )
            8       , '/e:Envelope/e:Body/e:Fault/e:Detail/f:attributes'
            9         passing t.transfer_logs
           10         columns
           11           FAULT_FAIL_REASON       varchar2(200) path 'f:attribute[@name="circuit.failure.reason"]/@value'
           12         , FAULT_CIRCUIT_EXCEPTION     varchar2(200) path 'f:attribute[@name="circuit.exception"]/@value'
           13         , FAULT_WS_NAME           varchar2(200) path 'f:attribute[@name="ws_name"]/@value'
           14         , FAULT_DESTINATION_HOST  varchar2(200) path 'f:attribute[@name="http.destination.host"]/@value'
           15         ) x
           16  where xmlexists(
           17         'declare namespace e = "http://www.w3.org/2003/05/soap-envelope"; (: :)
           18          declare namespace f = "http://www.es.com/soapfaults"; (: :)
           19          /e:Envelope/e:Body/e:Fault/e:Detail/f:attributes/f:attribute[@name="circuit.exception"]'
           20          passing t.transfer_logs
           21        ) ;
           
          TRANSFER_ID     TRANSFER_RESULT FAULT_FAIL_REASON        FAULT_CIRCUIT_EXCEPTION                                       FAULT_WS_NAME    FAULT_DESTINATION_HOST
          --------------- --------------- ------------------------ ------------------------------------------------------------- ---------------- -------------------------
          T3217           REJECTED        connection rejected      com.es.circuit.CircuitAbortException: connection rejected     PUSHDOC          es.live.com
           
          Or, more simply :
          SQL> select t.transfer_id, t.transfer_result, x.*
            2  from bulkmessages t
            3     , xmltable(
            4         xmlnamespaces(
            5           'http://www.w3.org/2003/05/soap-envelope' as "e"
            6         , 'http://www.es.com/soapfaults' as "f"
            7         )
            8       , '/e:Envelope/e:Body/e:Fault/e:Detail/f:attributes'
            9         passing t.transfer_logs
           10         columns
           11           FAULT_FAIL_REASON       varchar2(200) path 'f:attribute[@name="circuit.failure.reason"]/@value'
           12         , FAULT_CIRCUIT_EXCEPTION     varchar2(200) path 'f:attribute[@name="circuit.exception"]/@value'
           13         , FAULT_WS_NAME           varchar2(200) path 'f:attribute[@name="ws_name"]/@value'
           14         , FAULT_DESTINATION_HOST  varchar2(200) path 'f:attribute[@name="http.destination.host"]/@value'
           15         ) x
           16  where x.FAULT_CIRCUIT_EXCEPTION is not null ;
           
          TRANSFER_ID    TRANSFER_RESULT FAULT_FAIL_REASON      FAULT_CIRCUIT_EXCEPTION                                      FAULT_WS_NAME      FAULT_DESTINATION_HOST                                                        
          -------------- --------------- ---------------------- ------------------------------------------------------------ ------------------ --------------------------
          T3217          REJECTED        connection rejected    com.es.circuit.CircuitAbortException: connection rejected    PUSHDOC            es.live.com               
            
          • 2. Re: Searching xmltype column and listing the contents in relational format
            user12011892
            Hi Odie_63,

            Thank you for your prompt response. You are a star. Both methods you have mentioned I have tried on full size database and they work great. I have learnt new way of querying oracle xml data today feel very happy. Thanks a million.

            VC