Home » Developer & Programmer » JDeveloper, Java & XML » problems while parsing an xml file into a tables
| problems while parsing an xml file into a tables [message #177933] |
Sun, 18 June 2006 03:06 |
yairk30
Messages: 14
Registered: May 2006
Location: israel
|
Junior Member |
|
|
hei..
i have a problem while tring to parse an xml file and insert the results into a document.
generaly, i use a procedure that base on a proc that you've demonstrated here in a previously article.
my xml file contain 2 primery nodes ("collect_packages") where each one contain it's own sub set of nodes (called "sub_packeges" - contain 3 instance for the 1st primary node , 2 - for the 2nd.).
in my proc (shown below) i try to retrieve the results from the xml file using two loops. outer loop should insert data into a main table("MOBILE_COLLECT_header"). inner loop should insert data into a sub table ("MOBILE_COLLECT_lines").
the problem is that while the outer loop recognize 2 nodse , the inner one recognize 5 (3+2) and run 5 times for each round. as a result, it's insert totally a 10 records into the sub table instead of 5.
this is my xml:
<xml>
- <Collect>
- <Collect_packages>
<Temp_transition_num>1070</Temp_transition_num>
<Collect_date_time>11/05/06 15:45:57</Collect_date_time>
<Package_Qty>5</Package_Qty>
<Mass_ID>1447857</Mass_ID>
- <Sub_packages>
<Package_Num>13258910</Package_Num>
</Sub_packages>
- <Sub_packages>
<Package_Num>13258911</Package_Num>
</Sub_packages>
- <Sub_packages>
<Package_Num>13258912</Package_Num>
</Sub_packages>
</Collect_packages>
- <Collect_packages>
<Temp_transition_num>1071</Temp_transition_num>
<Collect_date_time>11/05/06 15:45:57</Collect_date_time>
<Package_Qty>5</Package_Qty>
<Mass_ID>1447857</Mass_ID>
- <Sub_packages>
<Package_Num>13258914</Package_Num>
</Sub_packages>
- <Sub_packages>
<Package_Num>13258915</Package_Num>
</Sub_packages>
</Collect_packages>
</Collect>
this is my proc:
procedure xml_collect2 (in_filename in varchar2)
is
my_dir varchar2(10) := 'XML_DIR';
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);
l_nl2 dbms_xmldom.DOMNodeList;
l_n2 dbms_xmldom.DOMNode;
l_temp2 VARCHAR2(1000);
V_TEMP_TRANSITION_NUM MOBILE_COLLECT_LINES.TEMP_TRANSITION_NUM%type;
V_COLLECT_ID MOBILE_COLLECT_LINES.COLLECT_ID%type;
V_SUB_PACKAGE MOBILE_COLLECT_LINES.SUB_PACKAGE%type;
V_PACKAGE_QTY MOBILE_COLLECT_HEADER.PACKAGE_QTY%type;
V_MASS_ID MOBILE_COLLECT_HEADER.MASS_ID%type;
V_COLLECT_DATE_TIME MOBILE_COLLECT_HEADER.COLLECT_DATE_TIME%type;
v_count number;
src_csid NUMBER := NLS_CHARSET_ID('UTF8');
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
v_line_h number;
v_location_line varchar2(1000);
BEGIN
l_bfile := BFileName(my_dir, in_filename);
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile), dest_offset,src_offset, src_csid, lang_context, warning);
dbms_lob.close(l_bfile);
-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD/MM/RR HH24:MI:SS''');
-- Create a parser.
l_parser := dbms_xmlparser.newParser;
-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
-- Get a list of all the nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/Collect/Collect_packages');
-- Loop through the list and create a new record in a tble collection
-- for each record.
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
dbms_output.put_line('header '|| cur_emp);
l_n := dbms_xmldom.item(l_nl, cur_emp);
-- Use XPATH syntax to assign values to he elements of the collection.
dbms_xslprocessor.valueOf(l_n,'Temp_transition_num/text()',V_TEMP_TRANSITION_NUM);
dbms_xslprocessor.valueOf(l_n,'Collect_date_time/text()',V_COLLECT_DATE_TIME);
dbms_xslprocessor.valueOf(l_n,'Package_Qty/text()',V_PACKAGE_QTY);
dbms_xslprocessor.valueOf(l_n,'Mass_ID/text()',V_MASS_ID);
-- dbms_xslprocessor.valueOf(l_n,'COLLECT_ID/text()',V_COLLECT_ID);
v_line_h := v_line_h +1;
INSERT INTO MOBILE_COLLECT_HEADER
(TEMP_TRANSITION_NUM,
COLLECT_ID,
PACKAGE_QTY,
MASS_ID,
COLLECT_DATE_TIME)
VALUES
(V_TEMP_TRANSITION_NUM,
V_COLLECT_ID,
V_PACKAGE_QTY,
V_MASS_ID,
V_COLLECT_DATE_TIME);
l_nl2 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/Collect/Collect_packages/Sub_packages');
FOR cur_emp2 IN 0 .. dbms_xmldom.getLength(l_nl2) -1 LOOP
dbms_output.put_line('line '|| cur_emp2);
l_n2 := dbms_xmldom.item(l_nl2, cur_emp2);
dbms_xslprocessor.valueOf(l_n2,'Package_Num/text()',V_SUB_PACKAGE);
INSERT INTO MOBILE_COLLECT_LINES
(TEMP_TRANSITION_NUM,
COLLECT_ID,
SUB_PACKAGE)
VALUES
(V_TEMP_TRANSITION_NUM,
V_COLLECT_ID,
V_SUB_PACKAGE);
END LOOP;
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);
--remove file to another directory
remove_file(my_dir,in_filename,'XML_DIR_2',in_filename);
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(substr(sqlerrm,1,100));
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
ROLLBACK;
END;
this is my tables:
MOBILE_COLLECT_HEADER:
TEMP_TRANSITION_NUM NUMBER
COLLECT_ID NUMBER
PACKAGE_QTY NUMBER
MASS_ID NUMBER
COLLECT_DATE_TIME DATE
MOBILE_COLLECT_LINES:
TEMP_TRANSITION_NUM NUMBER
COLLECT_ID NUMBER
SUB_PACKAGE NUMBER(12)
i running it on 9.2 platform.
thanks , yair
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 05:37:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
