以SQL输入、XML输出

技术 XML 以SQL输入、XML输出 作者：Jonathan Gennick 新的SQL/XML函数在关系数据和XML之间架起了一个基于标准的桥梁。 几十年来，各企业都一直将它们的数据存放在关系数据库中。但是，如果要与另一个企业交换数据，就很可能需要把数据从关系数据库中取出，然后在将其传送给业务合作伙伴之前将这些数据格式化为XML。 SQL/XML标准 Oracle9i数据库实现了许多基于标准的函数，使你能够查询关系数据并且返回XML文档。这些函数都被归入SQL/XML，有时也称为SQLX。现在，SQL/XML正在成为ANSI/ISO SQL标准的一个新兴部分（确切地说是第14部分），并将于今年下半年作为ISO/IEC标准发布。SQL/XML标准拥有广泛的行业支持；主要的数据库供应商都在努力定义SQL/XML标准，包括IBM、微软、Oracle以及Sybase等。 SQL/XML的最终国际标准草案定义了以下元素： XML: 保存XML数据的数据类型 XMLAgg: 在GROUP BY查询中对XML数据进行分组或汇总的函数 XMLAttributes: 用于在SQL查询返回的 XML 元素中设置属性的函数 XMLConcat: 连接两个或多个XML值的函数 XMLElement: 将一个关系值转换为XML元素的函数，格式为<elementName>值</elementName> XMLForest: 从关系值列表生成一个称为"森林"的XML元素列表的函数 XMLNamespaces: 在XML元素中声明名称空间的函数 XMLSerialize: 将XML值串行化为一个字符串的函数 在这个列表中，Oracle9i数据库实现了以下函数：XML数据类型（即XMLType）、XMLAgg、XMLConcat、XMLElement以及XMLForest。在以后的版本中将对其余的函数提供支持。 除了函数和数据类型，SQL/XML标准还定义了将列名称转换为XML元素名称以及将SQL数据类型转换为XML数据类型的规则。XMLElement和其他SQL/XML函数自动应用这些规则。 创建XML文档 假设你在旅游局工作，有一个以关系方式存储的旅游胜地清单，如下表所示： SQL> describe COUNTY Name Null? Type -------------- ----- -------------- COUNTY_NAME VARCHAR2(10) STATE VARCHAR2(2) SQL> describe ATTRACTION Name Null? Type -------------- ----- -------------- COUNTY_NAME VARCHAR2(10) ATTRACTION_NAME VARCHAR2(30) ATTRACTION_URL VARCHAR2(40) GOVERNMENT_OWNED CHAR(1) LOCATION VARCHAR2(20) 现在要求你将这些数据提供给另一个州的旅游局，而且要求提供XML格式的数据。一开始，你可以利用XMLElement为每个旅游点生成一个XML元素，如下所示： SELECT XMLElement("Attraction", attraction_name) FROM attraction; XMLELEMENT("ATTRACTION",ATTRACTION_NAME) ------------------------------------------ <Attraction>Pictured Rocks</Attraction> <Attraction>Da Yoopers ... </Attraction> <Attraction>Valley Camp ...</Attraction> ... XMLElement是需要掌握的最重要的SQL/XML函数之一，因为创建XML元素是SQL/XML存在的基本原因。XMLElement的第一个参数是一个标识符参数而不是字符串参数；它是一个与标识符的表名或列名一样的标识符，因此在上面的XMLElement查询中可以使用双引号。如果要使表中的列名为小写字母，可以在创建表时对列名使用双引号。同样，如果要使元素名称为小写字母，也可以把它放在双引号里。XMLElement的第二个参数是为要创建的元素提供值的列名。 对于所提供的数据，你可能不仅仅想要旅游点的名称，而是还想要其他信息。可以通过嵌套调用XMLElement为每个旅游点生成子元素，如清单1所示。外层的XMLElement调用生成元素。内层的XMLElement调用生成嵌套的、和元素。注意外层的XMLElement函数调用中第二个参数XMLAttributes的使用。XMLAttributes调用是XMLElement的第二个可选参数，在这种情况下，它将导致在每个标记中都能见到的GOV属性。 理解XMLElement函数返回的是XMLType值而不是字符串值这一点非常重要。因为SQL*Plus（在Oracle9i第2版中）对所选择的任何一个XMLType值都隐式地提取并显示XML文本，所以你可以在清单中看到字符。如果你想使用SQL*Plus重新生成本文中的示例，可以在这里下载创建表的脚本。在SQL*Plus中运行命令SET LONG 2000，以便XML的输出不被删减，同时可以选择运行命令SET PAGESIZE 80以避免在XML输出中出现令人讨厌的分页符。 处理可能出现的空元素 在对关系数据进行操作的任何时候，都必须考虑遇到空值的可能性。例如，如果某些旅游点的LOCATION列为空怎么办？使用XMLElement时，空的列值会产生空元素，如清单2所示的第一个查询及其结果。如果你不希望你的XML中有这样的空元素，至少可以采用两种解决方法。 避免创建空的XML元素可以采用的第一个解决方法是使用SQL CASE表达式来测试一个列是否有空值，并且相应地返回空值或者XMLElement的结果。清单2中的第二个查询使用的就是这种方法。当CASE表达式涉及的列为空时，则确保返回空（NULL），并将非空值传递给内层的XMLElement函数。外层XMLElement将所有非空值放到一起，并且忽略所有可能变成空XML元素的空值。 避免创建空的XML元素的另一种方法是使用XMLForest函数。在XML术语中，嵌套在下面的元素可以称为一个"森林"。XMLForest使你只需进行一次函数调用就能生成元素森林。在生成这些元素时，XMLForest跳过带有空值的元素。你可以在下面查询的输出中看到这一点，在该查询中，XMLForest在查询结果中不产生空的元素： SELECT XMLElement("Attraction", XMLAttributes(government_owned AS GOV), XMLForest(attraction_name AS "Name", Location AS "Location", attraction_url AS "URL")) FROM attraction WHERE attraction_name='Mackinac Bridge'; XMLELEMENT("ATTRACTION",XMLATTRIBUTES(GOV ------------------------------------------------- <Attraction GOV="Y"> <Name>Mackinac Bridge</Name> <URL>http://www.mackinacbridge.org/</URL> </Attraction> 该查询只调用一次XMLForest，比清单2所示的进行三次XMLElement调用的查询更易于输入，而且输入错误的可能性更小。在这两种情况下，查询结果中都去除了空元素。使用XMLForest的缺点是不能指定元素属性。如果需要指定元素属性，则必须结合使用XMLElement和XMLAttributes。 汇总XML元素 前面的查询为每个旅游点生成了一个独立的XML文档。这种情况并不现实。如果要向业务合作伙伴提供关于旅游点的数据，你可能希望以某种方式汇总这些数据。例如，你可能希望把某一个县的所有旅游点汇集在一起，并把它们放在一个文档中进行传输。你可以通过结合使用GROUP BY查询和XMLAgg函数来实现这一目标。 就像MIN、MAX和AVG一样，XMLAgg是一个汇总函数。使用它的关键是根据某一公有值对这些数据进行分组。清单3中的查询按照县名对数据分组。然后XMLAgg函数获取给定县的所有单个元素，把它们结合在一起，并将它们作为一个单一的XMLType值返回。然后将该值提供给一个生成元素的新的、所包含的XMLElement函数调用。该查询的结果显示在清单3中。 在清单3中，可以看到XMLAttributes被用于生成多个属性值。最外层的XMLAttributes调用生成三个属性：一个用于县名，两个指向文档所遵从的XML模式。由于查询中使用了GROUP BY子句，因此最外层的XMLElement函数调用及其相关的XMLAttributes调用仅涉及汇总的列。试着用a.county_name代替c.county_name，则会得到一个错误，因为a.county_name不是一个GROUP BY表达式。 对XML视图进行文件夹分层组织 在Oracle9i第2版中，与XML相关的一个特别令人兴奋的特性是可以使用一个SQL/XML查询（如本文中我所编写的查询）作为创建XMLType视图的基础。然后你可以对该视图进行文件夹分层组织，以便其内容作为XML文件显示在XML DB信息库的目录中。 下一步 了解关于SQL/XML标准的内容 sqlx.org 有关Oracle SQL/XML的更多内容 otn.oracle.com/tech/xml/ xmldb/htdocs/sql_xml.html 下载本文的表创建代码 otn.oracle.com/oramag/oracle/03-may/o33xml_tablecreate.zip XML DB演示 otn.oracle.com/tech/xml/xmldb 阅读相关文章 otn.oracle.com/oramag/oracle/03-jan/o13xml.html 关于XML DB otn.oracle.com/tech/xml/ xmldb/pdf/XMLDB_Technical_Whitepaper.pdf 无论你什么时候用Internet Explorer（IE）或诸如Microsoft Excel这样的应用程序打开这些XML文件中的一个, 都可以通过执行创建特定XMLType视图的查询来即时构造文件的内容。Microsoft Excel的Microsoft Office XP版支持XML文件格式，所以，如果你使用Excel期望的XML模式生成一个XMLType视图，你就可以打开包含数据库最新数据的电子数据表。 清单4显示了本文中编写的查询所生成的XMLType视图。（请注意这里特殊的CREATE VIEW语句和文件夹分层组织特性只有在Oracle9i数据库9.2.0.2版本或更高的版本中才有效）。CREATE VIEW语句中的OBJECT ID子句为视图中的每一行生成一个惟一的对象标识符。该视图为每个县返回一个XML文档（一行），该文档列出了该县所有的旅游点。你在extractValue函数中看到的对sys_nc_rowinfo$的引用是一个对该视图"当前"行的引用。Xpath查询语法'/County/@Name'使extractValue返回每个元素中的Name属性的值，该值将被用作为该视图返回的每一行生成惟一标识符的根据。 只有视图还不能使XML文档出现在XML DB信息库中。还必需创建信息库资源（它们以文档形式出现）并将每个资源与一个对象标识符联系起来。清单5中的PL/SQL块为attraction_xml视图完成这一工作。注意，这里甚至不需要对视图进行查询。清单5中的代码打开一个游标，来从视图所基于的旅游点表中检索所有不同的县名。然后调用MAKE_REF函数，为该视图结果集中的每个县的行生成一个REF。最后，在DBMS_XDB.createResource调用中使用该REF。createResource函数在XML DB信息库中创建一个资源，并将该资源与视图结果集中给定县的数据联系起来。在该信息库中，资源以.xml文件的形式出现。 注意，在运行清单5中的代码之前，你需要在你的信息库中创建一个名为/ATTRACTIONS的文件夹。PL/SQL代码就在这个文件夹中创建视图的XML文档。 不断学习！ 在Oracle9i数据库中，Oracle对SQL/XML的支持使得从现有的关系数据中生成XML数据变得非常容易。使用SQL/XML函数，你可以轻松地创建XML元素、嵌套的XML元素、XML元素汇总，等等。内置的XMLType数据类型使你能够在数据库中操作XML数据。基于SQL/XML的视图、XML DB信息库和文件夹分层组织特性的强大联合使你能够通过XML使数据直接用于诸如Microsoft Excel等应用程序。如果你计划今后从关系数据生成XML，甚至只是认为你可能会这么做，那么就花些时间来了解XMLType以及各种SQL/XML函数吧。 Jonathan Gennick (Jonathan@Gennick.com) 是一名经验丰富的Oracle DBA和Oracle认证专家（OCP）。他喜欢研究新的Oracle技术，最近完成了著作《Oracle SQL*Plus袖珍参考手册，第2版》（Oracle SQL*Plus Pocket Reference，Second Edition）（O'Reilly & Associates出版，2002年）。 有人这么写过： select  '<?xml version=\"1.0\" encoding=\"GBK\"?>'||xmlagg(xmlelement("LIST", xmlforest( customer_id,certi_type,certi_code,real_name,gender,to_char(birthday,'yyyy-mm-dd') birthday, 'Un' addr) cust)).getClobVal() result from cm_customer a where a.certi_type=? and a.certi_code=?;