1 Reply Latest reply: Jul 14, 2013 8:14 PM by form_dev RSS

    Xmltable extraction from soap

    form_dev

      Hi,

       

      I called a web service and in response got an XML data, now I have to extract the values,  and dispaly it using  Sql statement

      I need to display RemarkData,  if possible all the fields.

       

      <!-- Response -->
      <SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">
         <SOAP:Body>
            <air:AirCreateReservationRsp TransactionId="9C77DFE40A0771C30084963DBE1B9BCE" ResponseTime="49014" xmlns:air="http://www.XXXXport.com/schema/air_v18_0"

      xmlns:common_v15_0="http://www.XXXXport.com/schema/common_v15_0">
               <common_v15_0:ResponseMessage Code="3715" Type="Warning" ProviderCode="1G">Host only allows one Address/Delivery Address. Only one sent in

      request.</common_v15_0:ResponseMessage>
               <universal:UniversalRecord LocatorCode="DVT635" Version="0" Status="Active" xmlns:universal="http://">
                  <common_v15_0:GeneralRemark>
                        <common_v15_0:RemarkData>AC ETKT# 0142102009130 FOR Michael Jones</common_v15_0:RemarkData>
                     </common_v15_0:GeneralRemark>
                     <common_v15_0:SupplierLocator SupplierCode="AC" SupplierLocatorCode="LB54AR" ProviderReservationInfoRef="618979"/>
                     <air:DocumentInfo>
                        <air:TCRInfo Status="Confirmed" Date="2012-02-21T07:34:02.000+11:00" TCRNumber="75021" ProviderReservationInfoRef="618979"/>
                     </air:DocumentInfo>
                     <common_v15_0:Payment Key="110411" Type="Itinerary" Amount="CAD1477.32" ApproximateAmount="AUD1444.53" FormOfPaymentRef="125090"/>
                 
               </universal:UniversalRecord>
               </air:AirCreateReservationRsp>
         </SOAP:Body>
      </SOAP:Envelope>

      ==============================================

      I am storing this  in table   tmp_xml1  column xml

       

      CREATE TABLE "HR"."TMP_XML1"

         ( "XML" "XMLTYPE"

         )

      ===========================

      If I run this query I am getting null value

       

      SELECT xt.*

         FROM tmp_xml1 t      ,

      XMLTable(XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soap",

                                      'http://www.XXXXport.com/schema/common_v15_0' as "common_v15_0",

                                      'http://' as "universal",

                                      'http://www.XXXXport.com/schema/air_v18_0' as "air" )

                       , '/soap:Envelope/soap:Body/air:AirCreateReservationRsp'          

                        passing    t.xml

                        COLUMNS

                        body_xml   varchar2(49) PATH 'common_v15_0:ResponseMessage') xt;

      =============================================

      Any help, would be greatly appreciated.

       

      Thanking you in advance.