xmltables 函数 excel xmltype函数

1 介绍

XML（eXtended Markup Language可扩展标记语言）是一种基于文本，用于结构化任何可标记文档的格式语言。它是一种轻便的，可扩展的，标准的且简学易懂的保存数据的语言。它起源于SGML，并可模拟为树型。正因为这样，Oracle提供了一些函数来使用这种实用的语言来在数据库中保存文档。因此，学习这些函数来使得保存的数据更加具有可读性是很必要的。
   通过使用内置XMLType类型，Oracle允许将XML文档存放在数据库中，为了加强对XML的支持，Oracle还提供了一些XML函数。

1 .1准备阶段

在介绍这些XML函数之前，请读者首先建立表XMLTABLE，并为其插入数据，SQL语句如下所示：

SQL>CREATE TABLE XMLTABLE OF XMLTYPE;
SQL>INSERT INTO xmltable VALUES(XMLTYPE ('<PurchaseOrder
  2  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3  xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd">
  4  <Reference>ADAMS-20011127121040988PST</Reference>
  5  <Actions>
  6  <Action>
  7  <User>SCOTT</User>
  8  <Date>2006-05-31</Date>
  9  <Reject/>
 10  <Requestor>Julie P. Adams</Requestor>
 11  <User>ADAMS</User>
 12  <CostCenter>R20</CostCenter>
 15  <ShippingInstructions>
 16  <name>Julie P. Adams</name>
 17  <address>Redwood Shores, CA 94065</address>
 18  <telephone>650 506 7300</telephone>
 19  </ShippingInstructions> 
 20  <SpecialInstructions>Ground</SpecialInstructions>
 21  <LineItems>
 22  <LineItem ItemNumber="1">
 23  <Description>The Ruling Class</Description>
 24  <Part Id="037429135020" UnitPrice="29.95" Quantity="3"/>
 25  </LineItem>
 30  <LineItem ItemNumber="3">
 31  <Description>8 1/2</Description>
 32  <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/>
 33  </LineItem>
 34  </LineItems>
 35  </PurchaseOrder>'));
SQL> COMMIT;

   在创建完XMLTABLE以后，我来介绍一些在SQL中的一些主要的XML函数。在每个函数的介绍中，我会解释一些比较重要的参数并且在每个介绍后面附加一个例子来帮助您对函数的理解。

2 函数描述

2.1 DEPTH（n）

 DEPTH（n）：该函数用于返回XML方案中UNDER_PATH路径所对应的相对层数，其中参数n用于指定相对层数。示例如下：

SQL>  SELECT PATH(1),DEPTH(2) FROM resource_view
    2  WHERE UNDER_PATH(res, '/sys/schema/OE', 1)=1
    3  AND UNDER_PATH(res, '/sys/schema/OE', 2)=1;
PATH(1)                                                  DEPTH(2)----------------------------                               --------
/www.oracle.com                                                1
/www.oracle.com/xwarehouses.xsd                      2

2.2 EXISTSNODE（XMLType_instance，Xpath_string）

 EXISTSNODE（XMLType_instance，Xpath_string）：该函数用于确定特定的XML节点的路径是否存在，返回0表示节点不存在，返回1表示节点存在。其中参数XMLType_instance用于指定XMLType实例，Xpath_string用于指定XML节点路径。示例如下：

SQL> SELECT existsnode(VALUE(p),'/PurchaseOrder/User') node
    2   FROM xmltable p;
          NODE           ------------
           1

2.3 EXTRACT（XMLType_instance，Xpath_string）

  EXTRACT（XMLType_instance，Xpath_string）：该函数用于返回XML节点路径下的相应内容。其中参数XMLType_instance用于指定XMLType实例，Xpath_string用于指定XML节点路径。示例如下：

SQL> SELECT extract(value (p),'/PurchaseOrder/User') content
    2   FROM xmltable p;
CONTENT--------------------------------------------------
<User>ADAMS</User>

2.4 EXTRACTVALUE（XMLType_instance,Xpath_string)

  EXTRACTVALUE（XMLType_instance,Xpath_string):该函数用于返回特定XML节点路径的数据。其中参数XMLType_instance用于指定XMLType实例，Xpath_string用于指定XML节点路径。示例如下：

SQL> SELECT extractvalue(value(p),'/PurchaseOrder/User')  data
    2    FROM xmltable  p;
DATA-------------------------------------------------
ADAMS

2.5 PATH（correction_integer)

  PATH（correction_integer)：该函数用于返回特定XML资源所对应的相对路径，参数correction_integer用于指定路径层数。示例如下：

SQL> SELECT PATH(1), DEPTH(2)    FROM  resource_view
    2   WHERE UNDER_PATH(res, '/sys/schemas/OE', 1)=1
    3   AND INDER_PATH(res,'/sys/schemas/OE',2)=1;
PATH(1)                                                   DEPTH(2)----------------------------                             --------
/www.oracle.com                                                1
/www.oracle.com/xwarehouses.xsd                      2

2.6 SYS_DBURIGEN({column|attribute})

  SYS_DBURIGEN({column|attribute})：该函数用于根据列或者属性生成类型为DBUrlType的URL。参数column用于指定列名，attribute用于指定对象属性名。示例如下：

SQL> SELECT sys_dburigen(ename) url    FROM emp WHERE deptno=10;
URL(URL, SPARE)--------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ENAME='CLARK'] /ENAME', NULL)
DBURITYPE('/PUBLIC/EMP/ROW[ENAME='KING'] /ENAME', NULL)
DBURITYPE('/PUBLIC/EMP/ROW[ENAME='MILLER'] /ENAME', NULL)

2.7 SYS_XMLAGG（expr[，fmt]）

  SYS_XMLAGG（expr[，fmt]）：该函数用于汇总所有XML文档，并生成一个XML文档。示例如下：

<SQL> SELECT SYS_XMLAGG(SYS_XMLGEN(ename))  xml_content
      2  FROM emp;
XML_CONTENT----------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ENAME>SMITH</ENAME>
<ENAME>ALLEN</ENAME>
<ENAME>WARD</ENAME>
<ENAME>JONES</ENAME>
<ENAME>MARTIN</ENAME>
<ENAME>BLAKE</ENAME>
<ENAME>CLARK</ENAME>
<ENAME>SCOTT</ENAME>
<ENAME>KING</ENAME>
<ENAME>TURNER</ENAME>
<ENAME>ADAMS</ENAME>
<ENAME>JAMES</ENAME>
<ENAME>FORD</ENAME>
<ENAME>MILLER</ENAME>
</ROWSET>

2.8 SYS_XMLGEN(expr[,fmt])

  SYS_XMLGEN(expr[,fmt])：该函数用于根据数据库表的行和列生成XMLType实例。参数expr用于指定列名，fmt用于指定格式。示例如下：
SQL> SELECT sys_xmlgen(ename) xml FROM emp WHERE deptno=10;

XML
-----------------------------------------------------
<?xml version="1.0"?>
<ENAME>CLARK</ENAME>
<?xml version="1.0"?>
<ENAME>MILLER</ENAME>

2.9 UPDATEXML（XMLType_instance,Xpath_string,value_expr)

  UPDATEXML（XMLType_instance,Xpath_string,value_expr)：该函数用于更新特定XMLType实例相应的节点路径的内容。其中参数XMLType_instance用于指定XMLType实例，Xpath_string用于指定XML节点路径，value_expr用于指定新值。示例如下：

SQL> UPDATE xmltable p SET p=UPDATEXML(value (p),
    2       '/PurchaseOrder/User/text() ','SCOTT');

2.10 XMLAGG（XMLType_instance[ORDER BY sort_list])

  XMLAGG（XMLType_instance[ORDER BY sort_list])：该函数用于汇总多个XML块，并生成XML文档。其中参数XMLType_instance用于指定XMLType实例，sort_list用于生成指定的排序方式。示例如下：

SQL> SELECT xmlagg(xmlelement("employee",ename||' '||sal))  xml
    2   FROM emp WHERE deptno=30;
XML
---------------------------------------------------
<employee>ALLEN  1600</employee> 
<employee>WARD  1250</employee>
<employee>MARTIN  1250</employee>
<employee>BLAKE  2850</employee>
<employee>TURNER  1500</employee>
<employee>JAMES  950</employee>

2.11 XMLCOLATTVAL（value_expr[,value_expr2],...)

  XMLCOLATTVAL（value_expr[,value_expr2],...)：该函数用于生成XML块，参数value_expr用于指定列名或者别名作为属性名。示例如下：

SQL> SELECT xmlelement ("emp",xmlcolattval (ename,sal))    xml
    2   FROM emp WHERE ename='SCOTT';
XML
--------------------------------------------------
<emp>
<column name="ENAME">SCOTT</column>
<column name="SAL">3000</column>
</emp>

2.12 XMLCONCAT（XMLType_instance1[,XMLType_instance2],...)

  XMLCONCAT（XMLType_instance1[,XMLType_instance2],...)：该函数用于连接多个XMLType实例，并生成新的XMLType实例。参数XMLType_instance用于指定XML实例。示例如下：

SQL> SELECT xmlconcat(xmlelement("ename",ename),
    2      xmlelement("sal",sal))  xml
    3  FROM emp WHERE deptno=10;
XML
-------------------------------------------
<ename>CLARK</ename>
<sal>2450</sal><ename>KING</ename>
<sal>5000</sal><ename>CLARK</ename>
<sal>1300</sal>

2.13 XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])

  XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])：该函数用于返回XMLType的实例。其中参数identifier用于指定元素名，参数xml_attribute_clause用于指定元素属性子句，参数value_expr用于指定元素值。示例如下：

SQL> select xmlelement ("DATE",sysdate)  from dual;
XMLELEMENT ("DATE",SYSDATE)
------------------------------------------
<DATE>28-DEC-03</DATE>
SQL> SELECT xmlelement("Emp",
    2      xmlattributes(empno AS "ID", ename))  Employee
    3   FROM emp WHERE deptno=10;
EMPLOYEE
---------------------------------------------
<Emp ID="7782" ENAME="CLARK"/>
<Emp ID="7839" ENAME="KING"/>
<Emp ID="7934" ENAME="MILLER"/>

2.14 XMLFOREST（value_expr1[,value_expr2],...)

  XMLFOREST（value_expr1[,value_expr2],...)：该函数用于返回XML块。示例如下：

SQL> SELECT xmlelement ("Employee",xmlforest(ename,sal))
    2    FROM emp WHERE empno=7788;
XMLELEMENT ("EMPLOYEE",XMLFOREST(ENAME,SAL))
------------------------------------------------
<Employee>
    <ENAME>SCOTT</ENAME>
    <SAL>3000</SAL>
</Employee>

2.15 XMLSEQUENCE（xmltype_instance)

  XMLSEQUENCE（xmltype_instance)：该函数用于返回XMLType实例中顶级节点以下的VARRAY元素。示例如下：

SQL> SELECT xmlsequence(extract(value(x),
    2      '/PurchaseOrder/LineItem/*'))  varray FROM xmltable x;
VARRAY
--------------------------------------------------
XMLSEQUENCETYPE(XMLTYPE(<LineItem ItemNumber="1">
    <Description>The Ruling Class</Description>
    <Part Id="715515012423" UnitPrice="29.95" Quantity="2"/>
</LineItem>
),  XMLTYPE(<LineItem ItemNumber="2">
  <Description>Diabolique</Description>
  <Part Id="037429135020" UnitPrice="29.95" Quantity="3"/>
</LineItem>
),  XMLTYPE(<LineItem ItemNumber="3">
  <Description>8 1/2</Description>
  <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/>
</LineItem>
))

2.16 XMLTRANSFORM（xmltype_instance,xsl_ss)

  XMLTRANSFORM（xmltype_instance,xsl_ss)：该函数用于将XMLType实例按照XSL样式进行转换，并生成新的XMLType实例。示例如下：

SQL> SELECT XMLTRANSFORM(w.warehouse-spec,x.coll).GetClobVal()
    2    FROM warehouse w,xsl_tab x
    3    WHERE w.warehouse_name='San Francisco';

 

结论

   我们再次介绍一下XML（可扩展标记语言）的优点：轻便，可扩展，标准，简学易懂。也就是因为这些原因，它被使用得非常广泛，当然也包括Oracle这个全世界影响最大的数据库工具制造厂家。通过支持XML函数，SQL变得更加强大。

   这些函数作为在Oracle中使用XML文档的参考。理解这些函数将有助于您更有效率的在数据库中保存文档。