从Oracle数据访问和格式化XML

### 从 Oracle 数据访问和格式化 XML 在处理 Oracle 数据时，XML 是一种强大的数据交换和存储格式。本文将详细介绍如何从 Oracle 数据中访问和格式化 XML，包括处理多行数据、使用 XMLTYPE 数据类型、从 XML 文档创建关系数据等内容。 #### 1. 使用 XML DB 处理多行数据 在之前，我们已经了解了如何使用 `XMLFOREST()`、`SYS_XMLAGG()`、`XMLELEMENT()` 和 `XMLATTRIBUTES()` 函数处理单行数据。但处理多行结果集时，会面临一些挑战，因为每一行都需要在 XML 文档中进行定义，并且大多数情况下，XML 文档必须格式良好。 例如，若在之前的 `SYS_XMLAGG()` 示例中包含多行数据，所有元素都会处于同一嵌套级别，导致 XML 文档中无法清晰定义行结果的起始和结束，数据混乱。而之前的 `XMLELEMENT()` 和 `XMLATTRIBUTES()` 示例为每行返回一个带有 `RootElement` 的 XML 文档片段，但缺少 XML 文档根元素。 #### 2. 使用 XMLAGG() 聚合多行数据 `XMLAGG()` 函数可将多行数据聚合为单个 XML 文档。以下是具体示例： ```sql SELECT XMLELEMENT("RowElement", XMLFOREST( PRODUCTID, RANKING, TITLE)) as "RESULT" FROM AmazonListings ``` 此查询使用嵌套的 `XMLELEMENT()` 和 `XMLFOREST()` 函数为每行数据创建格式良好的 XML 文档片段，结果并非格式良好的 XML 文档，而是每行数据的文档片段集合： ```xml <RowElement> <PRODUCTID>1001</PRODUCTID> <RANKING>1</RANKING> <TITLE>Hamlet/MacBeth</TITLE> </RowElement> <RowElement> <PRODUCTID>1002</PRODUCTID> <RANKING>2</RANKING> <TITLE>MacBeth</TITLE> </RowElement> <RowElement> <PRODUCTID>1003</PRODUCTID> <RANKING>3</RANKING> <TITLE>William Shakespeare: MacBeth</TITLE> </RowElement> ``` 若要将多行结果聚合为单个 XML 文档，可使用如下查询： ```sql SELECT XMLELEMENT("RootElement", XMLAGG(XMLELEMENT("RowElement", XMLFOREST( PRODUCTID, RANKING, TITLE)))) as "RESULT" FROM AmazonListings ``` 结果是一个格式良好的 XML 文档，包含根元素和每行数据的定义，数据行是 `RowElement` 元素的子元素： ```xml <RootElement> <RowElement> <PRODUCTID>1001</PRODUCTID> <RANKING>1</RANKING> <TITLE>Hamlet/MacBeth</TITLE> </RowElement> <RowElement> <PRODUCTID>1002</PRODUCTID> <RANKING>2</RANKING> <TITLE>MacBeth</TITLE> </RowElement> <RowElement> <PRODUCTID>1003</PRODUCTID> <RANKING>3</RANKING> <TITLE>William Shakespeare: MacBeth</TITLE> </RowElement> </RootElement> ``` `XMLAGG()`、`XMLELEMENT()`、`XMLATTRIBUTES()`、`XMLFOREST()` 和 `XMLCOMMATTVAL()` 的组合是将 Oracle 关系数据表示为 XML 的最常用方法。 #### 3. 使用 XMLTYPE 数据类型 从 Oracle9i 开始，可使用 `XMLType` 数据类型在 Oracle 数据库中存储 XML 数据。在 Oracle9i 之前，使用 LOB 和文本将 XML 文档作为文本存储。从基础层面看，CLOB 和 XMLType 数据差异不大，但 `XMLType` API 中有一些有用的方法，可用于操作存储为 `XMLType` 数据类型的 XML 文档。 当通过 W3C 模式将关系数据表映射到 XML 文档数据时，表中的数据将以 `XMLType` 数据类型形式可用。常规表中的列也可以是 `XMLType` 数据类型，并且可以使用 `XMLType` 视图将关系数据伪装为 `XMLType` 数据。 ##### 3.1 将数据存入 XMLType 列 在 Oracle9i 及更高版本中，可向任何 Oracle 表添加 `XMLType` 列。以下示例创建了一个名为 `XMLONLY` 的表，该表包含一个名为 `XMLDOC` 的列，并将其数据类型指定为 `XMLTYPE`： ```sql CREATE TABLE XMLONLY (XMLDOC SYS.XMLTYPE); ``` 有了新的 `XMLONLY` 表后，可从任何源插入 `XMLType` 数据。以下示例使用嵌套的 `XMLELEMENT()` 和 `XMLFOREST()` 函数选择 `AMAZONLISTINGS` 表的前三列，将查询结果插入一个名为 `XMLTypeVal` 的变量（内存中的 `XMLType` 对象），然后将该 `XMLType` 对象插入 `XMLONLY` 表的 `XMLDOC` 列。 需要注意的是，`XMLType` 数据类型列仅接受格式良好的 XML 文档，不接受文档片段。例如，若尝试插入由 `XMLFOREST` 表达式产生的 XML 文档片段，Oracle 将返回 `ORA - 19010: Cannot insert XML fragments` 错误消息。添加 `XMLELEMENT()` 函数可在 `XMLFOREST` 结果周围包裹根元素，使插入操作能够被接受。 ```sql DECLARE XMLTYpeVal SYS.XMLTYPE; BEGIN SELECT XMLELEMENT("RootElement", XMLFOREST( PRODUCTID, RANKING, TITLE)) as "result" INTO XMLTYpeVal FROM AmazonListings WHERE rownum = 1; INSERT INTO XMLONLY (XMLDOC) VALUES (XMLTYpeVal); COMMIT; END; ``` ##### 3.2 使用 SQL 和 SYS_XMLGEN 处理 XMLType 列 在 `XMLONLY` 表的 `XMLDOC` 列中存储了 `XMLType` 数据后，可使用常规 SQL 函数提取数据。例如，以下简单查询将返回存储在 `XMLDOC` 中的 XML 文档： ```sql SELECT XMLDOC from XMLONLY ``` 查询结果如下： ```xml <RootElement> <PRODUCTID>1001</PRODUCTID> <RANKING>1</RANKING> <TITLE>Hamlet/MacBeth</TITLE> </RootElement> ``` `SYS_XMLGEN()` 是一个 Oracle SQL 函数，可将表的单个列作为 XML 元素返回，元素名称基于列名。该函数在处理常规数据时使用场景有限，但对 `XMLType` 列非常有用。 以下是 `SYS_XMLGEN()` 从常规表数据返回 XML 元素的示例： ```sql SELECT SYS_XMLGEN(PRODUCTID) from AMAZONLISTINGS where rownum=1 ``` 查询结果为： ```xml <PRODUCTID>1001</PRODUCTID> ``` 当对 `XMLType` 列应用相同查询时，也非常有用。以下示例查询 `XMLONLY` 表的 `XMLDOC` 列并返回单个 XML 文档： ```sql SELECT SYS_XMLGEN(XMLDOC) from XMLONLY where rownum=1 ``` `SYS_XMLGEN()` 将列名作为元素名包裹在结果周围： ```xml <XMLDOC> <RootElement> <PRODUCTID>1001</PRODUCTID> <RANKING>1</RANKING> <TITLE>Hamlet/MacBeth</TITLE> </RootElement> </XMLDOC> ``` 需要注意的是，XML 文档顶部没有 XML 文档声明，这是有意为之。因为 XML 声明是可选的，创建格式良好的 XML 文档结构仅需要根元素。不存储可选声明而在查询时创建声明更好，因为没有声明的 XML 文档可以合并成更大的 XML 文档。若将 XML 声明作为数据的一部分存储，会占用更多空间，并且更难将 XML 结果聚合为更大的文档。以下示例在之前示例的 `SYS_XMLGEN()` 结果前添加 XML 文档声明： ```sql SELECT '<?xml version="1.0"?>', SYS_XMLGEN(XMLDOC) from XMLONLY ``` 添加 XML 文档声明后的结果如下： ```xml <?xml version="1.0"?> <XMLDOC> <RootElement> <PRODUCTID>1001</PRODUCTID> <RANKING>1</RANKING> <TITLE>Hamlet/MacBeth</TITLE> </RootElement> </XMLDOC> ``` #### 4. 从 XML 文档创建关系数据 Oracle 企业管理器具备注册生成的模式并将 XML 文档数据映射到关系数据的功能。`XMLType` 视图也可用于将 XML 文档数据映射到关系数据，并作为遗留数据结构的 XML 接口，而无需更改表本身。 ##### 4.1 从 Oracle 数据库模式生成 W3C 模式 Oracle 可使用 XDK 函数、PL/SQL `DBMS_XMLSCHEMA.generateSchema()` 包或第三方工具（如 XMLSpy）从关系数据生成 W3C 模式。以下是使用 XMLSpy 为 `AMAZONLISTINGS` 表生成的 W3C 模式示例： ```xml <?xml version="1.0" encoding="UTF-8"?> <!-- edited with XMLSPY v5 rel. 4 U (http://www.xmlspy.com) by Brian Benz (Wiley) --> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="AMAZONLISTINGS"> <xs:complexType> <xs:sequence> <xs:element name="PRODUCTID" type="xs:decimal"/> <xs:element nam ```