oraclexml⽂件是什么⽂件,Oracle⽣成XML⽂件最近在研究Oracle PLSQL中对于XML的系列操作。结合⼯作中使⽤的知识和参考资料整理出以下相关内容：
⼀ 如何⽣成XML⽂件：
1、使⽤dbms_xmlquery和utl_file内置包(scott⽤户执⾏)
CREATE OR REPLACE DIRECTORY xml_dir AS 'd:appxml';
DROP SEQUENCE q_filename;
CREATE SEQUENCE q_filename
MINVALUE 10000
MAXVALUE 99999
INCREMENT BY 1
START WITH 10000
NOCYCLE;
DECLARE
v_filename Varchar2(50) := 'Empmsg'||to_char(val)||'.xml';
xml_str clob;
xml_file utl_file.file_type;
offt number;
buffer varchar2(32767);
buffer_size number;
BEGIN
offt := 1;
buffer_size := 3000;
xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');
xml_str := l('lect empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
from emp');
while (offt < length(xml_str)) loop
buffer := dbms_lob.substr(xml_str, buffer_size, offt);
utl_file.put(xml_file, buffer);
utl_file.fflush(xml_file);
offt := offt + buffer_size;
end loop;
utl_file.fclo(xml_file);
END;
2、使⽤XMLELEMENT系列内置函数返回xml(sys⽤户执⾏)
DECLARE
v_filename Varchar2(50) := 'Empmsg'||to_char(scott.val)||'.xml'; xml_str clob;
xml_file utl_file.file_type;
offt number;
buffer varchar2(32767);
buffer_size number;
BEGIN
offt := 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"
)
)
)
ployees emp
WHERE emp.department_id = dept.department_id
)
)
).getclobval() INTO xml_str
FROM hr.departments dept
WHERE department_id = 20;
while (offt < length(xml_str)) loop
buffer := dbms_lob.substr(xml_str, buffer_size, offt);
utl_file.put(xml_file, buffer);
utl_file.fflush(xml_file);
offt := offt + buffer_size;
end loop;
utl_file.fclo(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 l
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
---------------------------------- By Dylan.
