Hello Tom,
I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I was not able to solve to my problem. probably because I don't know enough. I am using Oracle 11g.
I also want to do this all in PL/SQL. Here is my requirement:
1) I have a xml file ('test.xml') in server location 'MYDIR'
2) I want to read the xml data and store in to DB table
3) I am using 'BFILENAME' utility to load the xml data in to 'BFILE' and using 'dbms_xmlparser' utility to parsing the xml data.
4) Also using 'dbms_xslprocessor' utility to handling the xml nodes
5) I can able to read the 'Persons_Record' details from outer xml nodes in the outer loop('cur_emp'), But When I tried to read the 'Manager_Record' details from nested xml nodes in the inner loop('cur_mgr'), it is very hard read the corresponding nested nodes. My code is keeps reading entire 'Manager_Record' details (from top to bottom) in a single iteration.
Then control get exist from inner loop and start the second iteration in outer loop.
6) Ultimately, Again it is reading entire 'Manager_Record' details (from top to bottom) in the second iteration. I believe, it because of node initialization
Node initialization for 'Persons_Record' --> '/DATA_DS/G_1/G_2/FILE_FRAGMENT/HCM_Transaction_and_Foundation_Data_to_EBS/Persons/Persons_Record'
Node initialization for 'Manager_Record' --> '/DATA_DS/G_1/G_2/FILE_FRAGMENT/HCM_Transaction_and_Foundation_Data_to_EBS/Persons/Persons_Record/Assignments/Assignments_Record/Managers/Manager_Record'
7) It do have to exist from inner loop once it done read the corresponding 'Manager_Record' for that particular person.
I am including an example of the type of XML document and code I am dealing with.
'test.xml' is
<?xml version="1.0" encoding="UTF-8" ?>
<DATA_DS>
<G_1>
<PAYROLL_ACTION_ID>1</PAYROLL_ACTION_ID>
<G_2>
<FILE_FRAGMENT>
<HCM_Transaction_and_Foundation_Data_to_EBS>
<REP_CATEGORY_NAME>Foundation Data</REP_CATEGORY_NAME>
<Persons>
<OBJECT_ACTION_ID>9171</OBJECT_ACTION_ID>
<Persons_Record>
<Person_Number>123</Person_Number>
<Person_name>Ram</Person_name>
<Person_Type>EMP</Person_Type>
<Assignments>
<Assignments_Record>
<Assignment_Number>A123</Assignment_Number>
<Assignment_type>BC</Assignment_type>
<Mgr_Type>LINE_MANAGER</Mgr_Type>
<Managers>
<Manager_Record>
<Manager_Type>LINE_MANAGER_ONE</Manager_Type>
<Manager_Name>Line manager one name </Manager_Name>
<Manager_Number>Line manager one number</Manager_Number>
</Manager_Record>
</Managers>
<Managers>
<Manager_Record>
<Manager_Type>XX_SUPERVISOR_ONE</Manager_Type>
<Manager_Name>Supervisor one name</Manager_Name>
<Manager_Number>Supervisor one number</Manager_Number>
</Manager_Record>
</Managers>
</Assignments_Record>
</Assignments>
</Persons_Record>
</Persons>
<Persons>
<OBJECT_ACTION_ID>9172</OBJECT_ACTION_ID>
<Persons_Record>
<Person_Number>124</Person_Number>
<Person_name>John</Person_name>
<Person_Type>EMP</Person_Type>
<Assignments>
<Assignments_Record>
<Assignment_Number>A124</Assignment_Number>
<Assignment_type>AC</Assignment_type>
<Mgr_Type>LINE_MANAGER</Mgr_Type>
<Managers>
<Manager_Record>
<Manager_Type>LINE_MANAGER_TWO</Manager_Type>
<Manager_Name>Line manager two name</Manager_Name>
<Manager_Number>Line manager two numb</Manager_Number>
</Manager_Record>
</Managers>
<Managers>
<Manager_Record>
<Manager_Type>XX_SUPERVISOR_TWO</Manager_Type>
<Manager_Name>Supervisor two name</Manager_Name>
<Manager_Number>Supervisor two numb</Manager_Number>
</Manager_Record>
</Managers>
</Assignments_Record>
</Assignments>
</Persons_Record>
</Persons>
<Persons>
<OBJECT_ACTION_ID>9173</OBJECT_ACTION_ID>
<Persons_Record>
<Person_Number>124</Person_Number>
<Person_name>Miller</Person_name>
<Person_Type>EMP</Person_Type>
<Assignments>
<Assignments_Record>
<Assignment_Number>A124</Assignment_Number>
<Assignment_type>IC</Assignment_type>
<Mgr_Type>LINE_MANAGER</Mgr_Type>
<Managers>
<Manager_Record>
<Manager_Type>LINE_MANAGER_THREE</Manager_Type>
<Manager_Name>Line manager three name</Manager_Name>
<Manager_Number>Line manager three numb</Manager_Number>
</Manager_Record>
</Managers>
<Managers>
<Manager_Record>
<Manager_Type>XX_SUPERVISOR_THREE</Manager_Type>
<Manager_Name>Supervisor three name</Manager_Name>
<Manager_Number>Supervisor three numb</Manager_Number>
</Manager_Record>
</Managers>
</Assignments_Record>
</Assignments>
</Persons_Record>
</Persons>
<Persons>
<OBJECT_ACTION_ID>9174</OBJECT_ACTION_ID>
<Persons_Record>
<Person_Number>125</Person_Number>
<Person_name>Rose</Person_name>
<Person_Type>EMP</Person_Type>
<Assignments>
<Assignments_Record>
<Assignment_Number>A125</Assignment_Number>
<Assignment_type>BC</Assignment_type>
<Mgr_Type>LINE_MANAGER</Mgr_Type>
<Managers>
<Manager_Record>
<Manager_Type>LINE_MANAGER_FOUR</Manager_Type>
<Manager_Name>Line manager four name</Manager_Name>
<Manager_Number>Line manager four numb</Manager_Number>
</Manager_Record>
</Managers>
<Managers>
<Manager_Record>
<Manager_Type>XX_SUPERVISOR_FOUR</Manager_Type>
<Manager_Name>Supervisor four name</Manager_Name>
<Manager_Number>Supervisor four numb</Manager_Number>
</Manager_Record>
</Managers>
</Assignments_Record>
</Assignments>
</Persons_Record>
</Persons>
</HCM_Transaction_and_Foundation_Data_to_EBS>
</FILE_FRAGMENT>
</G_2>
</G_1>
</DATA_DS>
Code is
DECLARE
v_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_nl1 dbms_xmldom.DOMNodeList;
l_n1 dbms_xmldom.DOMNode;
l_temp_holder1 varchar2(500);
l_temp_holder2 varchar2(500);
l_temp_holder3 varchar2(500);
l_temp_holder4 varchar2(500);
l_temp_holder5 varchar2(500);
l_temp_holder6 varchar2(500);
l_temp_holder7 varchar2(500);
l_truncator number := 0;
l_count number := 0;
l_count1 number := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE ('*Starting*');
v_bfile := BFILENAME( 'MYDIR', 'test.xml' );
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(v_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob, src_lob => v_bfile, amount => dbms_lob.getLength(v_bfile));
dbms_lob.close(v_bfile);
l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/DATA_DS/G_1/G_2/FILE_FRAGMENT/Foundation_Data/Persons/Persons_Record');
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1
LOOP
l_n := dbms_xmldom.item(l_nl, cur_emp);
DBMS_OUTPUT.PUT_LINE ('*Main LOOP* Itteration - '||l_count1 );
dbms_xslprocessor.valueOf(l_n,'Person_Number/text()',l_temp_holder1);
dbms_xslprocessor.valueOf(l_n,'Person_Type/text()',l_temp_holder2);
dbms_xslprocessor.valueOf(l_n,'Assignments/Assignments_Record/Assignment_Number/text()',l_temp_holder3);
dbms_xslprocessor.valueOf(l_n,'Assignments/Assignments_Record/Assignment_Manager_Type/text()',l_temp_holder4);
l_nl1 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/DATA_DS/G_1/G_2/FILE_FRAGMENT/Foundation_Data/Persons/Persons_Record/Assignments/Assignments_Record/Managers/Manager_Record');
FOR cur_mgr IN 0 .. dbms_xmldom.getLength(l_nl1) - 1
LOOP
l_n1 := dbms_xmldom.item(l_nl1, cur_mgr);
dbms_xslprocessor.valueOf(l_n1,'Manager_Type/text()',l_temp_holder5);
dbms_xslprocessor.valueOf(l_n1,'Manager_Name/text()',l_temp_holder6);
dbms_xslprocessor.valueOf(l_n1,'Manager_Number/text()',l_temp_holder7);
DBMS_OUTPUT.PUT_LINE (' mgr type- '||l_temp_holder5);
DBMS_OUTPUT.PUT_LINE (' mgr name- '||l_temp_holder6);
DBMS_OUTPUT.PUT_LINE (' mgr numb- '||l_temp_holder7);
l_count := l_count+1;
IF MOD(l_count,2) <> 0 THEN
EXIT;
END IF;
END LOOP;
l_count1 := l_count1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('*Ending*');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error '||SQLCODE||SQLERRM);
END;
/
SHOW ERR;
What is a good way to do this?, I want to do this in PL/SQL. I have played with a number of utilities and test cases (dbms_xslprocessor, XMLTABLE, ExtractValue etc. but I have not been having much luck).
Any help would be greatly appreciated. Thank you.
Best Regards, Ram
Before you start the manager loop, you're going back to the original document, rather than just selecting those nodes
Seems to me you just need to replace the makeNode(l_doc) call in this:
dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/DATA_DS/G_1/G_2/FILE
_FRAGMENT/Foundation_Data/Persons/Persons_Record/Assignments/Assignments_Record/
Managers/Manager_Record');
to be the current node:
dbms_xslprocessor.selectnodes ( l_n ,'Assignments/Assignments_Record/Managers/Manager_Record' ) ;
And fix some of your paths ;)
declare
v_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_nl1 dbms_xmldom.domnodelist;
l_n1 dbms_xmldom.domnode;
l_temp_holder1 varchar2 ( 500 ) ;
l_temp_holder2 varchar2 ( 500 ) ;
l_temp_holder3 varchar2 ( 500 ) ;
l_temp_holder4 varchar2 ( 500 ) ;
l_temp_holder5 varchar2 ( 500 ) ;
l_temp_holder6 varchar2 ( 500 ) ;
l_temp_holder7 varchar2 ( 500 ) ;
l_truncator number := 0;
l_count number := 0;
l_count1 number := 0;
begin
dbms_output.put_line ( '*Starting*' ) ;
v_bfile := bfilename ( 'TMP', 'test.xml' ) ;
dbms_lob.createtemporary ( l_clob, cache=>false ) ;
dbms_lob.open ( v_bfile, dbms_lob.lob_readonly ) ;
dbms_lob.loadfromfile ( dest_lob => l_clob, src_lob => v_bfile, amount => dbms_lob.getlength ( v_bfile ) ) ;
dbms_lob.close ( v_bfile ) ;
l_parser := dbms_xmlparser.newparser;
dbms_xmlparser.parseclob ( l_parser, l_clob ) ;
l_doc := dbms_xmlparser.getdocument ( l_parser ) ;
dbms_lob.freetemporary ( l_clob ) ;
dbms_xmlparser.freeparser ( l_parser ) ;
l_nl := dbms_xslprocessor.selectnodes (
dbms_xmldom.makenode ( l_doc ) ,
'/DATA_DS/G_1/G_2/FILE_FRAGMENT/HCM_Transaction_and_Foundation_Data_to_EBS/Persons/Persons_Record' ) ;
for cur_emp in 0 .. dbms_xmldom.getlength ( l_nl ) - 1
loop
l_n := dbms_xmldom.item ( l_nl, cur_emp ) ;
dbms_output.put_line ( '*Main LOOP* Itteration - '||l_count1 ) ;
dbms_xslprocessor.valueof ( l_n,'Person_Number/text()',l_temp_holder1 ) ;
dbms_xslprocessor.valueof ( l_n,'Person_Type/text()',l_temp_holder2 ) ;
dbms_xslprocessor.valueof ( l_n,'Assignments/Assignments_Record/Assignment_Number/text()',l_temp_holder3 ) ;
dbms_xslprocessor.valueof ( l_n,'Assignments/Assignments_Record/Assignment_Manager_Type/text()',l_temp_holder4 ) ;
l_nl1 := dbms_xslprocessor.selectnodes ( l_n ,'Assignments/Assignments_Record/Managers/Manager_Record' ) ;
for cur_mgr in 0 .. dbms_xmldom.getlength ( l_nl1 ) - 1
loop
l_n1 := dbms_xmldom.item ( l_nl1, cur_mgr ) ;
dbms_xslprocessor.valueof ( l_n1,'Manager_Type/text()',l_temp_holder5 ) ;
dbms_xslprocessor.valueof ( l_n1,'Manager_Name/text()',l_temp_holder6 ) ;
dbms_xslprocessor.valueof ( l_n1,'Manager_Number/text()',l_temp_holder7 ) ;
dbms_output.put_line ( 'mgr type- '||l_temp_holder5 ) ;
dbms_output.put_line ( 'mgr name- '||l_temp_holder6 ) ;
dbms_output.put_line ( 'mgr numb- '||l_temp_holder7 ) ;
l_count := l_count+1;
if mod ( l_count,2 ) <> 0 then
exit;
end if;
end loop;
l_count1 := l_count1+1;
end loop;
dbms_output.put_line ( '*Ending*' ) ;
exception
when others then
dbms_output.put_line ( 'Error '||sqlcode||sqlerrm ) ;
end;
/
*Starting*
*Main LOOP* Itteration - 0
mgr type- LINE_MANAGER_ONE
mgr name- Line manager one name
mgr numb- Line manager one
number
*Main LOOP* Itteration - 1
mgr type- LINE_MANAGER_TWO
mgr name- Line manager two
name
mgr numb- Line manager two
numb
mgr type- XX_SUPERVISOR_TWO
mgr name- Supervisor two
name
mgr numb- Supervisor two
numb
*Main LOOP* Itteration - 2
mgr type- LINE_MANAGER_THREE
mgr name- Line manager three
name
mgr numb- Line manager three
numb
mgr type- XX_SUPERVISOR_THREE
mgr name- Supervisor three
name
mgr numb- Supervisor three
numb
*Main LOOP* Itteration - 3
mgr type- LINE_MANAGER_FOUR
mgr name- Line manager four
name
mgr numb- Line manager four
numb
mgr type- XX_SUPERVISOR_FOUR
mgr name- Supervisor four
name
mgr numb- Supervisor four
numb
*Ending*