将XML存入关系数据库

介绍

解决把 XML 有效的、自动的转换出入关系数据库的问题有各种方式。数据库厂商比如 IBM、Microsoft、Oracle 和 Sybase 已经开发了转换 XML 到数据库表中的辅助工具。各种解决方案如下。


Oracle XML SQL Utility 把 XML 文档元素建模为一组嵌套的表。通过使用 Oracle 对象数据类型建模套入的元素。"SQL-to-XML"转换使用被对象数据类型引用的表，和嵌套的元素之间的一到一关联来构造 XML 文档。"XML-to-SQL"可能要求数据模型的改进(从关系转换到对象-关系)或重新构造最初的 XML 文档。
IBM DB2 XML Extender 允许存储 XML 文档为 BLOB 式的对象或分解到一组表中。后者得变换叫做 XML 收集，以 XML 1.0 语法定义。
Microsoft 通过扩展 SQL-92 并介入 OPENXML 行集来解决问题。
Sybase Adaptive Server 介入 ResultSetXml Java 类作为在两个方向上处理 XML 文档的基础。


在这篇文章中，我们将详细讨论这些厂商的解决方案。此后，我们将尝试回答下列问题：


我们可以调整并简化问题吗?
在异构数据库环境中正确的途径是什么？
我将使用下列词汇表作为一个例子。


<!-- 简单类型 -->

<!ELEMENT CURRENCY1 (#PCDATA)>
<!ATTLIST CURRENCY1 e-dtype NMTOKEN #FIXED "string"
e-dsize NMTOKEN #FIXED "3">

<!ELEMENT CURRENCY2 (#PCDATA)>
<!ATTLIST CURRENCY2 e-dtype NMTOKEN #FIXED "string"
e-dsize NMTOKEN #FIXED "3">

<!ELEMENT AMOUNT (#PCDATA)>
<!ATTLIST AMOUNT e-dtype NMTOKEN #FIXED "decimal">

<!ELEMENT SETTLEMENT (#PCDATA)>
<!ATTLIST SETTLEMENT e-dtype NMTOKEN #FIXED "date">

<!ELEMENT BANKCODE (#PCDATA)>
<!ATTLIST BANKCODE e-dtype NMTOKEN #FIXED "string">

<!ELEMENT BANKACCT (#PCDATA)>
<!ATTLIST BANKACCT e-dtype NMTOKEN #FIXED "string">

<!-- 派生类型 -->

<!ELEMENT ACCOUNT (BANKCODE, BANKACCT)>

<!ELEMENT FXTRADE (CURRENCY1, CURRENCY2, AMOUNT, SETTLEMENT, ACCOUNT)>






Oracle XML-SQL Utility (XSU)

SQL 到 XML 的映射
Oracle 把对象引用链从数据库转换到 XML 文档的层次结构中。在对象-关系数据库，在表 FXTRADE 中的字段 ACCOUNT 被建模为类型 Aclearcase/" target="_blank" >ccountType 的一个对象引用:

CREATE TABLE FXTRADE
{
CURRENCY1 CHAR (3),
CURRENCY2 CHAR (3),
AMOUNT NUMERIC (18,2),
SETTLEMENT DATE,
ACCOUNT AccountType // 对象引用
}

CREATE TYPE AccountType as OBJECT
{
BANKCODE VARCHAR (100),
BANKACCT VARCHAR (100)
}


从给定的对象-关系模型生成相应的 XML 文档(使用 "SELECT * FROM FXTRADE")如下

<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<CURRENCY1>GBP</CURRENCY1>
<CURRENCY2>JPY</CURRENCY2>
<AMOUNT>10000</AMOUNT>
<SETTLEMENT>20010325</SETTLEMENT>
<ACCOUNT>
<BANKCODE>812</BANKCODE>
<BANKACCT>00365888</BANKACCT>
</ACCOUNT>
</ROW>
<!-- additional rows ... -->
</ROWSET>


从数据库提取 XML
下面的例子取自 Oracle 的 XSU 文档，带有 SQL 语句的适当替换并使用 Oracle 的纯 Java JDBC 薄驱动程序。

首先，建立了 OracleXMLQuery 的一个实例，其后，执行一个查询，并把结果表示为上面的 XML 文档的形式。类似的，可以提取 XML 文档到 DOM 的形式；在这种情况下，可以调用 qry.getXMLDOM() 取代 getXMLString()。

import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;

// 测试 XML 文档生成为 String 的类
class testXMLSQL {

public static void main(String[] args)
{
try {
// 建立连接
Connection conn = getConnection("scott", "tiger");

// 建立查询类
OracleXMLQuery qry = new OracleXMLQuery(conn,
"SELECT * FROM FXTRADE");

// 得到 XML 字符串
String str = qry.getXMLString();

// 打印 XML 输出
System.out.println("The XML output is:\n" + str);

// 总是关闭查询来释放所有资源
qry.close();
} catch(SQLException e) {
System.out.println(e.toString());
}
}

// 得到给定的用户名字和口令的连接
private static Connection getConnection(String username,
String password)
throws SQLException
{
// 注册 JDBC 驱动程序
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

// 建立 OCI8 驱动程序的连接
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@dlsun489:1521:ORCL",
username, password);

return conn;
}
}


存储 XML 在数据库中
在例子中使用 OracleXMLSave 来存储我们的 XML 文档到对象关系模型中；insertXML 方法进行实际的数据插入。

import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testXMLInsert
{
public static void main(String args[])
throws SQLException
{
Connection conn = getConnection("scott", "tiger");
OracleXMLSave sav = new OracleXMLSave(conn, "scott. FXTRADE");
// Assume that the user passes in this document as 0-arg
sav.insertXML(args[0]);
sav.close();
}

...
}


如果 XML 和在数据库中的对象-关系模型是同步的则一切正常，但是如果不同呢? 在这种情况下你有两种选择。

调整对象-关系模型 -- 可以构造一个可修改的对象-关系视图来完成多表修改；或者，作为替代，使用 XSLT，可以把 XML 文档分解成一组"平坦的"子文档。XSU 不允许属性值的存储；它建议你把属性转换成元素。

Oracle XSU 的总结
通过如下对象-关系模型构造规则建模 XML 到 SQL 映射: 把每个嵌套的 XML 元素映射在适当的类型的一个对象引用上。映射规则被暗含的嵌入到数据库模型中。

Java API 由类 OracleXMLQuery 和 OracleXMLSave 组成。




IBM DB2 XML Extender

SQL 到 XML 的映射
IBM 的 XML Extender 为使用 DB2 作为 XML 仓库提供两种访问和存储方法:


XML 列: 存储和取回整个 XML 文档为 DB2 列数据
XML 收集: 把 XML 文档分解成一组关系表，或从一组关系表合成 XML 文档。


DTD 存储在 DTD 仓库中，叫做 DTD_REF 的一个 DB2 表中；它的模式名字是"db2xml"。在 DTD_REF 表中每个 DTD 都有一个唯一的 ID。在数据库表和 XML 文档的结构之间的映射是通过数据访问定义(DAD)文件的方式来定义的。DAD 引用一个处理过的文档 DTD，从而提供在 XML 文档、它的 DTD 和在数据库表之上的映射规则之间的桥梁。

下面是一个例子 DAD。

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "dad.dtd">
<DAD>
<dtdid>FXTRADE.DTD</dtdid>
<validation>YES</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE FXTRADE FXTRADE.DTD </doctype>
<root_node>
<element_node name="FXTRADE">
<RDB_node>
<table name="FXTRADE"/>
<table name="ACCOUNT" key="ID"/>
<condition>
FXTRADE.ACCOUNT=ACCOUNT.ID
</condition>
</RDB_node>
<element_node name="CURRENCY1">
<text_node>
<RDB_node>
<table name="FXTRADE"/>
<column name="CURRENCY1" type="CHAR(3)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="CURRENCY2">
<text_node>
<RDB_node>
<table name="FXTRADE"/>
<column name="CURRENCY2" type="CHAR(3)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="AMOUNT">
<text_node>
<RDB_node>
<table name="FXTRADE"/>
<column name="AMOUNT" type="DECIMAL(18,2)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="SETTLEMENT">
<text_node>
<RDB_node>
<table name="FXTRADE"/>
<column name="SETTLEMENT" type="DATE"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="ACCOUNT">
<element_node name="BANKCODE">
<text_node>
<RDB_node>
<table name="ACCOUNT"/>
<column name="BANKCODE" type="VARCHAR(100)"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="BANKACCT">
<text_node>
<RDB_node>
<table name="ACCOUNT"/>
<column name="BANKACCT" type="VARCHAR(100)"/>
</RDB_node>
</text_node>
</element_node>
</element_node> <!--end of Account element-->
</element_node> <!-- end of FxTrade element -->
</root_node>
</Xcollection>
</DAD>


DAD 通过使用 element_node 到 RDB_node 关联来定义在 XML 文档和关系数据库列之间的映射。顶层的 element_node FXTRADE 被定义为在表 FXTRADE 和 ACCOUNT 之间的连接，带有在 ACCOUNT 表中的字段 ID 作为主键。子元素 CURRENCY1 被映射到在表 FXTRADE 中的字段 CURRENCY1 上，以此类推。注意 ACCOUNT 元素不包含任何 RDB_node -- 这是不需要的，原因是在前面已经定义了 ACCOUNT 和 FXTRADE 之间的连接。ACCOUNT、BANCCODE 和 BANKACCT 的子元素分别的定义在 ACCOUNT 表中的对应的列中。原子 XML 元素在 DAD 中标记为 text_node。在上面的例子中，除了 FXTRADE 和 ACCOUNT 之外的所有元素都是原子的。

从数据库提取 XML
通过存储过程来处理 XML 文档的合成和分解: 存储过程 dxxGenXML() 从数据库提取 XML 文档；存储过程 dxxShredXML() 存储 XML 文档到数据库中。

dxxGenXML() 的主要输入参数是


DAD: 以 DB2 CLOB 数据类型的形式存储；
结果表名字: 构造的 XML 文档被转发到这个表。


其他输入参数指定返回行的最大数目，和 <RDB_node> <condition> 元素摒弃(override)选项。输出参数包括返回行的实际数目，返回代码，和返回消息。

在 "IBM DB2 Universal Database XML Extender Administration and Programming, Version 7" 中的一个 C 程序内、可以找到的存储过程的一个详尽的例子。

存储 XML 在数据库中
把 XML 文档放入数据库中是通过存储过程 dxxShredXML() 来完成的。

dxxShredXML() 的输入参数是


DAD: 以 DB2 CLOB 数据类型的形式存储；
输入 XML 文档: 以 DB2 XMLCLOB 数据类型的形式存储。


它的输出参数是一个返回代码和返回消息。

总结
XML-SQL 映射规则通过数据访问定义(DAD)文件的方式来指定，它是一个 XML 文档。DB2 XML Extender 管理设施包括为每个持久的 DTD 构造 DAD 文件的一种手段。

进一步增强将包含新的 XML-SQL 转换语法，它将使用 XML 转换语言，是 W3C XSLT 的一个子集。




Microsoft SQL Server 2000

SQL 到 XML 的映射
SQL Server 的 SQL-to-XML 和 XML-to-SQL 映射规则使用不同的语法。在下面的提取和存储段落中讨论映射的详情。

从数据库提取 XML
在数据库列和 XML 元素或属性之间的映射通过在 SELECT 中的 AS 别名的方式来定义:

<数据库列> AS [元素名字! 嵌套级别! 属性名字! 指示]

同下面一样，文档顶层被赋予级别 1。缺省的，映射列数据在属性值上。可以使用指示"element"来改变这个缺省设置。

从数据库生成 XML 的过程有两步。

步骤 1。建立到你希望输出 XML 中的原子元素的 As-别名；别名定义了在元素之间的父/子联系。下面的表展示给我们的例子文档的别名。

FXTRADE /* LEVEL=1 */
CURRENCY1 [FXTRADE!1!CURRENCY1]
CURRENCY2 [FXTRADE!1!CURRENCY2]
AMOUNT [FXTRADE!1!AMOUNT]
SETTLEMENT [FXTRADE!1!SETTLEMENT]
ACCOUNT /* LEVEL=2 */
BANKCODE [ACCOUNT!2!BANKCODE]
BANKACCT [ACCOUNT!2!BANKACCT]


步骤 2。在 SQL 中定义输出树结构。通过 SELECT 语句定义树的每个级别，此后通过 UNION ALL 语句的手段把各个级别组合在一起到树中。级别-1 SELECT 语句介入在所有级别上的原子元素名字。每个 SELECT 语句介入一个树级别标记和它的父标记。在结果集中有一个单一记录对应于树根，如同在下面的第一个 SELECT 语句中定义的那样。

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [FXTRADE!1!CURRENCY1],
NULL AS [FXTRADE!1!CURRENCY2],
NULL AS [FXTRADE!1!AMOUNT],
NULL AS [FXTRADE!1!SETTLEMENT],
NULL AS [ACCOUNT!2!BANKCODE],
NULL AS [ACCOUNT!2!BANKACCT]
FROM
FXTRADE
UNION ALL
SELECT
2,
1,
FXTRADE.CURRENCY1,
FXTRADE.CURRENCY2,
FXTRADE.AMOUNT,
FXTRADE.SETTLEMENT,
ACCOUNT.BANKCODE,
ACCOUNT.BANKACCT
FROM
FXTRADE, ACCOUNT
WHERE
FXTRADE.ACCOUNT = ACCOUNT.ID
ORDER BY [ACCOUNT!2!BANKCODE],
[ACCOUNT!2!BANKACCT]
FOR XML EXPLICIT, ELEMENTS


FOR XML 通过分析在组合的行集中的标记和AS-别名构造 XML 文档。关键字 EXPLICIT 选择构造 XML 文档的最灵活的、用户定义的模式。另一个模式 AUTO 通过应用缺省规则构造 XML 文档。关键字 ELEMENTS 在元素级别建模 SQL 列；否则，缺省的是在属性级别建模 SQL 列。

存储 XML 在数据库中
使用 OPENXML 存储 XML 文档，它是一个新的行集函数，类似于表或视图。可以使用 OPENXML 来插入或更新或 SELECT INTO 目标表。OPENXML 简化的语法展示如下:

OPENXML (<XML 文档句柄>, <路径 pattern>, <标志>)
WITH (模式 | 表)
存储 XML 文档的过程分三步。


使用存储过程 sp_xml_preparedocument，通过把 XML 文档编译成内部 DOM 表示来获取一个 XML 文档句柄。
通过对模式字段关联上原子 XML 元素来构造一个模式。 通过路径 pattern(绝对基础路径)加上相对元素路径来定义 XML 元素。通过标志值 2 指示以元素为中心的映射。可以使用现存的表替代一个模式，带有等价于 XML 名字的字段名字。
使用存储过程 sp_xml_removedocument 从内存中删除编译过的 XML 文档。


在下列例子中展示这些步骤。

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =''
<FXTRADE>
<CURRENCY1>GBP</CURRENCY1>
<CURRENCY2>JPY</CURRENCY2>
<AMOUNT>10000</AMOUNT>
<SETTLEMENT>20010325</SETTLEMENT>
<ACCOUNT>
<BANKCODE>812</BANKCODE>
<BANKACCT>00365888</BANKACCT>
</ACCOUNT>
</FXTRADE>''
-- 建立 XML 文档的内部 DOM 表示。
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- 执行使用 OPENXML 行集提供者的一个 SELECT 语句。
SELECT *
FROM OPENXML (@idoc, ''/FXTRADE/ACCOUNT'', 2)
WITH (
CURRENCY1 CHAR (3), ''../@CURRENCY1'',
CURRENCY2 CHAR (3), ''../@CURRENCY2'',
AMOUNT NUMERIC (18,2), ''../@AMOUNT'',
SETTLEMENT DATETIME, ''../@SETTLEMENT'',
BANKCODE VARCHAR (100), ''@BANKCODE'',
BANKACCT VARCHAR (100), ''@BANKACCT'' )
EXEC sp_xml_removedocument @idoc


总结
对于 Microsoft SQL Server 2000，XML 文档的提取和存档不使用对称的语法。提取通过使用 FOR XML 构造扩展出一个 SELECT-子句。存储介入一个行集函数 OPENXML，类比于一个表或视图。提取映射规则是基于 (a)介入指定树级别用的标记和 (b) 对表的字段关联上在 XML 文档元素之间的父/子联系。存储把 XML 文档重构到一个平坦的模式或表中；使用 XPath 表示法定义 "字段-元素"关联。




Sybase Adaptive Server

SQL 到 XML 的映射
Sybase 使用一个 XML 文档类型 ResultSet 来描述 XML 文档元数据(元素名字、类型、大小等)和实际的行数据二者。下面摘录自假定的 FxTradeSet.xml:

<?xml version="1.0"?>
<!DOCTYPE ResultSet SYSTEM "ResultSet.dtd">
<ResultSet>
<ResultSetMetaData>
<ColumnMetaData
...
getColumnLabel="CURRENCY1"
getColumnName="CURRENCY1"
getColumnType="12"
... />
...
</ResultSetMetaData>
<ResultSetData>
<Row>
<Column name="CURRENCY1">GBP</Column>
...
</Row>
</ResultSetData>
</ResultSet>


ResultSet DTD 好像不允许嵌套元素的定义。

从数据库提取 XML
Java 类 ResultSetXml 有一个构造器，它接受一个 SQL 查询作为参数，此后 getXmlLText 方法从结果集提取个 XML 文档:

jcs.xml.resultset.ResultSetXml rsx = new jcs.xml.resultset.ResultSetXml
("Select * from FxTrade", <other parameters>);
FileUtil.string2File ("FxTradeSet.xml", rsx.getXmlText());


存储 XML 在数据库中
ResultSetXml 类构造器也接受一个 XML 文档作为参数。此后方法 toSqlScript 生成 SQL 语句的序列来从结果集插入/更新到指定的表。

String xmlString = FileUtil.file2string ("FxTradeSet.xml");
jcs.xml.resultset.ResultSetXml rsx = new jcs.xml.resultset.ResultSetXml
(xmlString);
String sqlString = rsx.toSqlScript ("FxTrade", <other parameters>)


总结
XML 文档的提取和存储是本质上对称的。存储好像不允许修改多于一个表。提取把 SQL 查询的结果转换到有平坦结构的文档中。




厂商比较

厂商 映射规则 单一表/多个表 转化的手段 对称的提取/存储
Oracle 隐含的；通过构造对象-关系模型 多个 指定的 Java 类 对称，如果 XML 文档和对象-关系模型匹配
IBM 数据访问定义文件 多个 指定的存储过程 对称
Microsoft SQL 扩展；行集函数 多个表用于提取；单一表用于存储 通过使用 SQL 构造FOR XML和行集OPENXML 不对称
Sybase 结果集 DTD 单一表；查询可以包含多个表 通过使用 Java 类 对称


厂商间的公共特征是:


XML 持久性建立在特别的基础上，就是说，没有一般性的设施来存储任意 XML 文档)；如果 XML 文档使用一个新的文法则需要专门的映射；
存储经常需要数据处理，比如按照使用的地域来重新格式化数值/日期；可以利用 XSLT 来进行 XML 数据整理。





一种可替代的策略

XML 文档存储在数据库中的任务可以划分到阶段? 首先，存储 XML 在普通的数据库结构中而不应用任何映射规则；其次，为后续的处理提供一个框架? 这种方法的好处是对于事实上的任何 XML 文档有一个通用的采集代理(acquisition agent)。本文的余下部分只提议这种解决方式。

在这种策略下，XML 文档保存为正常的树结构——原始树数据——用于进一步的应用处理。进一步数据处理的框架是一组数据库对象(存储过程)，类似于 DOM API，用来通过传统的 SQL 处理来操纵数据。

使用与全自动方式相反的框架方式的好处有: 数据库群体(population)通常由分布在"纯"应用程序代码、事务处理、数据库层(layer)和存储过程之间的应用逻辑来控制的，不必提及一些表可能驻留在远端数据库中。

此外，在异构的数据库环境中，拥有一个统一的工具来对不同的数据库完成相同的任务有着明显的好处。

作者的解决方案，ObjectCentric Solutions x-Persistence Utility，实现了这里讨论的策略。

原始树结构通过如下表来定义。

1) 树节点定义

CREATE TABLE NodeDef (
nodeID udd_id NOT NULL, // 唯一性节点 id
dimID udd_id NOT NULL, // 树种类: XML
name udd_name NOT NULL, // 节点名字
value udd_paramvalue NULL, // 节点值
value_type udd_type NULL, // 值类型
power udd_power NULL, // 节点树级别
isRoot udd_logical NOT NULL, // 顶层节点标志
descriptor udd_descriptor NULL, // DOM 节点类型
color udd_color NULL // 非正式数据
)


2) 在节点间的父-子关系

CREATE TABLE NodeLinks (
parentID udd_id NOT NULL, // 父节点
childID udd_id NOT NULL // 子节点
)





用例

存储 XML 文档在数据库中需要调用 XML2SQL 程序:

XML2SQL <XML 文档文件名>

使用下列存储过程实现从数据库提取 XML 文档为一个树结构:


get_tree_roots <树种类> -- 提取一个给定森林的所有文档根 id
get_tree <根 id> -- 提取一个给定根(文档)的所有节点
get_children <节点, 级别> -- 提取一个给定节点的特定级别的所有子节点


实现细节:


当前的平台包括: Sybase，MS SQL Server。
被评估的平台: Oracle，DB2 和 PostgreSQL。
实用工具建立在 Xerces XML 分析器顶上。
数据库安装涉及到的只是增加一些表，和导航数据库表为树结构的存储过程。


x-Persistence Utility 当前在 Open Finance Laboratory 中用作基于 XML 的数据采集代理的核心，它是如证券管理、负债管理、风险管理等范围内的财务应用的一个可定制的集成平台。Open Finance Laboratory 接受 FpML 作为用于利率导出的一个叙述性语言。x-Persistence Utility 目前用作 FpML/FRA 数据采集代理。

原文转自：http://www.ltesting.net