当前位置： 首页 > news >正文

吉林省吉林市区号/青岛网站快速排名优化

news 2024-12-02 12:54
吉林省吉林市区号,青岛网站快速排名优化,在线网站创做简历,手机邮箱申请免费注册版权声明：凭栏处。潇潇雨歇。 https://blog.csdn.net/IndexMan/article/details/28130961 近期在研究Oracle PLSQL中对于XML的系列操作。结合工作中使用的知识和參考资料整理出以下相关内容： 一 怎样生成XML文件： 1、使用dbms_xmlquery和utl_…
版权声明：凭栏处。潇潇雨歇。 https://blog.csdn.net/IndexMan/article/details/28130961

近期在研究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 10000MAXVALUE 99999 INCREMENT BY 1 START WITH 10000 NOCYCLE;

DECLAREv_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;
BEGINoffset      := 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,deptnofrom emp');while (offset < dbms_lob.getlength(xml_str)) loopbuffer := 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用户运行）

DECLAREv_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;
BEGINoffset      := 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 empWHERE emp.department_id = dept.department_id))).getclobval() INTO xml_strFROM hr.departments deptWHERE department_id = 20;while (offset < dbms_lob.getlength(xml_str)) loopbuffer := 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 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 empWHERE emp.department_id = dept.department_id))) aFROM departments deptWHERE 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_columnVALUES ('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 ParkwayRedwood ShoresCA94065USA</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,CASEWHEN existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject') = 1THEN '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_specCOLUMNS "whNo" varchar2(100) PATH '@whNo')warehouse2;

SELECT warehouse_name warehouse,warehouse2."Water", warehouse2."Rail", warehouse2.fieldFROM warehouses,XMLTABLE('*//opt1'PASSING warehouses.warehouse_specCOLUMNS "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.

转载于:https://www.cnblogs.com/ldxsuanfa/p/10472848.html

相关文章：

  • 广东在线网站建设/如何制作简易网站