1 2 Previous Next 18 Replies Latest reply: May 4, 2012 8:32 AM by odie_63 RSS

    create meterilized view to parse the xmltype column

    928336
      i am new to XMLDB. today i got a new requirement which is to parse xml string and populate the parsed elements into meterilized view. open for new ideas

      details decription about this xmltype column : i have a table lets say MINF and this table has around 100 columns with in this i have one column MINF.XML_MSG my challange is to parse this (MINF.XML_MGS datatype(XMLTYPE)) column and populate the parsed values into MINF_MV

      sample xml doc of the MINF.XML_MGS column.

      <?xml version="1.0" encoding="UTF-8"?>
      <ns15:FndtMsg xmlns:q1="http://fundtech.com/SCL/CommonTypes" xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q0="http://fundtech.com/SCL/MessageSubmitService" xmlns:ns2="http://payments.baml.com/model/FXQuotesManagerV001" xmlns:ns3="http://payments.cpo.gcib.bofa.com/xsd/fxservice/realtimerates/V001" xmlns:ns5="http://payments.cpo.gcib.bofa.com/xsd/cpsMessageTypes/V001" xmlns:ns6="http://payments.cpo.gcib.bofa.com/xsd/paymentorchestrator/sla/V001" xmlns:ns7="http://payments.cpo.gcib.bofa.com/xsd/commontypes/V001" xmlns:ns8="http://payments.cpo.gcib.bofa.com/xsd/fxservice/V001" xmlns:ns9="http://pubsubservice.message.ws.services.cp.bofa.com" xmlns:ns10="http://pubsubservice.ws.services.cp.bofa.com/xsd/headertypes/V001" xmlns:ns11="http://payments.baml.com/PaymentsNotificationV001" xmlns:ns12="http://notesservice.pubsub.ws.services.cp.bofa.com" xmlns:ns13="http://ws.entitlements.services.cp.bofa.com/" xmlns:ns14="http://www.bankofamerica.com/cpo/payments/services/approval/v002" xmlns:ns15="http://fundtech.com/SCL/CommonTypes" xmlns:ns16="http://fundtech.com/SCL/MopSelectionService" xmlns:ns17="http://fundtech.com/SCL/MessageLoadService" xmlns:ns18="http://fundtech.com/SCL/ProfileListService" xmlns:ns19="http://fundtech.com/SCL/StaticDataCommonTypes" xmlns:ns20="http://fundtech.com/SCL/QueueListService" xmlns:ns21="http://Fundtech.com/prule-metadata-dto.xsd" xmlns:ns22="http://fundtech.com/SCL/MessageSubmitService" xmlns:ns23="http://www.bankofamerica.com/xmlschema/resource/metadata/osa/infrastructure/v001" xmlns:ns24="http://payments.cpo.gcib.bofa.com/xsd/paymentextn/V001" xmlns:ns25="http://payments.cpo.gcib.bofa.com/xsd/paymentlisttype/V001" xmlns:ns26="http://payments.cpo.gcib.bofa.com/xsd/paymentservice/V001" xmlns:ns27="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:ns28="http://payments.cpo.gcib.bofa.com/xsd/paymentutilityservice/V001" xmlns:ns29="http://www.fixprotocol.org/FIXML-4-4" xmlns:ns30="http://rp.baml.com/fxt/FIXML-4-4" xmlns:ns31="http://rp.baml.com/service/FXQuotesV001" xmlns:fix="http://www.fixprotocol.org/FIXML-4-4" xmlns:v001="http://payments.cpo.gcib.bofa.com/xsd/paymentutilityservice/V001" xmlns:mes="http://fundtech.com/SCL/MessageLoadService" xmlns:env="http://www.w3.org/2003/05/soap-envelope" xmlns:mop="http://fundtech.com/SCL/MopSelectionService">          <ns15:Msg>          <ns15:Pmnt>          <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:n1="http://www.w3.org/2001/XMLSchema-instance">          <CstmrCdtTrfInitn>          <GrpHdr>          <MsgId>10/02/2011 10:39</MsgId>          <CreDtTm>2012-01-20T14:46:04                         </CreDtTm>          <NbOfTxs>1</NbOfTxs>          <InitgPty>          <Nm>pHUBmRP Customer 10</Nm>          <PstlAdr>          <PstCd>6019.0</PstCd>          <TwnNm>Frankfurt</TwnNm>          <CtrySubDvsn>Frankfurt</CtrySubDvsn>          <Ctry>GR</Ctry>                              <AdrLine>GBS FrankFurt 1</AdrLine><AdrLine>Street Name 3</AdrLine></PstlAdr>          <Id>          <OrgId>          <Othr>          <Id>pHUBmRP10</Id>          </Othr>          </OrgId>          </Id>          </InitgPty>          </GrpHdr>          <PmtInf>          <PmtInfId>10/02/2011 10:39</PmtInfId>          <PmtMtd>TRF</PmtMtd>          <ReqdExctnDt>2012-12-20</ReqdExctnDt>          <Dbtr>          <Nm>GBS FrankFurt Debit Account</Nm>          <PstlAdr>          <PstCd>6019</PstCd>          <TwnNm>Frankfurt</TwnNm>          <CtrySubDvsn>Frankfurt</CtrySubDvsn>          <Ctry>GR</Ctry>                              <AdrLine>GBS FrankFurt 1</AdrLine><AdrLine>Street Name 3</AdrLine></PstlAdr>          <Id>          <OrgId>          <Othr>          <Id>pHUBmRP10</Id>          </Othr>          </OrgId>          </Id>          </Dbtr>          <DbtrAcct>          <Id>          <Othr>          <Id>173610125</Id>          </Othr>          </Id>          <Ccy>EUR</Ccy>          <Nm>GBS FrankFurt Debit Account</Nm>          </DbtrAcct>          <DbtrAgt>          <FinInstnId>          <Nm>pHUBmRP Customer 23</Nm>          <PstlAdr>          <Ctry>GR</Ctry>                    <AdrLine>GBS FrankFurt 1</AdrLine></PstlAdr>          </FinInstnId>          </DbtrAgt>          <CdtTrfTxInf>          <Amt>          <EqvtAmt>          <Amt Ccy="EUR" xsi:nil="true"/>          <CcyOfTrf>AUD</CcyOfTrf>          </EqvtAmt>          <InstdAmt Ccy="AUD">1286.07</InstdAmt>          </Amt>                    <ChrgBr>SHAR</ChrgBr>          <CdtrAgt>          <FinInstnId>          <BIC>BOFAAUSX</BIC>          <ClrSysMmbId>          <ClrSysId>          <Cd>AUBSB</Cd>          </ClrSysId>          <MmbId>013073</MmbId>          </ClrSysMmbId>          <Nm>ANZ Bkng Grp</Nm>          <PstlAdr>          <PstCd>3000</PstCd><TwnNm>Melbourne</TwnNm><Ctry>AU</Ctry>                    <AdrLine>6/530 Collins Street</AdrLine></PstlAdr>          </FinInstnId>          </CdtrAgt>          <Cdtr>          <Nm>usd to eur forwire feb10</Nm>          <PstlAdr>          <PstCd>123</PstCd>          <TwnNm>city</TwnNm>          <Ctry>AU</Ctry>          <AdrLine>add</AdrLine>          </PstlAdr>          </Cdtr>          <CdtrAcct>          <Id>          <Othr>          <Id>145580187</Id>          </Othr>          </Id>          </CdtrAcct>          </CdtTrfTxInf>          </PmtInf>          </CstmrCdtTrfInitn>          </Document>          </ns15:Pmnt>          <ns15:Extn>                    <ns15:OperationalSection>          <ns15:D_BUTTON_ID>Submit</ns15:D_BUTTON_ID>          </ns15:OperationalSection>          <ns15:UserDefinedFields>          <ns15:System code="***">          <ns15:UDF4 xsi:type="xsd:string">02/10/2011</ns15:UDF4>          <ns15:PMT_CTGORY xsi:type="xsd:string">URGENT</ns15:PMT_CTGORY>          <ns15:PMTAPRVUSR>RTYE</ns15:PMTAPRVUSR>          </ns15:System>          </ns15:UserDefinedFields>          <ns15:XMLPersistentInfo>          <ns15:D_FIRST_IN_CDT_CHAIN_BIC>BOFAAUSX</ns15:D_FIRST_IN_CDT_CHAIN_BIC>          </ns15:XMLPersistentInfo>          </ns15:Extn>          </ns15:Msg>          </ns15:FndtMsg>


      my approach to achive this

      CREATE MATERIALIZED VIEW MINF_TEST_MV
      NOCACHE
      NOLOGGING
      NOCOMPRESS
      NOPARALLEL
      BUILD IMMEDIATE
      REFRESH FAST ON COMMIT
      AS
      SELECT m.P_MID ,
      (EXTRACTVALUE (
      M.XML_MSG,
      'FndtMsg/Msg/Pmnt/Document/CstmrCdtTrfInitn/PmtInf[0]/CdtTrfTxInf[0]/RgltryRptg[0]/Dtls[0]/Inf[0]',
      'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"')) Narrative1,
      (EXTRACTVALUE (
      M.XML_MSG,
      'FndtMsg/Msg/Pmnt/Document/CstmrCdtTrfInitn/PmtInf[0]/CdtTrfTxInf[0]/RgltryRptg[0]/Dtls[0]/Inf[1]',
      'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"')) Narrative2,
      (EXTRACTVALUE (
      M.XML_MSG,
      'FndtMsg/Msg/Pmnt/Document/CstmrCdtTrfInitn/PmtInf[0]/CdtTrfTxInf[0]/RgltryRptg[0]/Dtls[0]/Inf[2]',
      'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"')) Narrative3,
      (EXTRACTVALUE (
      M.XML_MSG,
      'FndtMsg/Msg/Pmnt/Document/CstmrCdtTrfInitn/PmtInf[0]/CdtTrfTxInf[0]/RgltryRptg[0]',
      'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"')) RegulatoryReporting,
      (EXTRACTVALUE (
      M.XML_MSG,
      'FndtMsg/Msg/Pmnt/Document/CstmrCdtTrfInitn/PmtInf[0]/CdtTrfTxInf[0]/RgltryRptg[0]/Dtls[0]/Ctry',
      'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"')) RegulatoryReportingCountry
      from MINF_test m;



      This should happend real time its taking too long to parse the data please suggest me the appropriate methods
      i also have XSD if needed i will provide that as well but to validate this xml with the XSD their is a wrapper ......Thank You.::))
        • 1. Re: create meterilized view to parse the xmltype column
          Jason_(A_Non)
          What is your version?
          select * from v$version

          Lots of answers to the problem, but depending upon your performance requirements and version, different answers exist.
          • 2. Re: create meterilized view to parse the xmltype column
            928336
            Here is my version

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE     11.2.0.1.0     Production
            TNS for Linux: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production

            Right
            let me start from the beginning we have this requirement to support the front end application guys . webservices pump the data into OLTP systems from their the oracle golden gate replicate the data to one everonment let say (dev) for now, when OGG replicate data it does not do the XML parsing it replicate directly . so i am creating MV on the table which has this XMLTYPE column and populate the parsed data into the MV so that the Front end application guys run their quries . but MV does not support "Refresh Fast on commit" to this XMLTPYE datatype .
            Note: everything shold happens in Real time from start to end in continous flow .
            • 3. Re: create meterilized view to parse the xmltype column
              odie_63
              Here's a start :

              1) (Re)create your table using Binary XML storage :
              CREATE TABLE minf_test (
                p_mid      number
              , xml_msg    xmltype
              )
              XMLTYPE COLUMN xml_msg STORE AS SECUREFILE BINARY XML
              ;
              2) Create a relational view over it :
              CREATE OR REPLACE VIEW minf_test_v 
              AS
              SELECT m.p_mid
                   , x.Narrative1
                   , x.Narrative2
                   , x.Narrative3
                   , x.RegulatoryReportingCountry
              FROM minf_test 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/RgltryRptg/Dtls'
                     passing m.xml_msg
                     columns Narrative1                  varchar2(30) path 'Inf[1]'
                           , Narrative2                  varchar2(30) path 'Inf[2]'
                           , Narrative3                  varchar2(30) path 'Inf[3]'
                           , RegulatoryReportingCountry  varchar2(30) path 'Ctry'
                   ) x
              ;
              - Make sure you have all the paths and namespace mappings right. It wasn't the case in your first attempt.
              - Don't use positional predicates unless really necessary (btw, they start at position 1 not 0)

              If you encounter performance issue with the view then you can add a structured XML index, it'll give the queries near-relational speed.
              • 4. Re: create meterilized view to parse the xmltype column
                928336
                Thank you.. this is a very good start

                so when i try to create materilized view

                CREATE MATERIALIZED VIEW MINF_TEST_XQ_MV
                CACHE
                LOGGING
                NOCOMPRESS
                NOPARALLEL
                BUILD IMMEDIATE
                REFRESH FAST ON COMMIT
                AS
                SELECT m.p_mid
                , x.Narrative1
                , x.Narrative2
                , x.Narrative3
                , x.RegulatoryReportingCountry
                FROM minf_TEST_XQ 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/RgltryRptg/Dtls'
                passing m.xml_msg
                columns Narrative1 varchar2(30) path 'Inf[1]'
                , Narrative2 varchar2(30) path 'Inf[2]'
                , Narrative3 varchar2(30) path 'Inf[3]'
                , RegulatoryReportingCountry varchar2(30) path 'Ctry'
                ) x


                i am getting this error :

                cannot set the ON COMMIT refresh attribute for the materialized view

                Cause: The materialized view did not satisfy conditions for refresh at commit time.

                Action: Specify only valid options.

                Do we have any alternate methods or a solution to resolve this.
                • 5. Re: create meterilized view to parse the xmltype column
                  odie_63
                  so when i try to create materilized view
                  I did not suggest creating a materialized view, but a regular relational view.

                  How's the response time when you issue queries on it?
                  • 6. Re: create meterilized view to parse the xmltype column
                    928336
                    yes you are right but i want to create the materilized view because i want to persist the data then use it , if i create renational view it will not be able to the persist data.
                    • 7. Re: create meterilized view to parse the xmltype column
                      odie_63
                      if i create renational view it will not be able to the persist data.
                      The data is still there, in the XMLType column. The view is just showing it in another way.
                      Add a structured XMLIndex (I can show you how) and you'll be querying the view as if it were a real relational table.

                      You won't find better than a view on the base table if you need a "real time" process.
                      • 8. Re: create meterilized view to parse the xmltype column
                        928336
                        what do you mean by data is still their in Xmltype , i did not understand can you please explain it more clearly , or provide any document i can refer to. but creating this materilized view "on commit" is shows topper now....
                        • 9. Re: create meterilized view to parse the xmltype column
                          odie_63
                          You do know what a relational view is, don't you?
                          Did you try my example above?

                          I mean, why materialize the data elsewhere whereas it's readily available where it stands?
                          When you query the view, you query the real data presented in a relational way, just like you need.

                          What exactly do you not understand?
                          I can explain things further but I need to know if you're OK with basic concepts in the first place.

                          The materialized view is not a viable option since the REFRESH FAST option is not supported with XMLType, and using REFRESH COMPLETE introduces too much overhead (it would require parsing all XML instances over and over again every time a change is made in the base table).

                          Edited by: odie_63 on 12 avr. 2012 22:37
                          • 10. Re: create meterilized view to parse the xmltype column
                            928336
                            yes you are right we cannot Create MV on XMLTYPE datatype so i end up creating XVIEW

                            so my select statement looks like this

                            SELECT M.P_MID,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            '/FndtMsg/Msg/Extn/UserDefinedFields/System/PMT_CTGORY',
                            'xmlns="http://fundtech.com/SCL/CommonTypes"'))
                            paymentCategory,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            '/FndtMsg/Msg/Extn/UserDefinedFields/System/PMTINITUSR',
                            'xmlns="http://fundtech.com/SCL/CommonTypes"'))
                            initiatedUser,
                            (EXTRACTVALUE (M.XML_MSG,
                            '/FndtMsg/Msg/Extn/UserDefinedFields/System/REJT_RESN',
                            'xmlns="http://fundtech.com/SCL/CommonTypes"'))
                            rejectReason,
                            (EXTRACTVALUE (M.XML_MSG,
                            'FndtMsg/Msg/Extn/UserDefinedFields/System/PKP_AUTHTP',
                            'xmlns="http://fundtech.com/SCL/CommonTypes"'))
                            authorizedIdentityType,
                            (EXTRACTVALUE (M.XML_MSG,
                            'FndtMsg/Msg/Extn/UserDefinedFields/System/BARCODE',
                            'xmlns="http://fundtech.com/SCL/CommonTypes"'))
                            barCode,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            '/fndt:FndtMsg/fndt:Msg/fndt:Pmnt/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/MmbId',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneSortCdBankID,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            '/fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Prtry',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneSortCodeBankPrtry,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            '/fndt:FndtMsg/fndt:Msg/fndt:Pmnt/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryAccount_1,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAcct/Id/Othr/Id',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryAccount,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/PstlAdr/TwnNm',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankCity,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Cd',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankCode,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/PstlAdr/Ctry',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankCountry,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/BIC',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankID,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/MmbId',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankID_MmbId,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Cd',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankIdType,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/Nm',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankName,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/PstlAdr/CtrySubDvsn',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryBankState,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/PstlAdr/TwnNm',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            BeneficiaryCity,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/PstlAdr/Ctry',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            BeneficiaryCountry,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/Nm',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            BeneficiaryName,
                            (EXTRACTVALUE (
                            M.XML_MSG,
                            'fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Cdtr/PstlAdr/AdrLine',
                            'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" xmlns:fndt="http://fundtech.com/SCL/CommonTypes"'))
                            beneficiaryPostalCode
                            FROM MINF M

                            when i added this last addressline tag i am getting this error

                            (ORA-19025
                            EXTRACTVALUE returns value of only one node

                            Cause: Given XPath points to more than one node.

                            Action: Rewrite the query so that exactly one node is returned.)

                            please help me to reslove this ...Thank you.
                            • 11. Re: create meterilized view to parse the xmltype column
                              odie_63
                              OK, a few tips :

                              - Don't use extractvalue() function, it's deprecated.
                              What you need here is XMLTable, the query will be much more easier to write and maintain.

                              - The last error you got comes from the fact that there are multiple "AdrLine" elements per document, but extractvalue can only extract a scalar value (single element or attribute).
                              So, you have to decide how to deal with that situation. A few options would be :

                              - concatenate all AdrLine into a single string value
                              - output each AdrLine on a separate row, but that means all other extracted values will be duplicated
                              - output all AdrLine as an in-line SQL collection, on the same row
                              • 12. Re: create meterilized view to parse the xmltype column
                                928336
                                As discussed i have created XMLTable i am getting simillar error .

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

                                SELECT
                                m.p_mid,x.*
                                FROM pmts_hub_owner.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
                                beneSortCodeBankPrtry VARCHAR2(60) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/ClrSysId/Prtry' ,
                                beneSortCdBankID VARCHAR2(60) PATH 'CdtrAgt/FinInstnId/ClrSysMmbId/MmbId',
                                beneficiaryAccount_1 VARCHAR2(60) PATH 'CdtrAcct/Id/IBAN',
                                beneficiaryPostalCode VARCHAR2(60) PATH 'Cdtr/PstlAdr/AdrLine'
                                ) X ;
                                • 13. Re: create meterilized view to parse the xmltype column
                                  odie_63
                                  Did you read my last reply at all?

                                  I explained why you've got the error and how you can overcome it :
                                  So, you have to decide how to deal with that situation. A few options would be :

                                  - concatenate all AdrLine into a single string value
                                  - output each AdrLine on a separate row, but that means all other extracted values will be duplicated
                                  - output all AdrLine as an in-line SQL collection, on the same row
                                  Tell me what you want to do with repeating elements and I'll show you how to implement it.
                                  • 14. Re: create meterilized view to parse the xmltype column
                                    928336
                                    ok sorry my Bad .. i working with some random test data so ... anyways .. here is what we have to do . if we have elemennts comming like this we have to read that as element as one string ..

                                    for example .
                                    select
                                    N.value('~', 'varchar(70)') as X_ULTMT_DBTR_ADRLINE
                                    from
                                    FROM
                                    PMTS_HUB_OWNER.MINF M
                                    -- code change for the Adrline element handling start
                                    CROSS APPLY XML_MSG.nodes('/fndt:FndtMsg/fndt:Msg/fndt:Pmnt//Document/CstmrCdtTrfInitn/CdtTrfTxInf/Cdtr/PstlAdr/AdrLine') as T(N)
                                    -- code change for the Adrline element handling ends

                                    again sorry i have very limmited access to data i am doin this by assuming ..Thank you
                                    1 2 Previous Next