1 2 Previous Next 18 Replies Latest reply: May 4, 2012 8:32 AM by odie_63 Go to original post RSS
      • 15. Re: create meterilized view to parse the xmltype column
        odie_63
        OK, in this case you can use string-join() function directly in the PATH clause to aggregate repeating element values.

        For example, using the very first sample XML you gave :
        SQL> SELECT m.p_mid, x.*
          2  FROM minf_test m
          3     , XMLTable(
          4         XMLNamespaces( 'http://fundtech.com/SCL/CommonTypes' as "fndt"
          5                      , default 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03')
          6       , '/fndt:FndtMsg/fndt:Msg/fndt:Pmnt/Document/CstmrCdtTrfInitn/GrpHdr/InitgPty/PstlAdr'
          7         passing m.xml_msg
          8         columns
          9           postal_code varchar2(6)   path 'PstCd'
         10         , city        varchar2(60)  path 'TwnNm'
         11         , subdivision varchar2(60)  path 'CtrySubDvsn'
         12         , country     varchar2(60)  path 'Ctry'
         13         , address     varchar2(100) path 'string-join(AdrLine, " ")'
         14       ) x
         15  ;
         
             P_MID POSTAL_CODE CITY             SUBDIVISION      COUNTRY   ADDRESS
        ---------- ----------- ---------------- ---------------- --------- --------------------------------
                 1 6019.0      Frankfurt        Frankfurt        GR        GBS FrankFurt 1 Street Name 3
         
        • 16. Re: create meterilized view to parse the xmltype column
          928336
          thank you ...i will try this
          • 17. Re: create meterilized view to parse the xmltype column
            928336
            need help in tunning this query

            WITH
            A AS
            (
            SELECT pain113_CdtTrfTxInf.*
            FROM minf m
            , XMLTable(XMLNamespaces( 'http://fundtech.com/SCL/CommonTypes' as "fndt"
            , default 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03')
            , 'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf'
            passing m.xml_msg
            columns
            BENESORTCDBANKID VARCHAR2(60) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/MmbId'
            ,BENEFICIARYACCOUNT VARCHAR2(100) PATH 'CdtrAcct/Id/Othr/Id'
            ,BENEFICIARYADDRESSLINE1 VARCHAR2(140) PATH 'Cdtr/PstlAdr/string-join(AdrLine, " ")'
            ,BENEFICIARYBANKADDRESSLINE1 VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/PstlAdr/string-join(AdrLine, " ")'
            ,BENEFICIARYBANKCITY VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/PstlAdr/TwnNm'
            ,BENEFICIARYBANKCODE VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Cd'
            )pain113_CdtTrfTxInf
            ) ,
            B AS
            (
            SELECT pain812_CdtTrfTxInf.* FROM MINF M,
            XMLTable(XMLNamespaces( 'http://fundtech.com/SCL/CommonTypes' as "fndt"
            , default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.02' )
            , 'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/FIToFICstmrCdtTrf/CdtTrfTxInf'
            passing m.xml_msg
            columns
            BENESORTCDBANKID VARCHAR2(60) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/MmbId'
            ,BENEFICIARYACCOUNT VARCHAR2(100) PATH 'CdtrAcct/Id/Othr/Id'
            ,BENEFICIARYADDRESSLINE1 VARCHAR2(140) PATH 'Cdtr/PstlAdr/string-join(AdrLine, " ")'
            ,BENEFICIARYBANKADDRESSLINE1 VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/PstlAdr/string-join(AdrLine, " ")'
            ,BENEFICIARYBANKCITY VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/PstlAdr/TwnNm'
            ,BENEFICIARYBANKCODE VARCHAR2(140) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Cd'
            ) pain812_CdtTrfTxInf
            )
            SELECT
            M.P_MID, M.P_MSG_TYPE,
            CASE
            WHEN M.P_MSG_TYPE='Pain_001'
            THEN
            A.BENESORTCDBANKID
            WHEN
            M.P_MSG_TYPE ='Pacs_008'
            THEN
            B.BENESORTCDBANKID
            ELSE
            ''
            END
            BENESORTCDBANKID,
            CASE
            WHEN M.P_MSG_TYPE='Pain_001'
            THEN
            A.BENEFICIARYACCOUNT
            WHEN
            M.P_MSG_TYPE ='Pacs_008'
            THEN
            B.BENEFICIARYACCOUNT
            ELSE
            ''
            END
            BENEFICIARYACCOUNT,
            CASE
            WHEN M.P_MSG_TYPE='Pain_001'
            THEN
            A.BENEFICIARYADDRESSLINE1
            WHEN
            M.P_MSG_TYPE ='Pacs_008'
            THEN
            B.BENEFICIARYADDRESSLINE1
            ELSE
            ''
            END
            BENEFICIARYADDRESSLINE1
            FROM MINF M, A,B


            Thank you .

            Edited by: user12028740 on May 4, 2012 5:58 AM
            • 18. Re: create meterilized view to parse the xmltype column
              odie_63
              Sorry, too few information provided.

              At least give :

              - DDL for the table
              - Sample XML that contains both "CstmrCdtTrfInitn" and "FIToFICstmrCdtTrf" elements, the first sample you gave doesn't have the latter.
              - Explain plan on your system

              and please format your code using
               tags.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              1 2 Previous Next