Forum Stats

  • 3,853,894 Users
  • 2,264,290 Discussions
  • 7,905,498 Comments

Discussions

Parsing multiple versions using XML table

e6de7f2c-e1dc-4e3c-838d-f07eabaccfa2
edited May 12, 2015 8:50AM in XQuery

Hi,

Is there a way we can parse multiple versions using XML table at one go, for example i have the following query some paths are available in Version 2 and some are in version 3.

can i use the query to handle both the versions at one go.so if the data is related to version 2 it would give me the data if its a valid path from version 2 and null for if its a path availble in version3 only and vice versa.

SELECT

xt.*

         FROM PMTS_HUB_OWNER.MINF x,

XMLTABLE (

                        XMLNamespaces ('http://fundtech.com/SCL/CommonTypes' AS "fndt",DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pacs.003.001.02 and I want to accomodate version 3 as well here pacs.003.001.03'),

                        '/fndt:FndtMsg/fndt:Msg/fndt:Pmnt/Document/FIToFICstmrDrctDbt'

            PASSING x.XML_ORIG_MSG

            COLUMNS

ORGNLINSTRID     VARCHAR2(35)  PATH        'DrctDbtTxInf/PmtId/InstrId',

ORGNLENDTOENDID  VARCHAR2(35)  PATH        'DrctDbtTxInf/PmtId/EndToEndId',

Cdtr_AdrLine_1   varchar2(70) Path 'DrctDbtTxInf/Cdtr/PstlAdr/AdrLine[1]' ,

Cdtr_AdrLine_2   varchar2(70) Path 'DrctDbtTxInf/Cdtr/PstlAdr/AdrLine[2]' ,

CdtrAgt_AdrLine_1   varchar2(70) Path 'DrctDbtTxInf/CdtrAgt/FinInstnId/PstlAdr/AdrLine[1]' ,

CdtrAgt_AdrLine_2   varchar2(70) Path 'DrctDbtTxInf/CdtrAgt/FinInstnId/PstlAdr/AdrLine[2]'

)xt

WHERE P_ORIG_MSG_TYPE='Pacs_003'

Answers

This discussion has been closed.