Oracle操作XML各种场景介绍(4)

四  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;