xml to cursor to database insert

Question

i have a snippet of an xml file (below) that has same names that i need to parse:

<pmt:SingRelEntity>
<cmn:ABA>
 <cmn:ABANum>121004861234</cmn:ABANum>
 </cmn:ABA>
 <cmn:EntityType>Account With Institution</cmn:EntityType>
 </pmt:SingRelEntity>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>Transaction Reference</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>1234600</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>EntryDesc</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>VEN</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>X12 Control Numbers</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>000032007</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>320777</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>00320101</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>Instruction Code</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>ACH</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>VEN</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>BankID</cmn:AddInfoCmpType>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>BLOBdata</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>ISA*00* *00* *ZZ*ACHtestFTPS *ZZ*bbvacompass *150417*1046*:*00501*000032007*1*T*^~GS*RA*ACHtestFTPS*bbvacompass*20150417*1046*320777*X*005010~GE*2*320777~IEA*1*000032007~</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>

<pmt:SingRelEntity>
<cmn:ABA>
 <cmn:ABANum>080014907</cmn:ABANum>
 </cmn:ABA>
 <cmn:EntityType>Account With Institution</cmn:EntityType>
 </pmt:SingRelEntity>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>Transaction Reference</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>95010401144</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>EntryDesc</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>CON</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>X12 Control Numbers</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>000032007</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>320777</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>00320102</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>Instruction Code</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>ACH</cmn:AddInfoCmpText>
 <cmn:AddInfoCmpText>CON</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>BankID</cmn:AddInfoCmpType>
 </pmt:SingAddInfo>
<pmt:SingAddInfo>
 <cmn:AddInfoCmpType>BLOBdata</cmn:AddInfoCmpType>
 <cmn:AddInfoCmpText>ISA*00* *00* *ZZ*ACHtestFTPS *ZZ*bbvacompass *150417*1046*:*00501*000032007*1*T*^~GS*RA*ACHtestFTPS*bbvacompass*20150417*1046*320777*X*005010~GE*2*320777~IEA*1*000032007~</cmn:AddInfoCmpText>
 </pmt:SingAddInfo>

This is data for two separate payments in one file. I have used a cursor to pull the data into a record as such:

CURSOR c_sing_add_info
   IS
          SELECT sing_d1.sing_info_type,
                 sing_d1.sing_info_text1,
                 sing_d1.sing_info_text2,
                 sing_d1.sing_info_text3,
                 sing_d1.sing_info_text4,
                 sing_d1.sing_info_text5,
                 sing_d1.sing_info_text6,
                 sing_d1.sing_info_text7,
                 sing_d1.sing_info_text8,
                 sing_d1.sing_info_text9,
                 sing_d1.sing_info_text10,
                 sing_d1.sing_info_text11,
                 sing_d1.sing_info_text12
            FROM XMLTABLE (
                    xmlnamespaces (
                       'http://www.abcd.com/Canonical' AS "abcd",
                       'http://abcd.com/PmtInfo' AS "pmt",
                       'http://abcd.com/CommonTypes' AS "cmn",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                    '/abcd:abcd/abcd:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo'
                    PASSING payXml
                    COLUMNS addinfo   XMLTYPE PATH '/pmt:SingAddInfo') singAddInfo_t,
                 XMLTABLE (
                    xmlnamespaces (
                       'http://www.abcd.com/Canonical' AS "abcd",
                       'http://abcd.com/PmtInfo' AS "pmt",
                       'http://abcd.com/CommonTypes' AS "cmn",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                    '/pmt:SingAddInfo'
                    PASSING singAddInfo_t.addinfo
                    COLUMNS sing_info_type VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpType',
                            sing_info_text1 VARCHAR2 (4000)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[1]',
                            sing_info_text2 VARCHAR2 (4000)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[2]',
                            sing_info_text3 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[3]',
                            sing_info_text4 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[4]', 
                            sing_info_text5 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[5]',
                            sing_info_text6 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[6]',
                            sing_info_text7 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[7]',
                            sing_info_text8 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[8]',
                            sing_info_text9 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[9]',
                            sing_info_text10 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[10]',
                            sing_info_text11 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[11]',
                            sing_info_text12 VARCHAR2 (256)
                                  PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[12]'
                                  )
                                  sing_d1;

and the record:

TYPE sing_add_info_rec IS RECORD
   (
      r_sing_info_type    VARCHAR2 (256),
      r_sing_info_text1   VARCHAR2 (4000),
      r_sing_info_text2   VARCHAR2 (4000),
      r_sing_info_text3   VARCHAR2 (256),
      r_sing_info_text4   VARCHAR2 (256),
      r_sing_info_text5   VARCHAR2 (256),
      r_sing_info_text6   VARCHAR2 (256),
      r_sing_info_text7   VARCHAR2 (256),
      r_sing_info_text8   VARCHAR2 (256),
      r_sing_info_text9   VARCHAR2 (256),
      r_sing_info_text10  VARCHAR2 (256),
      r_sing_info_text11  VARCHAR2 (256),
      r_sing_info_text12  VARCHAR2 (256)
   );
   r_sing_add_info              sing_add_info_rec;

when im running the procedure, the loop is not distinguishing where the 1st payment stops and the second payment starts. The cursor above is returning values for the 1st and 2nd payments together before moving to the first section of the 2nd payment. Since the xml is just a snippet, there are other sections before the singrelentity portion.

open c_sing_add_info;
 FETCH c_sing_add_info INTO r_sing_add_info;
 exit when c_sing_add_info%notfound;


 IF (r_sing_add_info.r_sing_info_type = 'Transaction Reference')
 THEN
 r_cust_ref_id := r_sing_add_info.r_sing_info_text1;
 dbms_output.put_line('Transaction Reference:'||r_cust_ref_id);


 ELSIF (r_sing_add_info.r_sing_info_type = 'EntryDesc')
 THEN
 r_ent_desc := r_sing_add_info.r_sing_info_text1;
 dbms_output.put_line ('Entry Description :'||r_ent_desc);


 ELSIF (r_sing_add_info.r_sing_info_type = 'X12 Control Numbers') 
 THEN
 r_x12_inter_ctrl_num := r_sing_add_info.r_sing_info_text1;
 r_x12_grp_ctrl_num := r_sing_add_info.r_sing_info_text2;
 r_x12_tran_ctrl_num := r_sing_add_info.r_sing_info_text3;
 dbms_output.put_line('X12 Inter:'||r_x12_inter_ctrl_num);
 dbms_output.put_line('X12 Group:'||r_x12_grp_ctrl_num);
 dbms_output.put_line('X12 Tran:'||r_x12_tran_ctrl_num);

 ELSIF (r_sing_add_info.r_sing_info_type = 'Instruction Code')
 THEN
 r_instr_code := r_sing_add_info.r_sing_info_text1;
 r_instr_add_info := r_sing_add_info.r_sing_info_text2;
 dbms_output.put_line('Instruction Code:'||r_instr_code);
 dbms_output.put_line('Instruction Add Info:'||r_instr_add_info);

 ELSIF (r_sing_add_info.r_sing_info_type = 'BankID')
 THEN
 r_bnk_id := r_sing_add_info.r_sing_info_text1;
 dbms_output.put_line ('Bank Id:'||r_sing_add_info.r_sing_info_text1);

 ELSIF (r_sing_add_info.r_sing_info_type = 'BLOBdata')
 THEN
 r_blob_data := r_sing_add_info.r_sing_info_text1;
 r_blob_data2 := r_sing_add_info.r_sing_info_text2;
 dbms_output.put_line ('Blob Data 1 :'||r_sing_add_info.r_sing_info_text1);

 end if;

Please suggest how i can parse this correctly to distinguish the two payments. Thank you.

Please post a [minimal example](http://stackoverflow.com/help/mcve). — OldProgrammer, Jun 08 '15 at 12:55
I would suggest using an XPATH query to parse the XML data. There are several good resources listed in this post: http://stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value — MartianCodeHound, Jun 08 '15 at 14:56
Thank you MartianCodeHound. Is there something specific to Oracle that you can point me to? The problem I have is not with parsing, but how it is being parsed. As you can see, there are duplicate names "singaddinfo" for both payments, and I need to figure out a way to distinguish the two payments. Would you recommend not using a record, and instead declaring a table of that record type? I need some way of letting the procedure know that the 1st payment stops here and the 2nd payment starts here! — QuickDrawMcgraw, Jun 08 '15 at 18:10
If you want an answer you need to reduce this to an easily understandable problem. That means a much smaller XML and a much smaller code sample. Help us to help you. — APC, Jun 10 '15 at 07:25
Hi APC the xml i showed is for two separate payments that need inserted into the database. I am using a collection to bulk fetch everything from the cursor, but my problem here is how to make the code distinguish where the "singaddinfo" for payment 1 ends and then go to the outer loop, execute everything else, then re-enter the cursor for loop to execute the "singaddinfo" for payment 2. — QuickDrawMcgraw, Jun 10 '15 at 15:17

xml to cursor to database insert

0 Answers0