1 Reply Latest reply: Jul 16, 2013 5:20 AM by odie_63 RSS

    Why doesn't my XMLTable function work?

    e40540ce-cba4-478f-8f50-6673e063b98b

      Hi,

       

      I'm trying to read through my XML document using a XMLTable function, because a repetition of elements might occur. I wrote a SQL-script to test this function, but it does not give me the desired output. This is my script. I use a dummy table PETER_XML to pass the value to the XMLTable function. I would rather have done this directly with a PL/SQL variable, but that did not work.

       

      CREATE TABLE PETER_XML (AVY XMLTYPE);

       

      set serveroutput on size 100000
      set echo on
      set feedback on

       

      declare

      cursor c_avy is
      SELECT XMLRESPONSE."DepartureStation" DEPARTURE,
             XMLRESPONSE."FlightNumber"     FLIGHTNR
      FROM   PETER_XML,
             XMLTABLE(XMLNameSpaces('http://schemas.navitaire.com/WebServices' as "web",
                                    'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
                                    'http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService' as "book",
                                    'http://schemas.navitaire.com/WebServices/DataContracts/Booking' as "book1",
                                    'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "arr",
                                    'http://schemas.navitaire.com/WebServices/DataContracts/Common/Enumerations' as "enum",
                                    'http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService' as "ns0",
                                    'http://schemas.navitaire.com/WebServices/DataContracts/Booking' as "ns1" ),
                      '//GetAvailabilityRequest/TripAvailabilityRequest/AvailabilityRequests/AvailabilityRequest'
                      PASSING PETER_XML.AVY
                      COLUMNS
                        "DepartureStation" varchar2(3) PATH 'DepartureStation',
                        "FlightNumber"     varchar2(4) PATH 'FlightNumber'
                     ) XMLRESPONSE;

       

      l_response clob := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://schemas.navitaire.com/WebServices" xmlns:book="http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService" xmlns:book1="http://schemas.navitaire.com/WebServices/DataContracts/Booking" xmlns:arr="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:enum="http://schemas.navitaire.com/WebServices/DataContracts/Common/Enumerations">
         <soapenv:Header>
            <web:Signature>bnuOiHCVb3k=|Gx+eTRcZ5ABozAy8MosBFwagyUw7zrRXf1iprmw9Q4W17wt8SDpjYV2HwZRGIHYtE46UFBJw/aFyKVqjToEAfSTfh7cePm4r9JJwcIveDc75NuxnzoY14pKC+WLYDzE0MaALra4i/tI=</web:Signature>
            <web:ContractVersion>340</web:ContractVersion>
         </soapenv:Header>
         <soapenv:Body>
                <ns0:GetAvailabilityRequest xmlns:ns0 = "http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService">
                         <ns1:TripAvailabilityRequest xmlns:ns1 = "http://schemas.navitaire.com/WebServices/DataContracts/Booking">
                                   <ns1:AvailabilityRequests>
                                            <ns1:AvailabilityRequest>
                                                      <ns1:DepartureStation>AMS</ns1:DepartureStation>
                                                      <ns1:ArrivalStation>CTA</ns1:ArrivalStation>
                                                      <ns1:BeginDate>2013-07-13T00:00:00</ns1:BeginDate>
                                                      <ns1:EndDate>2013-07-13T00:00:00</ns1:EndDate>
                                                      <ns1:CarrierCode>HV</ns1:CarrierCode>
                                                      <ns1:FlightNumber> 547</ns1:FlightNumber>
                                                      <ns1:FlightType>All</ns1:FlightType>
                                                      <ns1:PaxCount>1</ns1:PaxCount>
                                                      <ns1:Dow>Daily</ns1:Dow>
                                                      <ns1:CurrencyCode>EUR</ns1:CurrencyCode>
                                                      <ns1:DisplayCurrencyCode>EUR</ns1:DisplayCurrencyCode>
                                                      <!--ns1:SourceOrganization>COO</ns1:SourceOrganization-->
                                                      <ns1:MaximumConnectingFlights>0</ns1:MaximumConnectingFlights>
                                                      <ns1:AvailabilityFilter>Default</ns1:AvailabilityFilter>
                                                      <ns1:ProductClassCode>NG</ns1:ProductClassCode>
                                                      <ns1:SSRCollectionsMode>None</ns1:SSRCollectionsMode>
                                                      <ns1:InboundOutbound>Both</ns1:InboundOutbound>
                                                      <ns1:NightsStay>0</ns1:NightsStay>
                                                      <ns1:IncludeAllotments>true</ns1:IncludeAllotments>
                                                      <ns1:FareTypes>
                                                                <ns2:string xmlns:ns2 = "http://schemas.microsoft.com/2003/10/Serialization/Arrays">T</ns2:string>
                                                      </ns1:FareTypes>
                                                      <ns1:PaxPriceTypes>
                                                                <ns1:PaxPriceType>
                                                                         <ns1:PaxType>ADT</ns1:PaxType>
                                                                </ns1:PaxPriceType>
                                                      </ns1:PaxPriceTypes>
                                                      <ns1:JourneySortKeys>
                                                                <ns2:JourneySortKey xmlns:ns2 = "http://schemas.navitaire.com/WebServices/DataContracts/Common/Enumerations">EarliestDeparture</ns2:JourneySortKey>
                                                      </ns1:JourneySortKeys>
                                                      <ns1:IncludeTaxesAndFees>false</ns1:IncludeTaxesAndFees>
                                                      <ns1:FareRuleFilter>Default</ns1:FareRuleFilter>
                                                      <ns1:LoyaltyFilter>MonetaryOnly</ns1:LoyaltyFilter>
                                                      <ns1:TravelClassCodeList>
                                                                <ns2:string xmlns:ns2 = "http://schemas.microsoft.com/2003/10/Serialization/Arrays">Y</ns2:string>
                                                      </ns1:TravelClassCodeList>
                                            </ns1:AvailabilityRequest>
                                   </ns1:AvailabilityRequests>
                                   <ns1:LoyaltyFilter>MonetaryOnly</ns1:LoyaltyFilter>
                         </ns1:TripAvailabilityRequest>
                </ns0:GetAvailabilityRequest>
         </soapenv:Body>
      </soapenv:Envelope>';

      begin

      dbms_output.put_line ('Start');
      insert into peter_xml (avy) values ( XMLTYPE(l_response) );


      dbms_output.put_line ('Insert processed ' || to_char(sql%rowcount) || ' rows.' );

      for r_avy in c_avy loop

         dbms_output.put_line ( 'Departure ' || r_avy.departure || ' Flightno. ' || r_avy.flightnr );

      end loop;

      end;
      /

      rollback;

       

      The script runs fine, but the problem is that I do not get any output form the cursor for-loop. When I address the elements using 'ns1:etc' I get an error, which suggests that the function is actually reading the XML. Since it gave no output, I started including all these XMLNameSpaces.

       

      Can anyone let me know what I'm missing?

       

      And if I can pass the value with a PL/SQL variable, it would save me the use of a dummy table. That would be nice.

       

      Thanks in advance.

       

      Peter

        • 1. Re: Why doesn't my XMLTable function work?
          odie_63

          Hi Peter,

           

          Since it gave no output, I started including all these XMLNameSpaces.

           

          All these namespaces have meaning, don't include them blindly, but if you do, use them.

          The main XQuery expression doesn't reference any of the necessary namespaces.

           

          Here's a simplified version that gives the expected output.

          Please note that I only use the namespaces I need to resolve the XQuery :

           

          declare

           

            cursor c_avy (p_xmlresponse in xmltype) is

              select x.DEPARTURE

                   , x.FLIGHTNR

              from xmltable(

                     xmlnamespaces(

                       'http://schemas.xmlsoap.org/soap/envelope/' as "soap"

                     , 'http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService' as "ns0"

                     , 'http://schemas.navitaire.com/WebServices/DataContracts/Booking' as "ns1"

                     )

                   , '/soap:Envelope/soap:Body/ns0:GetAvailabilityRequest/ns1:TripAvailabilityRequest/ns1:AvailabilityRequests/ns1:AvailabilityRequest'

                     passing p_xmlresponse

                     columns

                       DEPARTURE varchar2(3) path 'ns1:DepartureStation'

                     , FLIGHTNR  varchar2(4) path 'ns1:FlightNumber'

                   ) x ;

           

           

            l_response clob := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://schemas.navitaire.com/WebServices" xmlns:book="http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService" xmlns:book1="http://schemas.navitaire.com/WebServices/DataContracts/Booking" xmlns:arr="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:enum="http://schemas.navitaire.com/WebServices/DataContracts/Common/Enumerations">

             <soapenv:Header>

                <web:Signature>bnuOiHCVb3k=|Gx+eTRcZ5ABozAy8MosBFwagyUw7zrRXf1iprmw9Q4W17wt8SDpjYV2HwZRGIHYtE46UFBJw/aFyKVqjToEAfSTfh7cePm4r9JJwcIveDc75NuxnzoY14pKC+WLYDzE0MaALra4i/tI=</web:Signature>

                <web:ContractVersion>340</web:ContractVersion>

             </soapenv:Header>

             <soapenv:Body>

                    <ns0:GetAvailabilityRequest xmlns:ns0 = "http://schemas.navitaire.com/WebServices/ServiceContracts/BookingService">

                             <ns1:TripAvailabilityRequest xmlns:ns1 = "http://schemas.navitaire.com/WebServices/DataContracts/Booking">

                                       <ns1:AvailabilityRequests>

                                                <ns1:AvailabilityRequest>

                                                          <ns1:DepartureStation>AMS</ns1:DepartureStation>

                                                          <ns1:ArrivalStation>CTA</ns1:ArrivalStation>

                                                          <ns1:BeginDate>2013-07-13T00:00:00</ns1:BeginDate>

                                                          <ns1:EndDate>2013-07-13T00:00:00</ns1:EndDate>

                                                          <ns1:CarrierCode>HV</ns1:CarrierCode>

                                                          <ns1:FlightNumber> 547</ns1:FlightNumber>

                                                          <ns1:FlightType>All</ns1:FlightType>

                                                          <ns1:PaxCount>1</ns1:PaxCount>

                                                          <ns1:Dow>Daily</ns1:Dow>

                                                          <ns1:CurrencyCode>EUR</ns1:CurrencyCode>

                                                          <ns1:DisplayCurrencyCode>EUR</ns1:DisplayCurrencyCode>

                                                          <!--ns1:SourceOrganization>COO</ns1:SourceOrganization-->

                                                          <ns1:MaximumConnectingFlights>0</ns1:MaximumConnectingFlights>

                                                          <ns1:AvailabilityFilter>Default</ns1:AvailabilityFilter>

                                                          <ns1:ProductClassCode>NG</ns1:ProductClassCode>

                                                          <ns1:SSRCollectionsMode>None</ns1:SSRCollectionsMode>

                                                          <ns1:InboundOutbound>Both</ns1:InboundOutbound>

                                                          <ns1:NightsStay>0</ns1:NightsStay>

                                                          <ns1:IncludeAllotments>true</ns1:IncludeAllotments>

                                                          <ns1:FareTypes>

                                                                    <ns2:string xmlns:ns2 = "http://schemas.microsoft.com/2003/10/Serialization/Arrays">T</ns2:string>

                                                          </ns1:FareTypes>

                                                          <ns1:PaxPriceTypes>

                                                                    <ns1:PaxPriceType>

                                                                             <ns1:PaxType>ADT</ns1:PaxType>

                                                                    </ns1:PaxPriceType>

                                                          </ns1:PaxPriceTypes>

                                                          <ns1:JourneySortKeys>

                                                                    <ns2:JourneySortKey xmlns:ns2 = "http://schemas.navitaire.com/WebServices/DataContracts/Common/Enumerations">EarliestDeparture</ns2:JourneySortKey>

                                                          </ns1:JourneySortKeys>

                                                          <ns1:IncludeTaxesAndFees>false</ns1:IncludeTaxesAndFees>

                                                          <ns1:FareRuleFilter>Default</ns1:FareRuleFilter>

                                                          <ns1:LoyaltyFilter>MonetaryOnly</ns1:LoyaltyFilter>

                                                          <ns1:TravelClassCodeList>

                                                                    <ns2:string xmlns:ns2 = "http://schemas.microsoft.com/2003/10/Serialization/Arrays">Y</ns2:string>

                                                          </ns1:TravelClassCodeList>

                                                </ns1:AvailabilityRequest>

                                       </ns1:AvailabilityRequests>

                                       <ns1:LoyaltyFilter>MonetaryOnly</ns1:LoyaltyFilter>

                             </ns1:TripAvailabilityRequest>

                    </ns0:GetAvailabilityRequest>

             </soapenv:Body>

          </soapenv:Envelope>';

           

          begin

           

            for r_avy in c_avy (xmltype(l_response)) loop

           

              dbms_output.put_line ( 'Departure ' || r_avy.departure || ' Flightno. ' || r_avy.flightnr );

           

            end loop;

           

          end;

          /

           

          Indeed, you don't need an intermediate table for this requirement. However, and depending on your db version, storing the XML in a binary XMLType table may improve performance dramatically.

          For small contents, you probably won't see a difference between the two approaches, but just so you know in case you have to deal with big XMLs in the future.