ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

XMLTABLE help needed [URGENT]

Collapse
X
Collapse
 
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
  • #1

    XMLTABLE help needed [URGENT]

    Hi guys,

    I need an urgent help with XMLTABLE.

    I am trying to decompose PAIN.002 (ISO20022) using XMLTABLE. These messages have 3 levels (MessageId, PaymentInfoId and EndtoEndIds).

    The message may have one MessageId that can have multiple PaymentInfoIds and one PaymentInfoId can have multiple EndToEndIds. I have got below query to solve this but it's failing with SQ16003N for an obvious reason where I get multiple PaymentInfoIds however it works well when I get One MessageId, One PaymentInfoId and Multiple EndToEndIds

    Can someone please help me with a way to handle this:

    select x.BankMsgId, x.MsgId, x.
    from
    xmltable
    (
    '$d/*ocument/*:CstmrPmtStsRpt/ +
    *:OrgnlPmtInfAndSts/*:TxInfAndSts/*:OrgnlEndToEndId'
    passing
    xmlparse(document GET_XML_FILE(InpPath)) as "d"
    columns
    BankMsgId
    varchar(45) default ' '
    PATH '../../../*:GrpHdr/*:MsgId',
    MsgId
    varchar(45) default ' '
    FileCount
    dec(15,0) default 0
    PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlNbOfTxs',
    FileSum
    dec(15,2) default 0
    PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlCtrlSum',
    PaymentInfoId
    varchar(45) default ' '
    PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlPmtInfId',
    BatchCount
    dec(15,0) default 0
    PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlNbOfTxs',
    BatchSum
    dec(15,2) default 0
    PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlCtrlSum',
    EndToEndId
    varchar(45) default ' '
    PATH
    '.',
    ExecDate
    varchar(45) default ' '
    PATH
    '../*:OrgnlTxRef/*:ReqdExctnDt',
    TrxSum
    dec(15,2) default 0
    PATH
    '../*:OrgnlTxRef/*:Amt/*:InstdAmt',
    Sts
    varchar(10) default ' '
    PATH
    '../*:TxSts',
    StsCod
    varchar(10) default ' '
    PATH
    '../*:StsRsnInf/*:Rsn/*:Cd'
    ) as x

    Tags: None
  • #2
    It is difficult to say anything, whithout seeing the XML data itself.
    Try to start simple, i.e. return one information after the other.
    Did you get returned anything?

    Comment

    • #3
      Can you please provide a sample of an xml that it will not decode?

      Can you also please re-post your code inside
      HTML Code:
      [CODE][/CODE]
      tags so that it keeps its formatting?

      Comment

      • #4
        The XML which is failing with 16003N. If we have only one <OrgnlPmtInfAndSts> block, it goes through 100%. I know the reason for failure but kind of not able to query around to bypass it: (Please use notepad++ or any other XML editor to view the pretty print of the XML given below):

        This XML file does not appear to have any style information associated with it. The document tree is shown below.

        HTML Code:
        <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">
<CstmrPmtStsRpt>
<GrpHdr>
<MsgId>MSGID123</MsgId>
<CreDtTm>2020-10-01T14:04:45.565</CreDtTm>
<InitgPty>
<Id>
<OrgId>
<BICOrBEI>BANK1</BICOrBEI>
</OrgId>
</Id>
</InitgPty>
</GrpHdr>
<OrgnlGrpInfAndSts>
<OrgnlMsgId>ORIGINALMSGID1</OrgnlMsgId>
<OrgnlMsgNmId>PAIN.001.001.03</OrgnlMsgNmId>
<OrgnlCreDtTm>2020-10-01T14:03:54.000+02:00</OrgnlCreDtTm>
<OrgnlNbOfTxs>3</OrgnlNbOfTxs>
<OrgnlCtrlSum>916.11</OrgnlCtrlSum>
</OrgnlGrpInfAndSts>
<OrgnlPmtInfAndSts>
<OrgnlPmtInfId>OrgnlPmtInfId1</OrgnlPmtInfId>
<OrgnlNbOfTxs>1</OrgnlNbOfTxs>
<OrgnlCtrlSum>304.00</OrgnlCtrlSum>
<TxInfAndSts>
<StsId>bbbbb 033505CS</StsId>
<OrgnlEndToEndId>bbbbb 033505CS</OrgnlEndToEndId>
<TxSts>ACSC</TxSts>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="ZAR">304.00</InstdAmt>
</Amt>
<ReqdExctnDt>2020-11-26</ReqdExctnDt>
<Dbtr>
<Nm>COMPANY1</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<Othr>
<Id>1233456789</Id>
</Othr>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>BANK2</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>BANK2</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>bbbbbbb</Nm>
</Cdtr>
<CdtrAcct>
<Id>
<Othr>
<Id>123456</Id>
</Othr>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
</OrgnlPmtInfAndSts>
<OrgnlPmtInfAndSts>
<OrgnlPmtInfId>OrgnlPmtInfId2</OrgnlPmtInfId>
<OrgnlNbOfTxs>1</OrgnlNbOfTxs>
<OrgnlCtrlSum>200.00</OrgnlCtrlSum>
<TxInfAndSts>
<StsId>aaaa 033504CS</StsId>
<OrgnlEndToEndId>aaaa 033504CS</OrgnlEndToEndId>
<TxSts>ACSC</TxSts>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="INR">200.00</InstdAmt>
</Amt>
<ReqdExctnDt>2020-11-26</ReqdExctnDt>
<Dbtr>
<Nm>DEBT1</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<Othr>
<Id>123999</Id>
</Othr>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>BANK3</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>BANK3</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>testa</Nm>
</Cdtr>
<CdtrAcct>
<Id>
<Othr>
<Id>123456</Id>
</Othr>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
</OrgnlPmtInfAndSts>
<OrgnlPmtInfAndSts>
<OrgnlPmtInfId>OrgnlPmtInfId3</OrgnlPmtInfId>
<OrgnlNbOfTxs>1</OrgnlNbOfTxs>
<OrgnlCtrlSum>412.11</OrgnlCtrlSum>
<TxInfAndSts>
<StsId>ccccc 033506CS</StsId>
<OrgnlEndToEndId>ccccc 033506CS</OrgnlEndToEndId>
<TxSts>ACSC</TxSts>
<OrgnlTxRef>
<Amt>
<InstdAmt Ccy="ZAR">412.11</InstdAmt>
</Amt>
<ReqdExctnDt>2020-11-26</ReqdExctnDt>
<Dbtr>
<Nm>CLIENT3</Nm>
</Dbtr>
<DbtrAcct>
<Id>
<Othr>
<Id>123456789999</Id>
</Othr>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>BANK4</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>BANK4</BIC>
</FinInstnId>
</CdtrAgt>
<Cdtr>
<Nm>ccccc</Nm>
</Cdtr>
<CdtrAcct>
<Id>
<Othr>
<Id>99999</Id>
</Othr>
</Id>
</CdtrAcct>
</OrgnlTxRef>
</TxInfAndSts>
</OrgnlPmtInfAndSts>
</CstmrPmtStsRpt>
</Document>
        Here is how I am processing it:

        Step#1
        Temp table to receive the data
        HTML Code:
        exec sql
declare global temporary table faccrt02tf
(BankMsgid varchar(45) not null with default,
Msgid varchar(45) not null with default,
FileCount dec(15,0) not null with default,
FileSum dec(15,2) not null with default,
PaymentInfoId varchar(45) not null with default,
BatchCount dec(15,0) not null with default,
BatchSum dec(15,2) not null with default,
EndToEndId varchar(45) not null with default,
ExecDate varchar(15) not null with default,
TrxSum dec(15,2) not null with default,
Sts varchar(10) not null with default,
StsCod varchar(10) not null with default)
with replace not logged;
        Step#2
        XML decomposition query
        HTML Code:
        exec sql                                                    
insert into faccrt02tf                                        
(                                                            
  select x.*                                                
  from                                                      
       xmltable                                              
       (                                                    
         '$d/*:Document/*:CstmrPmtStsRpt/  +                
         *:OrgnlPmtInfAndSts/*:TxInfAndSts/*:OrgnlEndToEndId'
         passing                                            
         xmlparse(document GET_XML_FILE(:pInpPath)) as "d"  
         columns                                            
         BankMsgId                                          
            varchar(45) default ' '                          
            PATH '../../../*:GrpHdr/*:MsgId',                
         MsgId                                              
            varchar(45) default ' '                          
            PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlMsgId',
        FileCount                                                
           dec(15,0) default 0                                  
           PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlNbOfTxs',  
        FileSum                                                  
           dec(15,2) default 0                                  
           PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlCtrlSum',  
        PaymentInfoId                                            
           varchar(45) default ' '                              
           PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlPmtInfId',  
        BatchCount                                                
           dec(15,0) default 0                                  
           PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlNbOfTxs',  
        BatchSum                                                
           dec(15,2) default 0                                    
           PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlCtrlSum',  
        EndToEndId                                              
           varchar(45) default ' '                              
           PATH                                                  
           '.',                                                  
           ExecDate                                              
              varchar(45) default ' '                            
              PATH                                              
              '../*:OrgnlTxRef/*:ReqdExctnDt',                  
           TrxSum                                                
              dec(15,2) default 0                                
              PATH                                              
              '../*:OrgnlTxRef/*:Amt/*:InstdAmt',                
           Sts                                                  
              varchar(10) default ' '                            
              PATH                                              
              '../*:TxSts',                                      
           StsCod                                                
              varchar(10) default ' '                            
              PATH                                              
              '../*:StsRsnInf/*:Rsn/*:Cd'                        
         ) as x                                                  
  );
        Additional Info:
        The highlighted tags are the 3 levels in the message. 1 MessageID can have multiple paymentInfoIds and 1 Payment InfoID can have multiple endtoendIDs.

        Expected Output (PS I have only included the main columns from the temp table above to explain how it should look like)
        Original MsgId PaymentInfoId E2EID
        ORIGINALMSGID1 OrgnlPmtInfId1 bbbbb 033505CS
        ORIGINALMSGID1 OrgnlPmtInfId2 aaaa 033504CS
        ORIGINALMSGID1 OrgnlPmtInfId3 ccccc 033506CS

        Comment

        • #5
          The error message (SQ16003) you get when this happens includes this text:
          "An expression of data type ( item(), item()+ ) cannot be used when the data type item() is expected for the context"
          Which means one of your output columns is returning multiple elements instead of one.

          So I commented out every individual column definition, and then uncommented them one by one to find which column(s) were the problem. It's these three:
          Code:
           
         PaymentInfoId                                                        varchar(45) default ' '                                          PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlPmtInfId',           BatchCount                                                            dec(15,0) default 0                                              PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlNbOfTxs',           BatchSum                                                            dec(15,2) default 0                                                PATH '../../../*:OrgnlPmtInfAndSts/*:OrgnlCtrlSum',
          Your repeating group in the XML is OrgnlPmtInfAndSts. So you are correctly drilling down to that level in the initial path at the very top, then extracting all columns relative to that.
          However.
          Those three columns are inside the OrgnlPmtInfAndSts repeating group. But when you extract the three columns above, you are navigating up three levels (../../../) which takes you out of the group, and then going back in (*:OrgnlPmtInfAndSts/...)
          You left the group. Which means when you try and go back in, you match every group rather than just the current one. So those three columns are each returning a list of xml elements from all groups, rather than the value of the one element inside the group.

          Those three paths should be this instead:

          Code:
           
         PaymentInfoId                                                        varchar(45) default ' '                                          PATH '../../*:OrgnlPmtInfId',           BatchCount                                                            dec(15,0) default 0                                              PATH '../../*:OrgnlNbOfTxs',           BatchSum                                                            dec(15,2) default 0                                                PATH '../../*:OrgnlCtrlSum',
          That worked for me:

          Code:
          with xtab(xcol) as (values(xmlparse(document 
'<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">'
||'<CstmrPmtStsRpt>'
||'<GrpHdr>'
||'<MsgId>MSGID123</MsgId>'
||'<CreDtTm>2020-10-01T14:04:45.565</CreDtTm>'
||'<InitgPty>'
||'<Id>'
||'<OrgId>'
||'<BICOrBEI>BANK1</BICOrBEI>'
||'</OrgId>'
||'</Id>'
||'</InitgPty>'
||'</GrpHdr>'
||'<OrgnlGrpInfAndSts>'
||'<OrgnlMsgId>ORIGINALMSGID1</OrgnlMsgId>'
||'<OrgnlMsgNmId>PAIN.001.001.03</OrgnlMsgNmId>'
||'<OrgnlCreDtTm>2020-10-01T14:03:54.000+02:00</OrgnlCreDtTm>'
||'<OrgnlNbOfTxs>3</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>916.11</OrgnlCtrlSum>'
||'</OrgnlGrpInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId1</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>304.00</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>bbbbb 033505CS</StsId>'
||'<OrgnlEndToEndId>bbbbb 033505CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="ZAR">304.00</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>COMPANY1</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>1233456789</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK2</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK2</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>bbbbbbb</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId2</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>200.00</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>aaaa 033504CS</StsId>'
||'<OrgnlEndToEndId>aaaa 033504CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="INR">200.00</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>DEBT1</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123999</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK3</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK3</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>testa</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId3</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>412.11</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>ccccc 033506CS</StsId>'
||'<OrgnlEndToEndId>ccccc 033506CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="ZAR">412.11</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>CLIENT3</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456789999</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK4</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK4</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>ccccc</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>99999</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'</CstmrPmtStsRpt>'
||'</Document>'
)))
  select x.*                                                
  from                                                      
       xmltable                                              
       (                                                    
         '$d/*:Document/*:CstmrPmtStsRpt/*:OrgnlPmtInfAndSts/*:TxInfAndSts/*:OrgnlEndToEndId'
         passing                                            
         (select xcol from xtab) as "d"  
         columns                                            
         BankMsgId                                          
            varchar(45) default ' '                          
            PATH '../../../*:GrpHdr/*:MsgId',
         MsgId                                              
            varchar(45) default ' '                          
            PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlMsgId',
        FileCount                                                
           dec(15,0) default 0                                  
           PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlNbOfTxs',  
        FileSum                                                  
           dec(15,2) default 0                                  
           PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlCtrlSum',  
        PaymentInfoId                                            
           varchar(45) default ' '                              
           PATH '../../*:OrgnlPmtInfId',  
        BatchCount                                                
           dec(15,0) default 0                                  
           PATH '../../*:OrgnlNbOfTxs',  
        BatchSum                                                
           dec(15,2) default 0                                    
           PATH '../../*:OrgnlCtrlSum',  
        EndToEndId                                              
           varchar(45) default ' '                              
           PATH                                                  
           '.',                                                  
           ExecDate                                              
              varchar(45) default ' '                            
              PATH                                              
              '../*:OrgnlTxRef/*:ReqdExctnDt',                  
           TrxSum                                                
              dec(15,2) default 0                                
              PATH                                              
              '../*:OrgnlTxRef/*:Amt/*:InstdAmt',                
           Sts                                                  
              varchar(10) default ' '                            
              PATH                                              
              '../*:TxSts',                                      
           StsCod                                                
              varchar(10) default ' '                            
              PATH                                              
              '../*:StsRsnInf/*:Rsn/*:Cd'                  
         ) as x
          Code:
           [TABLE]
[TR]
[TD]MSGID123[/TD]
 			[TD]ORIGINALMSGID1[/TD]
 			[TD]3[/TD]
 			[TD]916.11[/TD]
 			[TD]OrgnlPmtInfId1[/TD]
 			[TD]1[/TD]
 			[TD]304.00[/TD]
 			[TD]bbbbb 033505CS[/TD]
 			[TD]2020-11-26[/TD]
 			[TD]304.00[/TD]
 			[TD]ACSC[/TD]
 			[TD] [/TD]
 		[/TR]
[TR]
[TD]MSGID123[/TD]
 			[TD]ORIGINALMSGID1[/TD]
 			[TD]3[/TD]
 			[TD]916.11[/TD]
 			[TD]OrgnlPmtInfId2[/TD]
 			[TD]1[/TD]
 			[TD]200.00[/TD]
 			[TD]aaaa 033504CS[/TD]
 			[TD]2020-11-26[/TD]
 			[TD]200.00[/TD]
 			[TD]ACSC[/TD]
 			[TD] [/TD]
 		[/TR]
[TR]
[TD]MSGID123[/TD]
 			[TD]ORIGINALMSGID1[/TD]
 			[TD]3[/TD]
 			[TD]916.11[/TD]
 			[TD]OrgnlPmtInfId3[/TD]
 			[TD]1[/TD]
 			[TD]412.11[/TD]
 			[TD]ccccc 033506CS[/TD]
 			[TD]2020-11-26[/TD]
 			[TD]412.11[/TD]
 			[TD]ACSC[/TD]
 			[TD] [/TD]
 		[/TR]
[/TABLE]

          Comment

          • #6
            Excellent Vectorspce. Much appreciated for your time. Such an oversight from my side. Thank you so much.

            Comment

            • #7
              Hi guys,

              Back again for a help.

              I did a load test on my XMLTABLE script above with a 22MB document where-in I had multiple and it performed miserably in it's original form. I understand because it has to go up and down the hierarchy multiple times, it suffers bit of a performance lag. Considering that, I removed the following columns from the decomposition which improved the performance a bit but still the query has to look up the for paymentinfoId everytime there is a new block and it makes it slower. But my ultimate observation is that XMLTABLE in general gets slower with an increase in the document size. I will appreciate the views to optimize the above decomposition query:

              Columns removed from the original query:
              HTML Code:
                         BankMsgId                                                       varchar(45) default ' '                                       PATH '../../../*:GrpHdr/*:MsgId',          MsgId                                                           varchar(45) default ' '                                       PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlMsgId',         FileCount                                                            dec(15,0) default 0                                              PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlNbOfTxs',           FileSum                                                              dec(15,2) default 0                                              PATH '../../../*:OrgnlGrpInfAndSts/*:OrgnlCtrlSum',
              The repeating block in the original query (with different values):
              HTML Code:
                <OrgnlPmtInfId>OrgnlPmtInfId1</OrgnlPmtInfId> <OrgnlNbOfTxs>1</OrgnlNbOfTxs> <OrgnlCtrlSum>304.00</OrgnlCtrlSum> <TxInfAndSts> <StsId>bbbbb 033505CS</StsId> <OrgnlEndToEndId>bbbbb 033505CS</OrgnlEndToEndId> <TxSts>ACSC</TxSts> <OrgnlTxRef> <Amt> <InstdAmt Ccy="ZAR">304.00</InstdAmt> </Amt> <ReqdExctnDt>2020-11-26</ReqdExctnDt> <Dbtr> <Nm>COMPANY1</Nm> </Dbtr> <DbtrAcct> <Id> <Othr> <Id>1233456789</Id> </Othr> </Id> </DbtrAcct> <DbtrAgt> <FinInstnId> <BIC>BANK2</BIC> </FinInstnId> </DbtrAgt> <CdtrAgt> <FinInstnId> <BIC>BANK2</BIC> </FinInstnId> </CdtrAgt> <Cdtr> <Nm>bbbbbbb</Nm> </Cdtr> <CdtrAcct> <Id> <Othr> <Id>123456</Id> </Othr> </Id> </CdtrAcct> </OrgnlTxRef> </TxInfAndSts> </OrgnlPmtInfAndSts> <OrgnlPmtInfAndSts>
              Thanks, D

              Comment

              • #8
                I have two ideas, but I don't know how effective they will be.

                You define your document root path as '$d/*ocument/*:CstmrPmtStsRpt/*:OrgnlPmtInfAndSts/*:TxInfAndSts/*:OrgnlEndToEndId'
                OrgnlPmtInfAndSts is your repeating group. But rather than specifying that as your root path, you go down two more levels, /*:TxInfAndSts/*:OrgnlEndToEndId'
                Because every field is defined relative to that, you have to navigate up several levels to reach most of the fields.

                Try using $d/*ocument/*:CstmrPmtStsRpt/*:OrgnlPmtInfAndSts/ as your root path instead, and redefine all the field paths to be relative to that instead. It reduces the required number of steps to navigate to most of the fields.

                e.g.
                Code:
                       xmltable                                              
       (                                                    
         '$d/*:Document/*:CstmrPmtStsRpt/*:OrgnlPmtInfAndSts'
         passing                                            
         (select xcol from xtab) as "d"  
         columns              

         BankMsgId                                          
            varchar(45) default ' '                          
            PATH '../*:GrpHdr/*:MsgId',  -- up only one level, before it was 3

        BatchSum                                                
           dec(15,2) default 0                                    
           PATH '*:OrgnlCtrlSum',  --same level, before it was up 2 levels

        EndToEndId                                              
           varchar(45) default ' '                              
           PATH                                                  
           '*:TxInfAndSts/*:OrgnlEndToEndId',   --down 1 level, before it was up 3 and then down 1

                Secondly, this line:
                Code:
                         xmlparse(document GET_XML_FILE(:pInpPath)) as "d"
                You may have misunderstood what GET_XML_FILE() does. It retrieves the text content of an IFS file, parses it as XML, and returns the parsed XML object. So, you don't need to call xmlparse() on it (which would parse it again probably).
                In contrast, GET_CLOB_FROM_FILE() retrieves the text content of an IFS file and returns it as-is. So if you used GET_CLOB_FROM_FILE() then you would need to use xmlparse() on the result.
                That's all I can think of.

                Comment

                • #9
                  You are obviously committed to doing this with SQL but for the sale of others following along this path ...

                  Just out of interest I decided to see how long it would take me to get this running with XML-INTO. The answer was < 30 minutes. Admittedly retrieving the subsequent "pieces" is verbose but the result is far more comprehensible to those who have to maintain the code later than what I'm seeing here.

                  SQL is very often the right tool for the job - but in cases like this it does tend to require a write-only programming style.

                  Just sayin ...

                  Comment

                  • #10
                    Hi, Vectorspace and Jonboy,

                    Thanks again for your replies and guidance. Sorry, I got pulled in some other priorities and had to leave this task in-between. Got back to it only yesterday. I tried both the options XML-INTO and XMLTABLE. My final finding is, XMLTABLE is much simpler to implement for complex XMLs like ISO PAIN messages while XML-INTO is much more efficient.

                    I also found out that XML-INTO can still have a size issue even when you use %handler (unless I misunderstood it). As in my example, the PaymentInfoId is a repetitive block and also the EndToEndId underneath. A typical message can have say 10 PaymentInfoIds but can have thousands of EndToEndIds (transactions) within each of these PaymentInfoIds. Of course, depending on my DS size, RPG allowed me to have max 170,000 transactions (EndtoEndIds) under each of the PaymentInfoIds. I hope we don't ever hit the limit but nothing's guaranteed in the payment/collection industry.

                    Vectorspace - That was the first thing I tried to keep the row path to the PaymentInfoId level instead of to EndToEndId but the way these messages are laid out, SQL won't allow to do that and will throw SQ16003N error. Also, I tried removing XMLPARSE but looks like XMLVALIDATE and XMLTABLE both need you to have them. I couldn't get that right. There are certain suggestions on IBM developers site that if we have to go up the hierarchy, we must try to extract these values in the "Select" clause itself but I couldn't get that working either. For now I am resorting to XML-INTO but really curious to continue exploring XMLTABLE in future to validate more optimization ways. Just for the interest sake.

                    Thanks again.

                    Comment

                    • #11
                      I can understand that you found size issues with XML-INTO if you have no idea how many repeats you are dealing with but not sure why you thought it was hard to implement. I've just built a working PAIN processing example in about 20 minutes using your sample XML. %Handler should be able to deal with it - but if you really are going to get 170K transactions per unit ... maybe a probelm.

                      If performance becomes an issue you might want to take a look at noXDb (https://github.com/sitemule/noxDB) which is supposed to have great performance.

                      My problem with SQL in cases like this is that it so easily becomes write-only code - but I'm not an SQL expert so maybe that is just me.

                      Comment

                      • #12
                        Originally posted by dhirajt30@gmail View Post
                        Vectorspace - That was the first thing I tried to keep the row path to the PaymentInfoId level instead of to EndToEndId but the way these messages are laid out, SQL won't allow to do that and will throw SQ16003N error.
                        I assume that's because you tried it before I solved your existing SQ16003N error. I just tried it with the full XML and it worked. Run it and see for yourself:
                        Code:
                        with xtab(xcol) as (values(xmlparse(document
'<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.002.001.03">'
||'<CstmrPmtStsRpt>'
||'<GrpHdr>'
||'<MsgId>MSGID123</MsgId>'
||'<CreDtTm>2020-10-01T14:04:45.565</CreDtTm>'
||'<InitgPty>'
||'<Id>'
||'<OrgId>'
||'<BICOrBEI>BANK1</BICOrBEI>'
||'</OrgId>'
||'</Id>'
||'</InitgPty>'
||'</GrpHdr>'
||'<OrgnlGrpInfAndSts>'
||'<OrgnlMsgId>ORIGINALMSGID1</OrgnlMsgId>'
||'<OrgnlMsgNmId>PAIN.001.001.03</OrgnlMsgNmId>'
||'<OrgnlCreDtTm>2020-10-01T14:03:54.000+02:00</OrgnlCreDtTm>'
||'<OrgnlNbOfTxs>3</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>916.11</OrgnlCtrlSum>'
||'</OrgnlGrpInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId1</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>304.00</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>bbbbb 033505CS</StsId>'
||'<OrgnlEndToEndId>bbbbb 033505CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="ZAR">304.00</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>COMPANY1</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>1233456789</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK2</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK2</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>bbbbbbb</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId2</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>200.00</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>aaaa 033504CS</StsId>'
||'<OrgnlEndToEndId>aaaa 033504CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="INR">200.00</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>DEBT1</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123999</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK3</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK3</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>testa</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfAndSts>'
||'<OrgnlPmtInfId>OrgnlPmtInfId3</OrgnlPmtInfId>'
||'<OrgnlNbOfTxs>1</OrgnlNbOfTxs>'
||'<OrgnlCtrlSum>412.11</OrgnlCtrlSum>'
||'<TxInfAndSts>'
||'<StsId>ccccc 033506CS</StsId>'
||'<OrgnlEndToEndId>ccccc 033506CS</OrgnlEndToEndId>'
||'<TxSts>ACSC</TxSts>'
||'<OrgnlTxRef>'
||'<Amt>'
||'<InstdAmt Ccy="ZAR">412.11</InstdAmt>'
||'</Amt>'
||'<ReqdExctnDt>2020-11-26</ReqdExctnDt>'
||'<Dbtr>'
||'<Nm>CLIENT3</Nm>'
||'</Dbtr>'
||'<DbtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>123456789999</Id>'
||'</Othr>'
||'</Id>'
||'</DbtrAcct>'
||'<DbtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK4</BIC>'
||'</FinInstnId>'
||'</DbtrAgt>'
||'<CdtrAgt>'
||'<FinInstnId>'
||'<BIC>BANK4</BIC>'
||'</FinInstnId>'
||'</CdtrAgt>'
||'<Cdtr>'
||'<Nm>ccccc</Nm>'
||'</Cdtr>'
||'<CdtrAcct>'
||'<Id>'
||'<Othr>'
||'<Id>99999</Id>'
||'</Othr>'
||'</Id>'
||'</CdtrAcct>'
||'</OrgnlTxRef>'
||'</TxInfAndSts>'
||'</OrgnlPmtInfAndSts>'
||'</CstmrPmtStsRpt>'
||'</Document>'
)))
  select x.*
  from
       xmltable
       (
         '$d/*:Document/*:CstmrPmtStsRpt/*:OrgnlPmtInfAndSts'
         passing
         (select xcol from xtab) as "d"
         columns
         BankMsgId
            varchar(45) default ' '
            PATH '../*:GrpHdr/*:MsgId',
         MsgId
            varchar(45) default ' '
            PATH '../*:OrgnlGrpInfAndSts/*:OrgnlMsgId',
        FileCount
           dec(15,0) default 0
           PATH '../*:OrgnlGrpInfAndSts/*:OrgnlNbOfTxs',
        FileSum
           dec(15,2) default 0
           PATH '../*:OrgnlGrpInfAndSts/*:OrgnlCtrlSum',
        PaymentInfoId
           varchar(45) default ' '
           PATH '*:OrgnlPmtInfId',
        BatchCount
           dec(15,0) default 0
           PATH '*:OrgnlNbOfTxs',
        BatchSum
           dec(15,2) default 0
           PATH '*:OrgnlCtrlSum',
        EndToEndId
           varchar(45) default ' '
           PATH
           '*:TxInfAndSts/*:OrgnlEndToEndId',
        ExecDate
           varchar(45) default ' '
           PATH
           '*:TxInfAndSts/*:OrgnlTxRef/*:ReqdExctnDt',
        TrxSum
           dec(15,2) default 0
           PATH
           '*:TxInfAndSts/*:OrgnlTxRef/*:Amt/*:InstdAmt',
        Sts
           varchar(10) default ' '
           PATH
           '*:TxInfAndSts/*:TxSts',
        StsCod
           varchar(10) default ' '
           PATH
           '*:TxInfAndSts/*:StsRsnInf/*:Rsn/*:Cd'
         ) as x

                        Comment

                        • #13
                          dhirajt30@gmail I just posted a new example of how you can improve the root and relative paths, but it's marked as approval pending. I think because of its size. Can you confirm if you can see it?

                          Comment

                          • #14
                            Hi Vectorspace, Yes I can see your message. I am gonna give it a try and will advise the soonest. Thanks again for the help.

                            Comment

                            • #15
                              JonBoy Thanks for the suggestion again. I will explore that option too. Can you perhaps pls share your code with me if possible. I want to compare mine with yours.

                              Comment

                              Previous 1 2 template Next
                              Working...
                              X