首页 / XML / Oracle操作XML各种场景介绍
Oracle操作XML各种场景介绍
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle操作XML各种场景介绍，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7402字，纯文字阅读大概需要11分钟。
内容图文
一 怎样生成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_file utl_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_file utl_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;--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文件内容：
我们知道oracle 中xmltype数据类型用来存储XML内容。以下样例中介绍怎样将系统中XML文件内容载入至
含有XMLTYPE类型的表中。CREATE TABLE xml_table OF XMLTYPE;
INSERT INTO xml_table
VALUES(XMLTYPE(bfilename(‘XML_DIR‘,‘PurchaseOrder.xml‘),nls_charset_id(‘AL32UTF8‘)));
SELECT x.sys_nc_rowinfo$.getstringval() FROM xml_table x;
CREATE TABLE table_with_xml_column(filename VARCHAR2(64), xml_document XMLTYPE);
INSERT INTO table_with_xml_column
VALUES (‘PurchaseOrder.xml‘,XMLType(bfilename(‘XML_DIR‘, ‘PurchaseOrder.xml‘),nls_charset_id(‘AL32UTF8‘)));
SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;
PurchaseOrder.xml内容：
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>
三 怎样解析XML内容：
?1、XPath结构介绍：
? ?? ? ? ? ? ?/ ? ? ? 表示树根。比如：/PO 指向树根的子节点<PO>; 通常也作为路径分隔符使用，比如：/A/B
? ? ? ? // 表示当前节点下全部子节点；比如：/A//B 匹配A节点下全部B节点 ? ? ? ? * ? ? ? 作为通配符使用，匹配全部子节点； 如：/A/*/C 匹配A节点下全部子节点C ? ? ? ? [ ] ? ? ?表示预期表达式；XPath支持丰富的操作符如OR、AND、NOT等；比如：/PO[PONO=20 AND PNAME="PO_2"]/SHIPADDR 匹配全部订单号为20而且订单名为PO_2的送货地址 @?用来提取节点属性 ? ? ? ? FunctionsXPath支持一些内置函数如：substring(), round() 和 not().?? ? ? 2、使用XMLTYPE方法查询XML：
? ? ? ? ? ??SELECT x.OBJECT_VALUE.getCLOBVal() FROM xml_table x;
? ? ? ? ? ? ?SELECT x.OBJECT_VALUE.getSTRINGVal() FROM xml_table x;
? ? ? 3、使用函数解析XML：
--existsNode 推断XPath中节点是否存在，存在返回值1 不存在返回0；
SELECT existsNode(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘)
FROM purchaseorder;
--extractValue ?提取XPath节点值
SELECT extractValue(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘)
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘) = 1;
--extract ? 提取XPath节点
SELECT extract(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘) "REFERENCE"
FROM purchaseorder;4、使用SQL解析XML：
SELECT extractValue(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘) REFERENCE,
extractValue(OBJECT_VALUE, ‘/PurchaseOrder/*//User‘) USERID,
CASE
WHEN existsNode(OBJECT_VALUE, ‘/PurchaseOrder/Reject‘) = 1
THEN ‘Rejected‘
ELSE ‘Accepted‘
END "STATUS",
extractValue(OBJECT_VALUE, ‘//CostCenter‘) CostCenter
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,‘//Reject‘) = 1;四 ? XMLTABLE使用方法：
XMLTable?maps the result of an XQuery evaluation into
relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.
说白了就是解析XML内容返回虚拟关系型结构数据。 以下说个简单样例：
? ? ? ??
? ? ? ??CREATE TABLE warehouses(
warehouse_id NUMBER(3),
warehouse_spec SYS.XMLTYPE,
warehouse_name VARCHAR2(35),
location_id NUMBER(4)
);INSERT into warehouses (warehouse_id, warehouse_spec,warehouse_name) VALUES (100, sys.XMLType.createXML(
‘<Warehouse whNo="100">
<opt1>
<Building>Owned</Building>
<WaterAccess>WaterAccess</WaterAccess>
<RailAccess>RailAccess</RailAccess>
<field>f1</field>
<field>f2</field>
<field>f3</field>
</opt1>
<opt2>
<name>Dylan</name>
</opt2>
</Warehouse>‘),‘Warehouse-X‘); SELECT warehouse_name warehouse,
warehouse2."whNo"
FROM warehouses,
XMLTABLE(‘/Warehouse‘
PASSING warehouses.warehouse_spec
COLUMNS
"whNo" varchar2(100) PATH ‘@whNo‘)
warehouse2;SELECT warehouse_name warehouse,
warehouse2."Water", warehouse2."Rail", warehouse2.field
FROM warehouses,
XMLTABLE(‘*//opt1‘
PASSING warehouses.warehouse_spec
COLUMNS
"Water" varchar2(100) PATH ‘//WaterAccess‘,
"Rail" varchar2(100) PATH ‘//RailAccess‘,
field XMLTYPE PATH ‘/‘)
warehouse2;? ? 具体文章链接：?http://viralpatel.net/blogs/oracle-xmltable-tutorial/
to be continue...
---------------------------------- By ? ?Dylan.
内容总结
以上是互联网集市为您收集整理的Oracle操作XML各种场景介绍全部内容，希望文章能够帮你解决Oracle操作XML各种场景介绍所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】