Oracle操作XML各种场景介绍 -云博客

Oracle操作XML各种场景介绍

# 数据库 2024-05-05 20:34 0 49 来源：云博客

近期在研究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_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;

--XMLElement: 将一个关系值转换为XML元素的函数。格式为<elementName>值</elementName>?
--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 5000spool c:\a.xmlSELECT 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[P
ONO=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 purchaseorderWHERE 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‘) CostCenterFROM purchaseorderWHERE 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.