This discussion is archived
2 Replies Latest reply: Nov 6, 2013 6: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 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 ;