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.::))