3 Replies Latest reply on Nov 22, 2019 7:29 AM by mNem

    How can I parse the xml value of following xml

    39bb692e-71bb-476e-bdf8-9ce02df9421f

      How can I parse the xml value of "BizMsgIdr" from following xml

       

      "<DataPDU xmlns="urn:swift:saa:xsd:saa.2.0"> <Revision>2.0.5</Revision> <Body> <AppHdr xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01"> <Fr> <FIId> <FinInstnId> <BICFI>BBAFRDDR</BICFI> </FinInstnId> </FIId> </Fr> <To> <FIId> <FinInstnId> <BICFI>CVDADWF</BICFI> </FinInstnId> </FIId> </To> <BizMsgIdr>1I2093KKA00655955</BizMsgIdr> <MsgDefIdr>pacs.009.001.04</MsgDefIdr> <BizSvc>RTGS</BizSvc> <CreDt>2019-11-21T14:41:27Z</CreDt> </AppHdr> </Body> </DataPDU> "

        • 1. Re: How can I parse the xml value of following xml
          mNem

          with t (xmlstr) as

          (

            SELECT

            '<DataPDU xmlns="urn:swift:saa:xsd:saa.2.0">

               <Revision>2.0.5</Revision>

               <Body>

                  <AppHdr xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01">

                     <Fr>

                        <FIId>

                           <FinInstnId>

                              <BICFI>BBAFRDDR</BICFI>

                           </FinInstnId>

                        </FIId>

                     </Fr>

                     <To>

                        <FIId>

                           <FinInstnId>

                              <BICFI>CVDADWF</BICFI>

                           </FinInstnId>

                        </FIId>

                     </To>

                     <BizMsgIdr>1I2093KKA00655955</BizMsgIdr>

                     <MsgDefIdr>pacs.009.001.04</MsgDefIdr>

                     <BizSvc>RTGS</BizSvc>

                     <CreDt>2019-11-21T14:41:27Z</CreDt>

                  </AppHdr>

               </Body>

            </DataPDU>' from dual

          )

          select x.* from t, xmltable(

            xmlnamespaces(

              'urn:swift:saa:xsd:saa.2.0' as "ns"

              , default 'urn:iso:std:iso:20022:tech:xsd:head.001.001.01'

            )

            ,

            '/ns:DataPDU/ns:Body/AppHdr'

            passing xmltype(t.xmlstr)

            columns

            bizmsgidr varchar2(50) path 'BizMsgIdr'

            /* if you need more columns add them here as shown */

            , bizsvr varchar2(20) path 'BizSvc'

          )x

          ;

          BIZMSGIDR                                          BIZSVR             
          -------------------------------------------------- --------------------
          1I2093KKA00655955                                  RTGS               

           

          Note: I formatted the xml string for visualizing the structure of xml. It could well be left as you posted in one line.

           

          • 2. Re: How can I parse the xml value of following xml
            Raj__K

            Hi, Using XPath is a good way to access the values in a node. Ex: Use the xpath /DataPDU/Body/AppHdr/BizMsgIdr to access the value.

             

            Regards!

            • 3. Re: How can I parse the xml value of following xml
              mNem

              Here is another way to get the value ...

              select 
              xmlcast(
                xmlquery(
                  '
                  declare                 namespace ns="urn:swift:saa:xsd:saa.2.0"; (: dummy comment after semi colon :)
                  declare default element namespace    "urn:iso:std:iso:20022:tech:xsd:head.001.001.01"; (: :)
                  /ns:DataPDU/ns:Body/AppHdr/BizMsgIdr
                  '
                  passing xmltype('<DataPDU xmlns="urn:swift:saa:xsd:saa.2.0"> <Revision>2.0.5</Revision> <Body> <AppHdr xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01"> <Fr> <FIId> <FinInstnId> <BICFI>BBAFRDDR</BICFI> </FinInstnId> </FIId> </Fr> <To> <FIId> <FinInstnId> <BICFI>CVDADWF</BICFI> </FinInstnId> </FIId> </To> <BizMsgIdr>1I2093KKA00655955</BizMsgIdr> <MsgDefIdr>pacs.009.001.04</MsgDefIdr> <BizSvc>RTGS</BizSvc> <CreDt>2019-11-21T14:41:27Z</CreDt> </AppHdr> </Body> </DataPDU>')
                  returning content
                ) 
                as varchar2(50)
              ) bizmsgidr
              from dual
              ;