SQL & PL/SQL

Hi

Hope somebody can help me with the is xml.

I'm trying to extract a value from an xml tag, where the property is equal to "MASTER". There is a prefix involved, which is set using xmlnamespaces:

set serveroutput on

declare

   xml xmltype := xmltype(

'<?xml version="1.0" encoding="utf-8"?>

<fti:transactionNotification xmlns:fti="urn:lingua:fpmltransaction"

                             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                             xmlns="http://www.fpml.org/FpML-5/confirmation"

                             xmlns:ftr="urn:lingua:fpmltransactionreference"

                             xmlns:fts="urn:lingua:fpmltransactionshared"

                             xmlns:base="urn:lingua:base"

                             fpmlVersion="5-9"

                             schemaVersion="3.1.0">

   <header>

      <messageId messageIdScheme="http://www.XXX.com/msg_id">2348012_1_20180112210642871</messageId>

      <sentBy messageAddressScheme="YYYYY">YYYYY</sentBy>

      <sendTo>XXX</sendTo>

      <creationTimestamp>2018-01-12T21:06:42Z</creationTimestamp>

      <partyMessageInformation xsi:type="fts:PartyMessageInformation">

         <partyReference href="Party1"/>

         <fts:event>

            <fts:type>Inception</fts:type>

            <fts:subtype>Clearing</fts:subtype>

            <fts:action>New</fts:action>

            <fts:initiatedBy>TradeBooking</fts:initiatedBy>

            <fts:frontOfficeValidated>true</fts:frontOfficeValidated>

         </fts:event>

      </partyMessageInformation>

   </header>

  <trade>

      <swap>

         <productType productTypeScheme="YYYYYTradeFamily">ABC</productType>

         <productType productTypeScheme="YYYYYTradeGroup">DEF</productType>

         <productId productIdScheme="PTX">6684</productId>

         <swapStream>

            <payerPartyReference href="Party1"/>

            <receiverPartyReference href="Party2"/>

            <calculationPeriodDates id="leg_1_calculationPeriodDates">

               <effectiveDate>

                  <unadjustedDate>2018-06-20</unadjustedDate>

                  <dateAdjustments>

                     <businessDayConvention>NONE</businessDayConvention>

                  </dateAdjustments>

                  <adjustedDate>2018-06-20</adjustedDate>

               </effectiveDate>

               <terminationDate>

                  <unadjustedDate>2028-06-20</unadjustedDate>

                  <dateAdjustments>

                     <businessDayConvention>NONE</businessDayConvention>

                  </dateAdjustments>

                  <adjustedDate>2028-06-20</adjustedDate>

               </terminationDate>

               <calculationPeriodFrequency>

                  <periodMultiplier>6</periodMultiplier>

                  <period>M</period>

               </calculationPeriodFrequency>

            </calculationPeriodDates>

            <paymentDates id="leg_1_paymentDates">

               <paymentFrequency>

                  <periodMultiplier>6</periodMultiplier>

                  <period>M</period>

               </paymentFrequency>

               <paymentDatesAdjustments>

                  <businessCenters>

                     <businessCenter>USNY</businessCenter>

                  </businessCenters>

               </paymentDatesAdjustments>

            </paymentDates>

            <calculationPeriodAmount>

               <calculation>

                  <notionalSchedule id="leg_1_notionalSchedule">

                     <notionalStepSchedule>

                        <initialValue>1200000</initialValue>

                        <currency>USD</currency>

                     </notionalStepSchedule>

                  </notionalSchedule>

               </calculation>

            </calculationPeriodAmount>

            <cashflows>

               <cashflowsMatchParameters>true</cashflowsMatchParameters>

               <paymentCalculationPeriod xsi:type="fts:PaymentCalculationPeriod">

                  <adjustedPaymentDate>2018-12-20</adjustedPaymentDate>

                  <calculationPeriod>

                     <unadjustedStartDate>2018-06-20</unadjustedStartDate>

                     <unadjustedEndDate>2018-12-20</unadjustedEndDate>

                     <adjustedStartDate>2018-06-20</adjustedStartDate>

                     <adjustedEndDate>2018-12-20</adjustedEndDate>

                     <notionalAmount>1200000</notionalAmount>

                     <fixedRate>0.019</fixedRate>

                  </calculationPeriod>

                  <presentValueAmount>

                     <currency>USD</currency>

                     <amount>1901</amount>

                  </presentValueAmount>

                  <fts:paymentAmount>1901</fts:paymentAmount>

               </paymentCalculationPeriod>

            </cashflows>

         </swapStream>

         <additionalPayment>

            <payerPartyReference href="Party1"/>

            <receiverPartyReference href="Party2"/>

            <paymentAmount>

               <currency>USD</currency>

               <amount>445566</amount>

            </paymentAmount>

            <paymentDate><adjustedDate>2018-01-16</adjustedDate></paymentDate>

            <paymentType>STL</paymentType>

         </additionalPayment>

      </swap>

   </trade>

   <party id="Party1">

      <partyId partyIdScheme="MASTER">521374</partyId>

      <partyId partyIdScheme="TESTX">4001</partyId>

      <person id="Person1">

         <personId personIdScheme="PGI">43487072</personId>

      </person>

      <person id="Person2">

         <personId personIdScheme="PGI">43487072</personId>

      </person>

   </party>

   <party id="Party2">

      <partyId partyIdScheme="XXX-ZZ-GL">9576414</partyId>

   </party>

</fti:transactionNotification>');

l_var varchar2(50);

begin

  select x.part1_id into l_var

  from

  ( select xml from dual ) d

  ,xmltable(xmlnamespaces('xmlns:fti="urn:lingua:fpmltransaction"' as "fti"), '/fti:transactionNotification' passing d.xml

              columns part1_id varchar2(40) path '/fti/party[@id eq "Party1"]/partId[@partyIdScheme eq "MASTER"') as x;

  dbms_output.put_line('Var value : ' || l_var);

end;

/

The exception I get however is :

declare

*

ERROR at line 1:

ORA-19114: XPST0003 - error during parsing the XQuery expression:

LPX-00801: XQuery syntax error at 'urn:lingua'

1   declare namespace fti="xmlns:fti="urn:lingua:fpmltransaction"";/fti:transac

-                                    ^

ORA-06512: at line 136

I'm using 11gR2. Any ideas as to what it might be ?