2 Replies Latest reply: Nov 6, 2013 8:31 AM by cd192724-b922-4e19-ba04-54f0b42ad9c9 RSS

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

    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

       

       

       

      <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

        • 1. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
          odie_63

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

          When you want to present repeating groups in relational format, you have to extract the sequence of items in the main XQuery expression.

          Each item is then passed to the COLUMNS clause to be further shredded into columns.

           

          This should work as expected :

          select x.*

          from abc t

             , xmltable(

                 xmlnamespaces(

                   default 'urn:swift:xsd:fin.970.2011'

                 , 'urn:swift:xsd:mtmsg.2011' as "ns0"

                 )

               , '/ns0:FinMessage/ns0:Block4/Document/MT970/F61a/F61'

                 passing t.col1

                 columns F61ValueDate                Varchar(40) Path 'ValueDate'

                       , DebitCreditMark             Varchar(40) Path 'DebitCreditMark'

                       , Amount                      Varchar(40) Path 'Amount'

                       , TransactionType             Varchar(40) Path 'TransactionType'

                       , IdentificationCode          Varchar(40) Path 'IdentificationCode'                 

                       , ReferenceForTheAccountOwner Varchar(40) Path 'ReferenceForTheAccountOwner'

                       , SupplementaryDetails        Varchar(40) Path 'SupplementaryDetails'       

               ) x ;