Oracle XML 处理详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle XML DB 提供完整 XML 支持[1]：

特性：

XMLType 数据类型
XQuery / XPath
XML 索引
XML 视图

2. 存储

2.1 XMLType

CREATE TABLE xml_docs (
  id NUMBER PRIMARY KEY,
  doc XMLType
);

2.2 二进制 XML

CREATE TABLE xml_docs (
  id NUMBER PRIMARY KEY,
  doc XMLType
) XMLTYPE doc STORE AS SECUREFILE BINARY XML;

2.3 CLOB

CREATE TABLE xml_docs (
  id NUMBER PRIMARY KEY,
  doc XMLType
) XMLTYPE doc STORE AS CLOB;

2.4 结构化（对象关系）

CREATE TABLE xml_docs (
  id NUMBER PRIMARY KEY,
  doc XMLType
) XMLTYPE doc STORE AS OBJECT RELATIONAL;

3. 插入

INSERT INTO xml_docs VALUES (
  1, 
  XMLType('<?xml version="1.0"?>
<employee>
  <id>100</id>
  <name>Alice</name>
  <salary>5000</salary>
  <department>IT</department>
</employee>')
);

-- 文件
INSERT INTO xml_docs VALUES (2, XMLType(BFILENAME('XML_DIR', 'emp.xml'), nls_charset_id('AL32UTF8')));

4. 查询

4.1 EXTRACTVALUE

SELECT EXTRACTVALUE(doc, '/employee/name') AS name,
       EXTRACTVALUE(doc, '/employee/salary') AS salary
FROM xml_docs;

4.2 EXTRACT

SELECT EXTRACT(doc, '/employee/department').getStringVal() AS dept
FROM xml_docs;

4.3 XMLQuery

SELECT XMLQuery('/employee/name/text()' PASSING doc RETURNING CONTENT) AS name
FROM xml_docs;

4.4 XMLTable

SELECT t.id, t.name, t.salary, t.dept
FROM xml_docs x,
  XMLTable('/employee' PASSING x.doc
    COLUMNS 
      id NUMBER PATH 'id',
      name VARCHAR2(50) PATH 'name',
      salary NUMBER PATH 'salary',
      dept VARCHAR2(20) PATH 'department'
  ) t;

5. XQuery

5.1 FLWOR

SELECT XMLQuery('
  for $e in /employee
  where $e/salary > 4000
  order by $e/name
  return <high>{data($e/name)}</high>
' PASSING doc RETURNING CONTENT) AS high_paid
FROM xml_docs;

5.2 嵌套

SELECT XMLQuery('
  <employees>{
    for $e in /employees/employee
    return <emp id="{data($e/id)}">{data($e/name)}</emp>
  }</employees>
' PASSING doc RETURNING CONTENT) FROM xml_docs;

6. 创建 XML

6.1 XMLElement

SELECT XMLElement("employee", 
  XMLElement("id", e.id),
  XMLElement("name", e.name),
  XMLElement("salary", e.salary)
) AS xml
FROM employees e WHERE e.id = 100;

6.2 XMLForest

SELECT XMLElement("employee", 
  XMLForest(e.id, e.name, e.salary, e.dept_id)
) AS xml
FROM employees e;

6.3 XMLAgg

SELECT XMLElement("employees", 
  XMLAgg(XMLElement("employee", XMLForest(e.id, e.name)))
) AS xml
FROM employees e;

6.4 XMLConcat

SELECT XMLConcat(
  XMLElement("name", name),
  XMLElement("salary", salary)
) AS xml
FROM employees;

7. 索引

7.1 函数索引

CREATE INDEX idx_xml_name ON xml_docs (EXTRACTVALUE(doc, '/employee/name'));

7.2 XMLIndex

CREATE INDEX idx_xml ON xml_docs (doc) INDEXTYPE IS XDB.XMLIndex;

7.3 结构化

CREATE INDEX idx_xml_struct ON xml_docs (doc) 
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATH TABLE path_tab');

8. XMLSchema

8.1 注册

BEGIN
  DBMS_XMLSCHEMA.REGISTERSCHEMA(
    schemaurl => 'http://example.com/emp.xsd',
    schemadoc => '<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="employee">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="id" type="xs:integer"/>
        <xs:element name="name" type="xs:string"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'
  );
END;
/

8.2 基于 Schema

CREATE TABLE xml_emp OF XMLType
  XMLSCHEMA "http://example.com/emp.xsd"
  ELEMENT "employee";

8.3 验证

INSERT INTO xml_emp VALUES (XMLType('...'));
-- 自动验证

9. XML DB Repository

9.1 WebDAV / HTTP

- 文件系统访问
- HTTP 协议
- WebDAV

9.2 资源

-- 创建
DECLARE
  v_res BOOLEAN;
BEGIN
  v_res := DBMS_XDB.createResource('/public/emp.xml', 
    XMLType('<employee><id>1</id></employee>'));
END;
/

-- 查询
SELECT path FROM resource_view WHERE under_path('/public') = 1;

-- 删除
DECLARE
  v_res BOOLEAN;
BEGIN
  v_res := DBMS_XDB.deleteResource('/public/emp.xml');
END;
/

10. 修改

10.1 UPDATEXML

UPDATE xml_docs 
SET doc = UPDATEXML(doc, '/employee/salary/text()', '6000')
WHERE id = 1;

10.2 XQuery Update（12c+）

UPDATE xml_docs SET doc = XMLQuery(
  'copy $d := $x modify 
    replace value of node $d/employee/salary with "6000"
   return $d'
  PASSING doc AS "x" RETURNING CONTENT
) WHERE id = 1;

10.3 INSERTCHILDXML

UPDATE xml_docs 
SET doc = INSERTCHILDXML(doc, '/employee', 'email', XMLType('<email>alice@example.com</email>'))
WHERE id = 1;

11. 应用场景

11.1 Web Service

- SOAP / REST
- 配置
- 接口

11.2 文档

- Office 文档
- 配置文件
- 数据交换

11.3 集成

- 异构系统
- ETL

12. 性能

12.1 二进制 XML

- 推荐
- 紧凑
- 解析快

12.2 索引

- XMLIndex
- 函数索引
- 结构化

12.3 查询

- XMLTable：多值
- XMLQuery：复杂
- EXISTSNODE：存在

13. 常见坑与排错

13.1 ORA-31011

- XML 解析错误
- 检查格式

13.2 ORA-19202

- XML 处理错误
- 详情

13.3 性能

- 索引
- 二进制 XML
- XQuery 优化

14. 最佳实践

二进制 XML 存储：性能
XMLIndex：查询
XMLSchema：验证
XMLTable：多值
XMLQuery：复杂
结构化存储：固定
CLOB：简单
XML DB Repository：文件
HTTP/WebDAV：访问
测试：验证

15. 参考资料

[1] Oracle XML DB Developer’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/adxdb/