create or replace procedure DBToXml(strSQL varchar2,dir varchar2 ,filename varchar2)authid current_user is
result CLOB;
xmlstr VARCHAR2(32767);
line VARCHAR2(2000);
line_no INTEGER := 1;
output_file UTL_FILE.FILE_TYPE;
ii number;
BEGIN
ii:=0;
select count(*) into ii from all_directories where DIRECTORY_NAME='TEST_DIR';
if ii>0 then
execute immediate 'drop directory test_dir';
end if;
execute immediate 'CREATE DIRECTORY TEST_DIR AS '''||dir||'''';
result := DBMS_XMLQuery.getXml(strSQL);
output_file := UTL_FILE.FOPEN('TEST_DIR', filename, 'w');
xmlstr := DBMS_LOB.SUBSTR(result,32747);
xmlstr:=substr(xmlstr,1,21)||' encoding=''gb2312'' '||substr(xmlstr,22,32747);
xmlstr:=substr(xmlstr,1,43)||'<!DOCTYPE STUDENTS SYSTEM "xmlstudent.dtd">'||substr(xmlstr,44,32747);
xmlstr:=replace(xmlstr,'ROWSET','STUDENTS');
xmlstr:=replace(xmlstr,'ROW','STUDENT');
UTL_FILE.put(output_file,xmlstr);
UTL_FILE.FCLOSE(output_file);
xmlstr:='<?xml version="1.0" encoding="gb2312" ?>
<!ELEMENT STUDENTS (STUDENT+)>
<!ELEMENT STUDENT ( STUID , STUNAME , STUSEX , STUBIRTH , STUTEL )>
<!ELEMENT STUID (#PCDATA)>
<!ELEMENT STUNAME (#PCDATA)>
<!ELEMENT STUSEX (#PCDATA)>
<!ELEMENT STUBIRTH (#PCDATA)>
<!ELEMENT STUTEL (#PCDATA)>
<!ATTLIST STUDENT num CDATA #IMPLIED >';
output_file := UTL_FILE.FOPEN('TEST_DIR', 'XMLStudent.dtd', 'w');
UTL_FILE.put(output_file,xmlstr);
UTL_FILE.FCLOSE(output_file);
transDate(dir,filename,'db2xml_err.log');
execute immediate 'drop directory test_dir';
END DBToXml;
/
begin
dbtoxml('select * from student','e:\gao','student.xml');
end;
///////////////////////////
create or replace procedure transDate(dir varchar2, inpfile varchar2,
errfile varchar2) is
p xmlparser.parser;
doc xmldom.DOMDocument;
nl xmldom.DOMNodeList;
len number;
n xmldom.DOMNode;
nodeName varchar2(20);
DTDate date;
strDate varchar2(10);
begin
-- 获取所有元素
