Amir
Reputation: 1979
easiest way to extract Oracle form xml format data
I have a few hundred oracle form that I convert them into XML. I want to extract any text (value) that could be useful for me for checking some interdependency among all the forms which I have.
I tried to insert them into a table in the database with full XML format but the challenge is the size of the table is quite high and it takes around 10min to query the table.
therefore I want to remove all the unnecessary text from the XML format and just keep what is required.
what is your suggestion? I am using python and useful info form me will be like block name, any program written inside the oracle form(In program unit tag)
Also, take note that my Oracle XML format is :
<?xml version = '1.0' encoding = 'UTF-8'?>
<Module version="111020200" xmlns="http://xmlns.oracle.com/Forms">
<FormModule Name="TEST" DirtyInfo="true" Title="MODULE1" MenuModule="DEFAULT&SMARTBAR" ConsoleWindow="WINDOW1">
<Coordinate CharacterCellWidth="5" DefaultFontScaling="true" CoordinateSystem="Real" CharacterCellHeight="14" RealUnit="Point"/>
<Block Name="BLOCK2" DirtyInfo="true" ScrollbarWidth="9" ScrollbarLength="135">
<Item Name="ROUND_RULE" Prompt="round rule" YPosition="143" TabPageName="" XPosition="110" DirtyInfo="true" Width="45" PromptAttachmentOffset="11" PromptAlignOffset="1" CanvasName="CANVAS5" Height="14"/>
<Item Name="ROUND_PREM" MaximumLength="28" YPosition="143" XPosition="215" DataType="Number" Prompt="round prem" TabPageName="" DirtyInfo="true" PromptAttachmentOffset="7" Width="102" PromptAlignOffset="1" CanvasName="CANVAS5" Height="14"/>
<Item Name="ITEM7" YPosition="178" TabPageName="" Label="return" XPosition="142" DirtyInfo="true" Width="45" CanvasName="CANVAS5" Height="14" ItemType="Push Button">
<Trigger Name="WHEN-BUTTON-PRESSED" TriggerText=":value := ffn_round_premium(:Round_rule,:Round_Prem);" DirtyInfo="true"/>
</Item>
<Item Name="VALUE" MaximumLength="28" YPosition="209" XPosition="145" DataType="Number" Prompt="value" TabPageName="" DirtyInfo="true" PromptAttachmentOffset="5" Width="97" PromptAlignOffset="2" CanvasName="CANVAS5" Height="14"/>
</Block>
<Canvas Name="CANVAS5" ViewportHeight="324" ViewportWidth="360" DirtyInfo="true" Width="360" Height="324" WindowName="WINDOW1"/>
<ProgramUnit Name="FFN_ROUND_PREMIUM" ProgramUnitType="Function" ProgramUnitText="Function ffn_round_premium (P_Round_rule in varchar2,P_Rounded_Prem number) &#10;RETURN NUMBER IS&#10; Cursor cr_round_premium is select v_calculation_type,v_round_needed,v_decimal_side,&#10; n_round_digits,v_round_method,n_round_to ,&#10; v_first_cut_digit_check &#10; from Gnlu_calc_rounding_rules &#10; where v_round_rule_ref = p_round_rule; &#10; Lv_cal_type varchar2(10);	&#10; Lv_round_needed varchar2(1);	 &#10; Lv_decimal_side varchar2(10); &#10; Ln_round_digits number; &#10; Lv_round_method varchar2(10); &#10; Ln_round_to number; &#10; Lv_first_cut_digit_check varchar2(1); &#10; Ln_last_digit number; &#10; Ln_previous_digit number;&#10; ln_rounded_prem number := 0; &#10; ln_prem number := 0;&#10; ln_first_value number := 0;&#10; ln_temp number := 0;&#10; lv_temp varchar2(50); &#10;begin &#10; open cr_round_premium ;&#10; fetch cr_round_premium into Lv_cal_type,&#10; Lv_round_needed,&#10; Lv_decimal_side,&#10; Ln_round_digits, &#10; Lv_round_method,&#10; Ln_round_to,&#10; Lv_first_cut_digit_check;&#10; close cr_round_premium; &#10; /*Round_needed = NO, then, the input value is returned back */ &#10; If lv_cal_type = 'P' then&#10;	 If Lv_round_needed ='N' then&#10;	 	 ln_prem := p_rounded_prem;&#10;	 elsif lv_round_needed = 'Y' then&#10;	 	 ln_rounded_prem := p_rounded_prem;&#10;	 ln_first_value := p_rounded_prem;&#10; If lv_decimal_side = 'B' and lv_first_cut_digit_check = 'N' then&#10; ln_rounded_prem := floor(ln_rounded_prem);&#10; if ln_round_digits = 0 then&#10; 	 ln_round_digits := 1;&#10; end if;	 &#10; elsif lv_decimal_side = 'A' and lv_first_cut_digit_check = 'N' then&#10;&#10;	 	 if floor(ln_rounded_prem) <> ln_rounded_prem then&#10;	 	 	 lv_temp := substr(ln_rounded_prem ,instr(ln_rounded_prem ,'.',1)+1) ; &#10;	 	 	 if substr(lv_temp,1,1) = 0 then&#10;	 	 	 	 lv_temp := 'ZERO';&#10;	 	 	 end if;	 &#10;			 ln_rounded_prem:= substr(ln_rounded_prem ,instr(ln_rounded_prem ,'.',1)+1) ; &#10;			 ln_rounded_prem := substr(ln_rounded_prem,1,ln_round_digits);&#10;			 	 elsif floor(ln_rounded_prem) = ln_rounded_prem then &#10;	 	 	 ln_rounded_prem := 0;&#10;			 	 end if;&#10;			 	 if ln_round_digits = 0 then&#10; 	 ln_round_digits := 1;&#10;			 	 end if;&#10;	 	 elsif lv_first_cut_digit_check = 'Y' and ln_round_digits = 0 then&#10;	 	 ln_rounded_prem := round(ln_rounded_prem);&#10;	 	 ln_prem := ln_rounded_prem;&#10;	 end if; &#10;	 &#10;	 if lv_decimal_side = 'A' and lv_round_method in ('F','C') and lv_first_cut_digit_check = 'N' and Ln_round_digits <> 0 then&#10;	 If ln_round_to = 5 and lv_round_method = 'F' then &#10;	 if substr(ln_rounded_prem,length(ln_rounded_prem),1) >= 5 then&#10;		 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1) ||'5'; &#10;	 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) < 5 then &#10;	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1) ||'0'; &#10;	 end if;	 &#10;	 End if;&#10;	 &#10;	 If ln_round_to = 5 and lv_round_method = 'C' then &#10;	 if lv_temp = 'ZERO'	 then&#10;	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'0'; 	&#10;	 else &#10;			 if substr(ln_rounded_prem,length(ln_rounded_prem),1) = 0 then&#10;			 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'0'; &#10;			 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) > 0 and&#10;		 	 substr(ln_rounded_prem,length(ln_rounded_prem),1) <= 5 then&#10;		 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'5';&#10;				 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) > 5 and &#10;			 	 substr(ln_rounded_prem,length(ln_rounded_prem),1) <= 9 and&#10;			 	 Ln_round_digits <> 1 then &#10;			 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-2)||substr(ln_rounded_prem,length(ln_rounded_prem)-1,1)+1||'0'; &#10;			 	 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) > 5 and &#10;			 	 substr(ln_rounded_prem,length(ln_rounded_prem),1) <= 9 and&#10;			 	 Ln_round_digits = 1 then&#10;			 	 ln_first_value := floor(ln_first_value)+ 1;&#10;			 	 ln_prem := 0; 	 &#10;			 	 end if;&#10;			 	 end if; &#10;	 End if;&#10;	 If ln_round_to = 0 and lv_round_method = 'F' then &#10;	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1) ||'0'; &#10;	 End if;&#10;	 &#10;	 If ln_round_to = 0 and lv_round_method = 'C' then &#10;	 if lv_temp = 'ZERO'	 then&#10;	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'0'; 	&#10;	 else &#10;			 if substr(ln_rounded_prem,length(ln_rounded_prem),1) = 0 and ln_round_digits <> 1 then&#10;			 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'0'; &#10;			 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) > 0 and &#10;			 	 substr(ln_rounded_prem,length(ln_rounded_prem),1) <= 9 and&#10;			 	 Ln_round_digits <> 1 then &#10;			 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-2)||substr(ln_rounded_prem,length(ln_rounded_prem)-1,1)+1||'0'; &#10;			 	 elsif substr(ln_rounded_prem,length(ln_rounded_prem),1) > 0 and &#10;			 	 substr(ln_rounded_prem,length(ln_rounded_prem),1) <= 9 and&#10;			 	 Ln_round_digits = 1 then&#10;			 	 ln_first_value := floor(ln_first_value)+ 1;&#10;			 	 ln_prem := 0; 	 &#10;			 	 end if;	 &#10; end if;		 &#10;	 End if;&#10;	 ln_prem := floor(ln_first_value)||'.'||ln_prem;&#10;	 elsif lv_decimal_side = 'B' and lv_round_method in ('F','C') &#10;	 and lv_first_cut_digit_check = 'N' and Ln_round_digits <> 0 then&#10;	 If ln_round_to = 0 and lv_round_method = 'C' then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);&#10; 	 if ln_temp >= 1 and ln_temp <= 4 then&#10; 	 	 ln_temp := ln_temp + 5;&#10; 	 end if;&#10; 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp||substr(ln_rounded_prem,length(ln_rounded_prem)-ln_round_digits+2,ln_round_digits-1);&#10; 	 ln_prem := round(ln_prem,'-'||ln_round_digits);&#10; end if;	 &#10; If ln_round_to = 0 and lv_round_method = 'F' then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);&#10; 	 if ln_temp >= 5 and ln_temp <= 9 then&#10; 	 	 ln_temp := ln_temp - 5;&#10; 	 end if;&#10; 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp||substr(ln_rounded_prem,length(ln_rounded_prem)-ln_round_digits+2,ln_round_digits-1);&#10; 	 ln_prem := round(ln_prem,'-'||ln_round_digits);&#10; end if;&#10; &#10; If ln_round_to = 5 and lv_round_method = 'C' then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);&#10; 	 if ln_temp >= 1 and ln_temp <= 5 then&#10; 	 	 ln_temp := 5;&#10; 	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp;&#10; 	 elsif ln_temp >= 6 and ln_temp <= 9 then&#10; 	 	 ln_temp := 0;&#10; 	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)+1||ln_temp;&#10; 	 	 if ln_prem = 0 then&#10; 	 	 	 ln_prem := 10;&#10; 	 	 	 ln_rounded_prem := ln_rounded_prem || 1;&#10; 	 	 end if;	 &#10; 	 elsif ln_temp = 0 then&#10; 	 	 ln_temp := 0;&#10; 	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp;&#10; 	 end if;&#10; 	 ln_prem := rpad(ln_prem,length(ln_rounded_prem),0);&#10; end if;&#10; &#10; If ln_round_to = 5 and lv_round_method = 'F' then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);&#10; 	 if ln_temp >= 0 and ln_temp <= 4 then&#10; 	 	 ln_temp := 0;&#10; 	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp;&#10; 	 elsif ln_temp >= 5 and ln_temp <= 9 then&#10; 	 	 ln_temp := 5;&#10; 	 	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||ln_temp;&#10; 	 end if;	 &#10; 	 ln_prem := rpad(ln_prem,length(ln_rounded_prem),0);&#10; 	 end if;&#10; elsif lv_round_method = 'R' and ln_round_to = 0 and lv_first_cut_digit_check = 'N' then&#10; 	 	 if lv_decimal_side = 'A' then&#10; 	 	 ln_temp := nvl(substr(ln_rounded_prem,ln_round_digits,1),0);&#10; 	 	 if lv_temp = 'ZERO'	 then&#10;	 ln_prem := 0;&#10;	 else&#10;		 	 if ln_temp in (0,1,2,3,4) then&#10;		 	 	 ln_prem :=substr(ln_rounded_prem,1,ln_round_digits-1)||'0';&#10;		 	 elsif ln_temp in (5,6,7,8,9) and ln_round_digits <> 1 then&#10;		 	 	 ln_prem :=substr(ln_rounded_prem,1,ln_round_digits-1)+1||'0';&#10;		 	 elsif ln_temp in (5,6,7,8,9) and ln_round_digits =1 then &#10;		 	 	 ln_prem := 0;&#10;		 	 	 ln_first_value := floor(ln_first_value) + 1;&#10;		 	 end if;&#10;		 	 end if; &#10; 	 ln_prem := floor(ln_first_value)||'.'||ln_prem;&#10; elsif lv_decimal_side = 'B' and ln_round_digits <> 0 then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);	&#10; 	 if ln_temp in (0,1,2,3,4) then&#10; 	 	 ln_prem :=substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||'0';&#10; 	 elsif ln_temp in (5,6,7,8,9) and ln_round_digits <> 0 then&#10; 	 	 ln_prem :=substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)+1||'0';&#10; 	 	 if ln_prem = 0 then&#10; 	 	 	 ln_prem := 10;&#10; 	 	 	 ln_rounded_prem := ln_rounded_prem || 1;&#10; 	 	 end if;	&#10; 	 end if;&#10; 	 ln_prem := rpad(ln_prem,length(ln_rounded_prem),0);&#10; 	 elsif lv_decimal_side = 'B' and ln_round_digits = 0 then&#10; ln_prem := ln_rounded_prem;&#10; end if; 	 	&#10; &#10; elsif lv_round_method = 'R' and ln_round_to = 5 and lv_first_cut_digit_check = 'N' then&#10; 	 	 if lv_decimal_side = 'A' then&#10; 	 	 	 if lv_temp = 'ZERO'	 then&#10;	 ln_prem := substr(ln_rounded_prem,1,length(ln_rounded_prem)-1)||'0'; 	&#10;	 else &#10;		 	 	 ln_temp := nvl(substr(ln_rounded_prem,ln_round_digits,1),0);&#10;		 	 if ln_temp in (0,1,2) then&#10;		 	 	 ln_prem :=substr(ln_rounded_prem,1,ln_round_digits-1)||'0';&#10;		 	 elsif ln_temp in (3,4,5,6,7) then &#10;		 	 	 ln_prem :=substr(ln_rounded_prem,1,ln_round_digits-1)||'5';&#10;		 	 elsif ln_temp in (8,9) and ln_round_digits <> 1 then&#10;		 	 	 ln_prem :=substr(ln_rounded_prem,1,ln_round_digits-1)+1;&#10;		 	 	 ln_prem := rpad(ln_prem,length(ln_prem+1),0);&#10;		 	 elsif ln_temp in (8,9) and ln_round_digits =1 then &#10;		 	 	 ln_prem := 0;&#10;		 	 	 ln_first_value := floor(ln_first_value) + 1;&#10;		 	 end if; &#10;		 	 end if; &#10;		 	 ln_prem := floor(ln_first_value)||'.'||ln_prem;&#10; elsif lv_decimal_side = 'B' and ln_round_digits <> 0 then&#10; 	 ln_temp := substr(ln_rounded_prem,(length(ln_rounded_prem)-ln_round_digits+1),1);	&#10; 	 if ln_temp in (0,1,2) then&#10; 	 	 ln_prem :=substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||'0';&#10; 	 elsif ln_temp in (3,4,5,6,7) then &#10; 	 	 ln_prem :=substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)||'5';&#10; 	 elsif ln_temp in (8,9) and ln_round_digits <> 0 then&#10; 	 	 ln_prem :=substr(ln_rounded_prem,1,length(ln_rounded_prem)-ln_round_digits)+1||'0';&#10; 	 	 if ln_prem = 0 then&#10; 	 	 	 ln_prem := 10;&#10; 	 	 	 ln_rounded_prem := ln_rounded_prem || 1;&#10; 	 	 end if;	&#10; 	 end if;&#10; 	 ln_prem := rpad(ln_prem,length(ln_rounded_prem),0);&#10; 	 elsif lv_decimal_side = 'B' and ln_round_digits = 0 then&#10; ln_prem := ln_rounded_prem;&#10; end if; 	 &#10; End if;&#10; end if;&#10;	 end if; &#10; RETURN ln_prem ;&#10;End; "/>
<Window Name="WINDOW1" Width="360" Height="324"/>
</FormModule>
</Module>
Upvotes: 0
Views: 356
Answers (1)
CallMePhil
Reputation: 1657
Ok so from what I gathered from what you have said. This is code that will grab all child items and extract their values only. It converts those values into a single string that is added to a list. The list is return after finding all child elements. I don't know how fluent you are with coding but this is referred to as a recursive function. It keeps calling itself until there is no more work for it to do.
import xml.etree.ElementTree as ET
xml_items = []
root = ET.parse('data.xml').getroot()[0]
def get_attribs(element, items=None):
items = items if items is not None else []
items.append(' '.join([v for v in element.attrib.values()]))
for child in element:
get_attribs(child, items)
return items
get_attribs(root)
Result
['TEST true MODULE1 DEFAULT&SMARTBAR WINDOW1', '5 true Real 14 Point', 'BLOCK2 true 9 135', 'ROUND_RULE round rule 143 110 true 45 11 1 CANVAS5 14', ...]
Upvotes: 1
Related Questions
- Extracting XML in VBA from Oracle DB
- Parse XML in python and add it to database
- Extract XML in a Oracle Database Table Column
- Export Oracle database table as XML file using Python?
- Extracting data from xml using python in oracle soa suite 12c
- Extracting value from xml data in Oracle
- XML data extraction in oracle
- How to extract nested XMLType Data from Oracle Database?
- retrieving XML form with Python Selenium
- read data form xml to list