oraclexml文件是什么文件,Oracle生成XML文件

更新时间:2023-05-09 20:51:23 阅读: 评论:0

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.

本文发布于:2023-05-09 20:51:23,感谢您对本站的认可!

本文链接:https://www.wtabcd.cn/fanwen/fan/82/567125.html

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。

标签:元素   函数   做为   内容   属性   整理   返回   结合
相关文章
留言与评论(共有 0 条评论)
   
验证码:
推荐文章
排行榜
Copyright ©2019-2022 Comsenz Inc.Powered by © 专利检索| 网站地图