1 123 Ram EMP 124 John EMP 124 Miller EMP 125 Rose EMP
代码是
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;
什么是实现此目的的好方法? ,我想在PL/SQL中实现此目的。我已经使用了许多实用程序和测试用例( dbms_xsl处理器、XML TABLE、ExtractValue等,但我一直没有太多运气).
如有任何帮助,我们将不胜感激。谢谢你。
最诚挚的问候,拉姆
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');
作为当前节点:
dbms_xslprocessor.selectnodes ( l_n ,'Assignments/Assignments_Record/Managers/Manager_Record' ) ;
修修你的路;)
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*