oracle xml文件是什么文件,Oracle生成XML文件

最近在研究Oracle PLSQL中对于XML的系列操作。结合工作中使用的知识和参考资料整理出以下相关内容：

一 如何生成XML文件：

1、使用dbms_xmlquery和utl_file内置包(scott用户执行)

CREATE OR REPLACE DIRECTORY xml_dir AS 'd:\app\xml';

DROP SEQUENCE seq_filename;

CREATE SEQUENCE seq_filename

MINVALUE 10000

MAXVALUE 99999

INCREMENT BY 1

START WITH 10000

NOCYCLE;

DECLARE

v_filename Varchar2(50) := 'Empmsg'||to_char(seq_filename.nextval)||'.xml';

xml_str clob;

xml_fileutl_file.file_type;

offset number;

buffer varchar2(32767);

buffer_size number;

BEGIN

offset := 1;

buffer_size := 3000;

xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');

xml_str := dbms_xmlquery.getxml('select empno,

ename,

job,

mgr,

hiredate,

sal,

comm,

deptno

from emp');

while (offset < dbms_lob.getlength(xml_str)) loop

buffer := dbms_lob.substr(xml_str, buffer_size, offset);

utl_file.put(xml_file, buffer);

utl_file.fflush(xml_file);

offset := offset + buffer_size;

end loop;

utl_file.fclose(xml_file);

END;

2、使用XMLELEMENT系列内置函数返回xml(sys用户执行)

DECLARE

v_filename Varchar2(50) := 'Empmsg'||to_char(scott.seq_filename.nextval)||'.xml';

xml_str clob;

xml_fileutl_file.file_type;

offset number;

buffer varchar2(32767);

buffer_size number;

BEGIN

offset := 1;

buffer_size := 3000;

xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');

SELECT XMLElement("DEPARTMENT"

, XMLAttributes( department_id as "ID"

, department_name as "NAME"

)

, XMLElement("EMPLOYEES"

, (SELECT XMLAgg( XMLElement("EMPLOYEE"

, XMLForest(employee_id as "ID"

,first_name||' '||last_name as "NAME"

)

)

)

FROM hr.employees emp

WHERE emp.department_id = dept.department_id

)

)

).getclobval() INTO xml_str

FROM hr.departments dept

WHERE department_id = 20;

while (offset < dbms_lob.getlength(xml_str)) loop

buffer := dbms_lob.substr(xml_str, buffer_size, offset);

utl_file.put(xml_file, buffer);

utl_file.fflush(xml_file);

offset := offset + buffer_size;

end loop;

utl_file.fclose(xml_file);

END;

--XMLElement: 将一个关系值转换为XML元素的函数，格式为值

--XMLAttributes: 用于在SQL查询返回的 XML 元素中设置属性的函数

--XMLForest:       该函数返回一个或多个子元素的集合,该函数使用列名做为XML元素的名称并用SQL值表达式做为XML元素的内容，但使用时不能指定元素的属性

--XMLAgg:           在GROUP BY查询中对XML数据进行分组或汇总的函数

PS: 使用SPOOL方式导出文件：

SET TRIMSPOOL ON

SET TERMOUT ON

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET PAGESIZE 999

SET HEAD OFF

SET HEADING OFF

SET LONG 5000

spool c:\a.xml

SELECT XMLElement("DEPARTMENT"

, XMLAttributes( department_id as "ID"

, department_name as "NAME"

)

, XMLElement("EMPLOYEES"

, (SELECT XMLAgg( XMLElement("EMPLOYEE"

, XMLForest(employee_id as "ID"

,first_name||' '||last_name as "NAME"

)

)

)

FROM employees emp

WHERE emp.department_id = dept.department_id

)

)

) a

FROM departments dept

WHERE department_id = 10;

spool off

二 如何存储XML文件内容：

三 如何解析XML内容：

四 XMLTABLE用法：

to be continue...

----------------------------------

By    Dylan.