developers

    Forum Stats

  • 3,873,950 Users
  • 2,266,645 Discussions
  • 7,911,666 Comments

Discussions

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

1052751
1052751 Member Posts: 3
edited Nov 6, 2013 9:31AM in XQuery

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

1052751ranit B2940367

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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 ;
    
    1052751ranit B2940367

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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 ;
    
    1052751ranit B2940367
  • 1052751
    1052751 Member Posts: 3

    Thank You Odie .

    This query worked .

This discussion has been closed.
developers