Forum Stats

  • 3,876,077 Users
  • 2,267,057 Discussions
  • 7,912,420 Comments

Discussions

Categories

create meterilized view to parse the xmltype column

928336
928336 Member Posts: 10
edited May 4, 2012 9:32AM in XQuery
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.::))
· Share on TwitterShare on Facebook
«1

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,160 Gold Trophy
    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.
    · Share on TwitterShare on Facebook
  • 928336
    928336 Member Posts: 10
    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 .
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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.
    · Share on TwitterShare on Facebook
  • 928336
    928336 Member Posts: 10
    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.
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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?
    · Share on TwitterShare on Facebook
  • 928336
    928336 Member Posts: 10
    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.
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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.
    · Share on TwitterShare on Facebook
  • 928336
    928336 Member Posts: 10
    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....
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Apr 12, 2012 5:02PM
    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
    · Share on TwitterShare on Facebook
  • 928336
    928336 Member Posts: 10
    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.
    · Share on TwitterShare on Facebook
This discussion has been closed.