DevOps, CI/CD and Automation

Hi,

I am trying to fetch the xml values using the XMLTABLE query, I can able to fetch only the UPPER case xml element values, if i tried combination of upper and lower case xml elements i didnt get any values. I have mentioned blue color in the below example. I want to fetch the xml values in combination of upper and lowercase also.

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>15121A</ORGNLMSGID>

          <ORGNLMSGNMID>PAIN.001.001.02</ORGNLMSGNMID>

          <ORGNLNBOFTXS>50</ORGNLNBOFTXS>

          <ORGNLCTRLSUM>6000</ORGNLCTRLSUM>

          <GRPSTS>ACTC</GRPSTS>

          <STSRSNINF>

            <ADDTLSTSRSNINF>ACK - FILE ACCEPTED</ADDTLSTSRSNINF>

          </STSRSNINF>

        </ORGNLGRPINFANDSTS>

        <ORGNLGRPINFANDSTS>

          <ORGNLMSGID>15121B</ORGNLMSGID>

          <ORGNLMSGNMID>PAIN.001.001.02</ORGNLMSGNMID>

          <ORGNLNBOFTXS>20</ORGNLNBOFTXS>

          <ORGNLCTRLSUM>2000</ORGNLCTRLSUM>

          <GRPSTS>ACTC</GRPSTS>

          <STSRSNINF>

            <ADDTLSTSRSNINF>ACK - FILE ACCEPTED</ADDTLSTSRSNINF>

          </STSRSNINF>

        </ORGNLGRPINFANDSTS>

        <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>

    </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),

             OrgnlMsgId VARCHAR2(24),

             ORGNLNBOFTXS VARCHAR2(24),

             ORGNLCTRLSUM NUMBER,

             GRPSTS      VARCHAR2(24),

             ADDTLSTSRSNINF  VARCHAR2(24)                       

         ) r

This is the script output

---------- ------------------------ ------------------------ ------------ ------------------------ ------------------------

Thanks in advance. Please help me on this.

Thanks,

Chidambaram