Oracle操作XML各种场景介绍

昵称10087950 2016-08-09

展开全文

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

一如何生成XML文件：

1、使用dbms_xmlquery和utl_file内置包（scott用户执行）

[sql] view plain copy

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;

[sql] view plain copy

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用户执行）

[sql] view plain copy

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;

--XMLElement: 将一个关系值转换为XML元素的函数，格式为<elementName>值</elementName>
--XMLAttributes: 用于在SQL查询返回的 XML 元素中设置属性的函数
--XMLForest: 该函数返回一个或多个子元素的集合,该函数使用列名做为XML元素的名称并用SQL值表达式做为XML元素的内容，但使用时不能指定元素的属性
--XMLAgg: 在GROUP BY查询中对XML数据进行分组或汇总的函数

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

[sql] view plain copy

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类型的表中。

[sql] view plain copy

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内容：

[html] view plain copy

<PurchaseOrder xmlns:xsi="http://www./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；

[sql] view plain copy

SELECT existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference')
FROM purchaseorder;

--extractValue 提取XPath节点值

[sql] view plain copy

SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference') = 1;

--extract 提取XPath节点

[sql] view plain copy

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"
FROM purchaseorder;

4、使用SQL解析XML：

[sql] view plain copy

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内容返回虚拟关系型结构数据。下面说个简单例子：

[sql] view plain copy

CREATE TABLE warehouses(
warehouse_id NUMBER(3),
warehouse_spec SYS.XMLTYPE,
warehouse_name VARCHAR2(35),
location_id NUMBER(4)
);

[sql] view plain copy

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');