1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
declare
doc dbms_xmldom.domdocument;
nodedoc dbms_xmldom.domnode;
var xmltype;
old_critere varchar2(100) := 'a';
crit dbms_xmldom.domelement;
val dbms_xmldom.domelement;
root dbms_xmldom.domelement;
text dbms_xmldom.domtext;
buf varchar2(3000);
i number;
dir varchar2(200);
cpt number;
begin
var := xmltype('<root></root>');
doc := dbms_xmldom.newdomdocument(var);
nodedoc := dbms_xmldom.makenode(doc);
i := 1;
cpt := 0;
for j in 1..200 loop
for rec in (select ref, critere, valeur from xref where ref = 'PR0VAR2' order by critere)
loop
begin
root := dbms_xmldom.getDocumentElement(doc);
if (rec.critere != old_critere or rec.critere is null or old_critere is null) and not (rec.critere is null and old_critere is null) then
crit := dbms_xmldom.createelement(doc, 'crit');
crit := dbms_xmldom.makeelement(dbms_xmldom.appendchild(dbms_xmldom.makenode(root), dbms_xmldom.makenode(crit)));
dbms_xmldom.setAttribute(crit, 'nom', rec.critere);
dbms_xmldom.setAttribute(crit, 'idDebut', i);
dbms_xmldom.freenode(dbms_xmldom.makenode(crit));
end if;
val := dbms_xmldom.createelement(doc, 'valeur');
val := dbms_xmldom.makeelement(dbms_xmldom.appendchild(dbms_xmldom.makenode(crit), dbms_xmldom.makenode(val)));
dbms_xmldom.freenode(dbms_xmldom.makenode(crit));
text := dbms_xmldom.createtextnode(doc, rec.valeur);
text := dbms_xmldom.maketext(dbms_xmldom.appendchild(dbms_xmldom.makenode(val), dbms_xmldom.makenode(text)));
dbms_xmldom.freenode(dbms_xmldom.makenode(text));
dbms_xmldom.freenode(dbms_xmldom.makenode(val));
old_critere := rec.critere;
dbms_xmldom.setAttribute(crit, 'idFin', i);
dbms_xmldom.setAttribute(val, 'id', i);
i := i + 1;
dbms_xmldom.freenode(dbms_xmldom.makenode(root));
exception
when others then
dbms_output.put_line(sqlerrm);
goto fin;
end;
end loop;
end loop;
dbms_output.put_line(cpt);
<<fin>>
select value into dir from v$parameter where name = 'utl_file_dir';
dbms_xmldom.writetofile(doc, dir||'/test.xml');
end; |
Partager