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