13 Replies Latest reply: Feb 6, 2014 2:37 AM by user597769 RSS

Load a nested XML into a nested PL/SQL table (associative array)

user597769 Newbie
Currently Being Moderated

Hi,

I am fairly new to XML in PL/SQL and faced with a complex case as follows:

 

<MSG>

    <HEADER>

  <FILE_NAME>SW20130102-01</FILE_NAME>

  <FILE_TYPE>SWITCH</FILE_TYPE>

    </HEADER>

  <BODY>

  <POLICY_LIST>

           <POLICY>

               <INSTRUCTION_SOURCE>CLIENT</INSTRUCTION_SOURCE>

               <POLICY_NO>401-002298</POLICY_NO>

    <SWITCH_LIST>

    <SWITCH>

  <COMMON_ID>101</COMMON_ID>

  <SWITCH_TYPE>P</SWITCH_TYPE>

  <SWITCH_CHARGE>100.00</SWITCH_CHARGE>

  <EFFECTIVE_DATE></EFFECTIVE_DATE>

  <FUND_LIST>

  <FUND>

  <FUND_CODE>1069</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>100.00</PERC>

  <AMOUNT></AMOUNT>

  <BUY_SELL>SELL</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1003001</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>25.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1070</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>75.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

   </FUND_LIST>

    </SWITCH>

    <SWITCH>

  <COMMON_ID>102</COMMON_ID>

  <SWITCH_TYPE>P</SWITCH_TYPE>

  <SWITCH_CHARGE>100.00</SWITCH_CHARGE>

  <EFFECTIVE_DATE></EFFECTIVE_DATE>

  <FUND_LIST>

  <FUND>

  <FUND_CODE>1055</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>100.00</PERC>

  <AMOUNT></AMOUNT>

  <BUY_SELL>SELL</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1003012</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>25.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1068</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>75.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

   </FUND_LIST>

    </SWITCH>  

    </SWITCH_LIST>

             </POLICY>

             <POLICY>

               <INSTRUCTION_SOURCE>CLIENT</INSTRUCTION_SOURCE>

               <POLICY_NO>401-002300</POLICY_NO>

    <SWITCH_LIST>

    <SWITCH>

  <COMMON_ID>101</COMMON_ID>

  <SWITCH_TYPE>P</SWITCH_TYPE>

  <SWITCH_CHARGE>100.00</SWITCH_CHARGE>

  <EFFECTIVE_DATE></EFFECTIVE_DATE>

  <FUND_LIST>

  <FUND>

  <FUND_CODE>1090</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>100.00</PERC>

  <AMOUNT></AMOUNT>

  <BUY_SELL>SELL</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1091</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>25.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1092</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>75.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

   </FUND_LIST>

    </SWITCH>

    <SWITCH>

  <COMMON_ID>103</COMMON_ID>

  <SWITCH_TYPE>P</SWITCH_TYPE>

  <SWITCH_CHARGE>100.00</SWITCH_CHARGE>

  <EFFECTIVE_DATE></EFFECTIVE_DATE>

  <FUND_LIST>

  <FUND>

  <FUND_CODE>1069</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>100.00</PERC>

  <AMOUNT></AMOUNT>

  <BUY_SELL>SELL</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1003001</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>25.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

  <FUND>

  <FUND_CODE>1070</FUND_CODE>

  <INVEST_MODEL>1</INVEST_MODEL>

  <PERC>50.00</PERC>

  <AMOUNT></AMOUNT>

  <SWITCH_CHARGE>75.00</SWITCH_CHARGE>

  <TRADED>N</TRADED>

  <BUY_SELL>BUY</BUY_SELL>

  </FUND>

   </FUND_LIST>

    </SWITCH>  

    </SWITCH_LIST>

             </POLICY> 

  </POLICY_LIST>          

     </BODY>

</MSG>

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In this XML I have a POLICY_LIST tag which could have one or more policy entities. Each Policy entity has a SWICH_LIST which could have multiple SWITCHES. And each SWITCH has a FUND_LIST with multiple funds. With a lot of searching the net I found the following way of reading this XML in one go into a PL/SQL table as follows

    select p.policy_no

         , p.instruction_source

         , s.common_id

         , s.switch_type

         , s.switch_charge

         , s.effdate

         , f.fund_code

         , f.invest_model

         , f.buy_sell

         , f.perc

         , f.amount

         , f.fund_switch_charge

    bulk collect into v_temp_switch_tab 

    from xmltable('/MSG/BODY/POLICY_LIST/POLICY' passing v_request_xml

                  columns policy_no          varchar2(20) path 'POLICY_NO'

                        , instruction_source varchar2(50) path 'INSTRUCTION_SOURCE'

                        , switch_list        xmltype      path 'SWITCH_LIST'

                 ) p

       , xmltable('SWITCH_LIST/SWITCH' passing p.switch_list

                  columns common_id          varchar2(50) path 'COMMON_ID'

                        , switch_type        varchar2(1)  path 'SWITCH_TYPE'

                        , switch_charge      number       path 'SWITCH_CHARGE'

                        , effdate            date         path 'EFFECTIVE_DATE'

                        , fund_list          xmltype      path 'FUND_LIST'

                  ) s

       , xmltable('FUND_LIST/FUND' passing s.fund_list 

                  columns fund_code          varchar2(10) path 'FUND_CODE'

                        , invest_model       number       path 'INVEST_MODEL'

                        , perc               number       path 'PERC'

                        , amount             number       path 'AMOUNT'

                        , buy_sell           varchar2(10) path 'BUY_SELL'

                        , fund_switch_charge number       path 'SWITCH_CHARGE') f;

--------------------------------------------------------------------------

Trouble with this approach is I have to depend on one of the tag values to separate multiple SWITCH tags on the same POLICY tag. Is there a better way of doing this ?? as in reading the XML in one go (Bulk Collect) and then getting the link based on the grouping of the tags in the input XML ?? Hope my explaination of the problem at hand is clear

Many Thanks for taking the time to understand this.

Parag

  • 1. Re: Load a nested XML into a nested PL/SQL table (associative array)
    Jason_(A_Non) Expert
    Currently Being Moderated

    I am not sure I follow your question.

     

    The query you show will return one row for each

    /MSG/BODY/POLICY_LIST/POLICY/SWITCH_LIST/SWITCH/FUND_LIST/FUND

    node in the XML, with data from that node and it's parents.  The parent(ancestor) data will be repeated as necessary across each row, based on what the parent(ancestor) for each FUND node is.

    What are you having trouble separating out?

  • 2. Re: Load a nested XML into a nested PL/SQL table (associative array)
    odie_63 Guru
    Currently Being Moderated

    Is there a better way of doing this ?? as in reading the XML in one go (Bulk Collect) and then getting the link based on the grouping of the tags in the input XML ??

    So I assume you want to preserve the nested structure of the XML?

    In that case, flattening the whole thing out is not really a good way.

     

    Ideally, you'd have to load the data into three different tables (POLICY, SWITCH and FUND) with PK/FK relationships.

    You may also want to use an object type with nested table attributes to hold the whole structure.

  • 3. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    Thanks for your promptness folks !

     

    As you have rightly said - the parent(ancestor) data gets repeated for each row.

     

    Consequence : I am unable to separate two independent switches based within the same policy node merely based on the grouping in the XML. I have to depend on the value of a data-element to do this, which by my experience, doesn't feel right !

     

    I am not quite sure how to load the data into three different tables and in doing so link them up as well. Do I necessarily need to have object types to implement this or can this be done using associative arrays.

     

    Appreciate your help

     

    Thanks

    Parag

  • 4. Re: Load a nested XML into a nested PL/SQL table (associative array)
    odie_63 Guru
    Currently Being Moderated

    Do I necessarily need to have object types to implement this or can this be done using associative arrays.

    That depends on your requirement really, and on how you plan to navigate through the data.

    Could you give us a little more details?

     

    Associative arrays won't help preserving the hierarchical structure of the data, so as said use either three different tables (if necessary, with generated PKs if there's no existing candidate in the XML) or an object that mirrors the XML structure.

    Here's an example of the first approach : How To : Load XML data into multiple tables | Odie's Oracle Blog

     

    I'll try to post an example using an object type if I have time.

  • 5. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    I dont think the example you have sent me answers my query ! Correct me if I am wrong here !!

     

    In the example you are having employees belonging to departments and the departments belonging to some locations and all these are optional sets. However, if all these were mandatory sets you would end up with the parent data getting repeated for each row - isnt it ??

     

    Also, I did not quite get the "for ordinality" clause that you have used !

     

    thanks

  • 6. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    Hey Guys

     

    A BIG THANK YOU to you both for helping me out !!!!

     

    The ordinality clause does the trick.

     

    Thanks Again

     

    Parag

  • 7. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    Hello Again,

     

    Taking this a bit more further - adding more complexity !

     

    Like i said in my previous post the ordinality clause did the trick for me. However, the parent data gets repeated for every child. Now, if I have two different tables for storing this data with a FK reference linking the two - is there any way i can directly load this data into oracle database tables, say using insert all, for instance.

     

    At the moment, I am loading this into a custom PL/SQL table and then iterating through this table to split the parent and child data into respective PL/SQL tables and then doing a bulk insert using these tables to insert data into the underlying tables.

     

    thanks

    Parag

  • 8. Re: Load a nested XML into a nested PL/SQL table (associative array)
    Jason_(A_Non) Expert
    Currently Being Moderated

    Based on Odie's blog, what SQL did you end up using?  I ask because his solution, while it does return the parent data repeating in appropriate rows, the conditions on the INSERT ALL prevent duplicate rows from being inserted into the parent tables.

  • 9. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    select * from

    (with t as (select xmltype('<MSG>

            <HEADER>

                <FILE_NAME>PS20130106-01.xml</FILE_NAME>

                <FILE_TYPE>PART SURRENDER</FILE_TYPE>

            </HEADER>

            <BODY>

              <POLICY_LIST>

                 <POLICY>

                   <POLICY_NO>401-002298</POLICY_NO>             

                   <INSTRUCTION_SOURCE>CLIENT</INSTRUCTION_SOURCE>

                   <COMMON_ID>101</COMMON_ID>

                   <REINVESTMENT>N</REINVESTMENT> 

                   <NOTIFICATION_DATE>29012014</NOTIFICATION_DATE>

                   <VALUE_TYPE>P</VALUE_TYPE>

                   <VALUE>0</VALUE>

            <FREE_WITHDRAWALS>A</FREE_WITHDRAWALS>

                   <CHARGE_PERC>0</CHARGE_PERC>

                   <CHARGE_AMOUNT>100</CHARGE_AMOUNT>

                   <FUND_LIST>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

                   </FUND_LIST>

                </POLICY>   

                 <POLICY>

                   <POLICY_NO>401-002298</POLICY_NO>             

                   <INSTRUCTION_SOURCE>CLIENT</INSTRUCTION_SOURCE>

                   <COMMON_ID>101</COMMON_ID>

                   <REINVESTMENT>N</REINVESTMENT> 

                   <NOTIFICATION_DATE>29012014</NOTIFICATION_DATE>

                   <VALUE_TYPE>P</VALUE_TYPE>

                   <VALUE>0</VALUE>

            <FREE_WITHDRAWALS>A</FREE_WITHDRAWALS>

                   <CHARGE_PERC>0</CHARGE_PERC>

                   <CHARGE_AMOUNT>100</CHARGE_AMOUNT>

                   <FUND_LIST>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

             <FUND>

              <FUND_CODE>1003001</FUND_CODE>

                         <INVEST_MODEL>1</INVEST_MODEL>

                         <VALUE>30</VALUE>

                      </FUND>

                   </FUND_LIST>

                </POLICY>   

              </POLICY_LIST>      

            </BODY>

          </MSG>

    ' ) as xml from dual)

        select p.pol_rec_id

             , p.policy_no

             , p.instruction_source

             , p.common_id

             , p.reinvestment

             , p.notification_date

             , p.value_type

             , p.value

             , p.free_withdrawal_option

             , p.charge_perc

             , p.charge_amount

             , f.fund_rec_id

             , f.fund_code

             , f.invest_model

             , f.fund_list_value

        from   t

             , xmltable('/MSG/BODY/POLICY_LIST/POLICY' passing t.xml

                        columns pol_rec_id for ordinality

                            , policy_no              varchar2(50) path 'POLICY_NO'

                            , instruction_source     varchar2(50) path 'INSTRUCTION_SOURCE'

                            , common_id              varchar2(50) path 'COMMON_ID'

                            , reinvestment           varchar2(50) path 'REINVESTMENT'

                            , notification_date      varchar2(50) path 'NOTIFICATION_DATE'

                            , value_type             varchar2(50) path 'VALUE_TYPE'

                            , value                  number       path 'VALUE'

                            , free_withdrawal_option varchar2(50) path 'FREE_WITHDRAWAL_OPTION'

                            , charge_perc            number       path 'CHARGE_PERC'

                            , charge_amount          number       path 'CHARGE_AMOUNT'

                            , fund_list              xmltype      path 'FUND_LIST'

                     ) p

            , xmltable('FUND_LIST/FUND' passing p.fund_list

                       columns fund_rec_id  for ordinality

                             , fund_code    varchar2(50) path 'FUND_CODE'

                             , invest_model number       path 'INVEST_MODEL'

                             , fund_list_value        number       path 'VALUE'

                      )  f) tmp;

     

    If you execute the above SQL, you will get 6 rows having two distinct values for pol_rec_id ( 3 records of pol_rec_id = 1 and 3 for pol_rec_id = 2). That being the case how do I use the INSERT ALL to insert only two records in the parent table (one for pol_rec_id 1 and one for pol_rec_id 2). The columns upto the fund_rec_id belong to the parent table. The child table may or may not have data. To be quite honest, I havent understood how Odie's implementation of the INSERT ALL will prevent duplicates being inserted

     

    thanks

    Parag

  • 10. Re: Load a nested XML into a nested PL/SQL table (associative array)
    Jason_(A_Non) Expert
    Currently Being Moderated

    As you noted, there are six rows being returned.  Three for the first policy and three for the second policy.  You have an ordinality in both your XMLTables.  Look at the values for fund_rec_id and you will see (in this order)

    1

    2

    3

    1

    2

    3

    Regarding ordinality, from the Oracle docs you get a only slightly helpful description of

    "FOR ORDINALITY specifies that column is to be a column of generated row numbers."

    What this implies is that for each instantiation of XMLTable in a query, the generated row number starts at 1.  One XMLTable instantiation is used to satisfy the XPATH /MSG/BODY/POLICY_LIST/POLICY.  This also generates a temporary XMLType (for this discussion), one per each row returned by the XMLTable and that is passed to another XMLTable to handle the XPATH of FUND_LIST/FUND.  As that second XMLTable is called twice, the ordinality starts at 1 each time.

     

    This is what Odie's example, and yours, can use to know when you should INSERT ALL into the parent table

    WHEN fund_rec_id = 1 THEN INTO <parent table>

    Each time that count starts over at 1, you know you have a new parent record.

     

    If you are also saying that there may be a POLICY without FUND_LIST/FUND children, then you need to look into the outer join ( +) usage that Odie's example shows on his second and third XMLTables.  This is why the WHEN clause is instead written

    WHEN fund_rec_id = 1 OR fund_rec_id IS NULL THEN INTO <parent table>

    as that handles the times where there is no child record and you still need to insert the parent record.

     

    That should give you enough to go off of, but if not, show what you have so far and we can make corrections to it.

  • 11. Re: Load a nested XML into a nested PL/SQL table (associative array)
    user597769 Newbie
    Currently Being Moderated

    with t as (select xmltype('<MSG>

            <HEADER>

                <FILE_NAME>DC20130102-01.xml</FILE_NAME>

                <FILE_TYPE>DEATH CLAIM</FILE_TYPE>

            </HEADER>

            <BODY>

              <CLAIM_LIST>

               <CLAIM>

                   <INSTRUCTION_SOURCE>CLIENT</INSTRUCTION_SOURCE>

                   <COMMON_ID>101</COMMON_ID>

                   <PERSON_WEB_REF>CSDF45345</PERSON_WEB_REF>

                   <FIRST_NAME>JOHN</FIRST_NAME>

                   <SURNAME>SMITH</SURNAME>

                   <DOB>06011966</DOB>

                   <NOTIFICATION_DATE>06012013</NOTIFICATION_DATE>

                   <EFFECTIVE_DATE>06012013</EFFECTIVE_DATE>

                   <CLAIM_TYPE>X</CLAIM_TYPE>

                   <CLAIM_REASON>AD</CLAIM_REASON>

                   <POLICY_LIST>

                      <POLICY_NO>401002052</POLICY_NO>

                      <POLICY_NO>401002050</POLICY_NO>

                      <POLICY_NO>401002051</POLICY_NO>

                   </POLICY_LIST>

               </CLAIM> 

               </CLAIM_LIST>        

           </BODY>

          </MSG>

    ' ) as xml from dual)

        select d.dc_rec_id

             , d.instruction_source

             , d.common_id

             , d.person_web_ref

             , d.first_name

             , d.surname

             , d.dob

             , d.notification_date

             , d.effective_date

             , d.claim_type

             , d.claim_reason

             , p.pol_rec_id

             , p.policy_no

        from   t left outer join

               xmltable('/MSG/BODY/CLAIM_LIST/CLAIM' passing t.xml

                      columns dc_rec_id         for ordinality

                            , instruction_source varchar2(50) path 'INSTRUCTION_SOURCE'

                            , common_id          varchar2(50) path 'COMMON_ID'

                            , person_web_ref     varchar2(50) path 'PERSON_WEB_REF'

                            , first_name         varchar2(50) path 'FIRST_NAME'

                            , surname            varchar2(50) path 'SURNAME'

                            , dob                varchar2(50) path 'DOB'

                            , notification_date  varchar2(50) path 'NOTIFICATION_DATE'

                            , effective_date     varchar2(50) path 'EFFECTIVE_DATE'

                            , claim_type         varchar2(50) path 'CLAIM_TYPE'

                            , claim_reason       varchar2(50) path 'CLAIM_REASON'

                            , policy_list        xmltype      path 'POLICY_LIST'

                     ) d on 1= 1

            left outer join

            xmltable('POLICY_LIST/POLICY_NO' passing d.policy_list

                      columns pol_rec_id for ordinality

                            , policy_no  varchar2(50) path 'POLICY_NO'

                      )  p on 1 = 1 

    ------------------

    In the output of this query, I get the ordinality columns but not the actual data - why ?? I am sure I am missing something very basic here

     

    thanks for your help

  • 12. Re: Load a nested XML into a nested PL/SQL table (associative array)
    odie_63 Guru
    Currently Being Moderated

    In the output of this query, I get the ordinality columns but not the actual data - why ??

    You mean for the POLICY_NO column?

    That's because the PATH expression in the COLUMNS clause is relative to the context item passed from the main XQuery expression 'POLICY_LIST/POLICY_NO' which, in this case, returns a sequence of POLICY_NO elements.

    Therefore, your PATH expression is resolved as 'POLICY_NO/POLICY_NO' which targets no existing node.

     

    In a situation like this, use the '.' (dot) notation to specify that you want to retrieve the value of the context item directly :

    policy_no  varchar2(50) path '.'

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points