1 Reply Latest reply on Nov 6, 2013 12:24 PM by odie_63

    ORA-19279: XPTY0004 - XQuery dynamic type mismatch

    cd192724-b922-4e19-ba04-54f0b42ad9c9

      Hi ,

       

      Can you please help me in querying from the following XML .

       

      below query works for 1 iteration . but for multiple sets i am getting following error .

       

      ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

      19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

      *Cause:    The XQuery sequence passed in had more than one item.

      *Action:   Correct the XQuery expression to return a single item sequence.

       

      please help

       

      select X.*

      from abc evt

      ,XMLTABLE (XMLNAMESPACES ('urn:swift:xsd:mtmsg.2011' AS NS2, DEFAULT 'urn:swift:xsd:fin.970.2011'),

               '$msg_xml'  PASSING EVT.col1 as "msg_xml"

                  COLUMNS 

                          F61ValueDate Varchar(40) Path '/F61/ValueDate[1]',

        DebitCreditMark Varchar(40) Path '//Document/MT970/F61a/F61/DebitCreditMark[1]',

                          Amount Varchar(40) Path '//Document/MT970/F61a/F61/Amount[1]',

                          TransactionType Varchar(40) Path '//Document/MT970/F61a/F61/TransactionType[1]',

                          IdentificationCode Varchar(40) Path '//Document/MT970/F61a/F61/IdentificationCode[1]',                   

                          ReferenceForTheAccountOwner Varchar(40) Path '//Document/MT970/F61a/F61/ReferenceForTheAccountOwner[1]',

                          SupplementaryDetails Varchar(40) Path '//Document/MT970/F61a/F61/SupplementaryDetails[1]'            

                ) X

                where EVT.col2 = 2

       

      XML Text

       

      <FinMessage xmlns="urn:swift:xsd:mtmsg.2011" xmlns:FinMessage="urn:swift:xsd:mtmsg.2011">
         <Block1>
            <ApplicationIdentifier>F</ApplicationIdentifier>
            <ServiceIdentifier>01</ServiceIdentifier>
            <LogicalTerminalAddress>IRVTDEFXAXXX</LogicalTerminalAddress>
            <SessionNumber>5252</SessionNumber>
            <SequenceNumber>699163</SequenceNumber>
         </Block1>
         <Block2>
            <OutputIdentifier>O</OutputIdentifier>
            <MessageType>970</MessageType>
            <InputTime>1633</InputTime>
            <MessageInputReference>
               <Date>131101</Date>
               <LTIdentifier>EBASBEBBQ</LTIdentifier>
               <BranchCode>XXX</BranchCode>
               <SessionNumber>1113</SessionNumber>
               <ISN>070016</ISN>
            </MessageInputReference>
            <Date>131101</Date>
            <Time>1634</Time>
            <MessagePriority>N</MessagePriority>
         </Block2>
         <Block3>
            <F108>ENS0000857017566</F108>
         </Block3>
         <Block4>
            <Document xmlns="urn:swift:xsd:fin.970.2011" xmlns:Document="urn:swift:xsd:fin.970.2011">
               <MT970>
                  <F20a>
                     <F20>ENS17566/FINAL</F20>
                  </F20a>
                  <F25a>
                     <F25>IRVTDEFX/EUR/131101/N</F25>
                  </F25a>
                  <F28a>
                     <F28C>
                        <StatementNumber>215</StatementNumber>
                        <SequenceNumber>16</SequenceNumber>
                     </F28C>
                  </F28a>
                  <F60a>
                     <F60M>
                        <DCMark>D</DCMark>
                        <Date>131101</Date>
                        <Currency>EUR</Currency>
                        <Amount>2686836,28</Amount>
                     </F60M>
                  </F60a>
                  <F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>40248,</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>103</IdentificationCode>
                        <ReferenceForTheAccountOwner>FX5445414</ReferenceForTheAccountOwner>
                        <SupplementaryDetails>   KREDBEBBXXXIRVTDEFXXXXN011031</SupplementaryDetails>
                     </F61>
                  </F61a>
                  <F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>41605,</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>103</IdentificationCode>
                        <ReferenceForTheAccountOwner>FX5443846</ReferenceForTheAccountOwner>
                        <SupplementaryDetails>   DEUTDEFFXXXIRVTDEFXXXXN011031</SupplementaryDetails>
                     </F61>
                  </F61a>
                  <F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>43730,</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>103</IdentificationCode>
                        <ReferenceForTheAccountOwner>C13110130526301</ReferenceForTheAccountOwner>
                        <SupplementaryDetails>   BARCGB22XXXIRVTDEFXXXXN011033</SupplementaryDetails>
                     </F61>
                  </F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>58000,</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>202</IdentificationCode>
                        <ReferenceForTheAccountOwner>FXT1311010000500</ReferenceForTheAccountOwner>
                        <ReferenceOfTheAccountServicingInstitution>FXT1311010000500</ReferenceOfTheAccountServicingInstitution>
                        <SupplementaryDetails>   BKAUATWWXXXIRVTDEFXXXXN011116</SupplementaryDetails>
                     </F61>
                  </F61a>
                  <F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>59826,21</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>103</IdentificationCode>
                        <ReferenceForTheAccountOwner>FX5446070</ReferenceForTheAccountOwner>
                        <SupplementaryDetails>   CHASGB2LXXXIRVTDEFXXXXN011031</SupplementaryDetails>
                     </F61>
                  </F61a>
                  <F61a>
                     <F61>
                        <ValueDate>131101</ValueDate>
                        <DebitCreditMark>D</DebitCreditMark>
                        <Amount>60309,4</Amount>
                        <TransactionType>S</TransactionType>
                        <IdentificationCode>103</IdentificationCode>
                        <ReferenceForTheAccountOwner>01987HS016415</ReferenceForTheAccountOwner>
                        <SupplementaryDetails>   EFGBGRAAXXXIRVTDEFXXXXN011458</SupplementaryDetails>
                     </F61>
                  </F61a>
                  <F62a>
                     <F62M>
                        <DCMark>D</DCMark>
                        <Date>131101</Date>
                        <Currency>EUR</Currency>
                        <Amount>3736765,9</Amount>
                     </F62M>
                  </F62a>
               </MT970>
            </Document>
         </Block4>
      </FinMessage>

       

       

       

      Thanks

      Siva Prakash