2 Replies Latest reply: Oct 24, 2013 6:26 AM by Chidam K RSS

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

    Chidam K

      Hi ,

       

      I executed the below query in database version 11.2.0.3.0, it throws the error like "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

       

      with PAYMENT_XML as (

            select XMLTYPE(

              '<Document>

              <pain.002.001.02>

                  <GrpHdr>

                        <MsgId>CITIBANK/20091204-PSR/4274</MsgId>

                        <CreDtTm>2009-12-04T09:36:00</CreDtTm>

                 </GrpHdr>

                  <OrgnlGrpInfAndSts>

                  <OrgnlMsgId>10002</OrgnlMsgId>

                  <OrgnlMsgNmId>pain.001.001.02</OrgnlMsgNmId>

                  <OrgnlNbOfTxs>20</OrgnlNbOfTxs>

                  <OrgnlCtrlSum>7000</OrgnlCtrlSum>

                  <GrpSts>PART</GrpSts>

                  <StsRsnInf>

                    <AddtlStsRsnInf>ACK - FILE PARTIALLY SUCCESSFUL</AddtlStsRsnInf>

                  </StsRsnInf>

                </OrgnlGrpInfAndSts>

                <OrgnlGrpInfAndSts>

                  <OrgnlMsgId>10001</OrgnlMsgId>

                  <OrgnlMsgNmId>pain.001.001.02</OrgnlMsgNmId>

                  <OrgnlNbOfTxs>202</OrgnlNbOfTxs>

                  <OrgnlCtrlSum>9000</OrgnlCtrlSum>

                  <GrpSts>PART</GrpSts>

                  <StsRsnInf>

                    <AddtlStsRsnInf>ACK - FILE PARTIALLY SUCCESSFUL</AddtlStsRsnInf>

                    <AddtlStsRsnInf>Formated</AddtlStsRsnInf>

                  </StsRsnInf>

                </OrgnlGrpInfAndSts>

            </pain.002.001.02>

        </Document>') as OBJECT_VALUE1

         from dual

        )

        select R.*

        from PAYMENT_XML,

             XMLTABLE(

             'for $COMP in $COMPANY/Document/pain.002.001.02

                for $DEPT at $DEPTIDX in $COMP/OrgnlGrpInfAndSts

                 return <RESULT>

                          <NAME>{fn:data($COMP/GrpHdr/MsgId)}</NAME>

                          {

                            $DEPT/OrgnlMsgId,

                            $DEPT/OrgnlNbOfTxs,

                            $DEPT/OrgnlCtrlSum,

                            $DEPT/GrpSts,

                            $DEPT/StsRsnInf/AddtlStsRsnInf

                          }

                        </RESULT>'

             passing OBJECT_VALUE1 as "COMPANY"

             columns

               NAME            VARCHAR(10)  path 'NAME',

               OrgnlMsgId      VARCHAR2(24) path 'OrgnlMsgId',

               ORGNLNBOFTXS    VARCHAR2(24) path 'OrgnlNbOfTxs',

               ORGNLCTRLSUM    NUMBER       path 'OrgnlCtrlSum',

               GRPSTS          VARCHAR2(24) path 'GrpSts',

               ADDTLSTSRSNINF  VARCHAR2(40) path 'AddtlStsRsnInf'

           ) r

        ;

       

      Errors comes this part :

       

                  <StsRsnInf>

                    <AddtlStsRsnInf>ACK - FILE PARTIALLY SUCCESSFUL</AddtlStsRsnInf>

                    <AddtlStsRsnInf>Formated</AddtlStsRsnInf>

                  </StsRsnInf>

       

      if i put the single statement for this xml element <AddtlStsRsnInf> it works fine if more than one element comes it raised the error.

       

      i want the output like the below format : want to merge the element value with (, comma)  delimiter with single coloumn value

       

      NAMEORGNLMSGIDORGNLNBOFTXSORGNLCTRLSUMGRPSTSADDTLSTSRSNINF
      CITIBANK/210002207,000PARTACK - FILE PARTIALLY SUCCESSFUL
      CITIBANK/2100012029,000PARTACK - FILE PARTIALLY SUCCESSFUL, Formated

       

      Thanks is advance for reply

       

      Thanks,

      Chidam