一、XML技术简介
XML是可标记扩展语言eXtensible Markup Language的缩写，它将用户接口与结构化数据分隔开来，实现数据与显示的分离，使得其逐渐成为因特网上数据表示和数据交换的新标准。用XML进行数据管理具有以下几个特点：
（一）XML是纯文本，开发简单；
（二）基于内容的数据标识可格式化及可扩展性；
（三）可用CSS、 XSL自定义数据的显示方式；
（四）与平台无关，可在Windows、Linux等任意平台任选一解析器进行解析。
如，“家电下乡”信息系统中提供给生产厂家的享受补贴明细XML文件内容如下（部分）：
|
<?xml version="1.0" encoding="gb2312"?> <ROOT> <PRODUCT> <PD_NAME>空调</PD_NAME> <PD_ID>8020971</PD_ID> <PD_TYPE>6</PD_TYPE> <PD_SERIAL>NB2990050126</PD_SERIAL> </PRODUCT> <PRODUCT> <PD_NAME>空调</PD_NAME> <PD_ID>8020971</PD_ID> <PD_TYPE>6</PD_TYPE> <PD_SERIAL>NB2990050226</PD_SERIAL> </PRODUCT> …… </ROOT> |
在XML序言后面的是XML的根元素。在上面的例子中，此元素就是
XML是一种“元”语言，它定义了一套标准，而世界上任何个人和组织均可以基于这套标准建立适合自己使用的标记语言。如，用于地理信息系统的地理标记语言GML（Geography Markup Language），用于金融行业的金融产品标记语言FpML(Financial Products Markup Language)，在开展资源环境审计或金融审计中均有可能遇见，且对它们的处理都遵循统一的标准。
二、SQL Server数据库对XML的支持
SQL Server 对XML的支持源自2005版。用户可以创建这样的表，它在关系列之外还有一个或多个 XML 类型的列；此外，还允许带有变量和参数。为了更好地支持 XML 模型特征（例如文档顺序和递归结构），XML 值以内部格式存储为大型二进制对象 (BLOB)。
用户将一个XML数据存入数据库的时候，可以使用这个XML的字符串，SQL Server会自动的将这个字符串转化为XML类型，并存储到数据库中。相应的，T-SQL语句也提供了大量对XML操作的功能来配合SQL Server中XML字段的使用。
（一）定义XML字段并插入XML文件。
XML字段的定义即是在创建数据表时，对要用于存储XML数据的列，指定其数据格式为XML。如，要创建一个存放家电下乡空调补贴明细的表，包含两个字段，一是存放空调型号的字段，二是存放该型号空调享受家电下乡补贴明细XML文件的字段。SQL语句如下：
|
CREATE TABLE 家电下乡空调补贴明细 ( 型号 NVARCHAR(255) PRIMARY KEY, 明细 XML NOT NULL ) |
|
DECLARE @doc XML; SELECT @doc=BULKCOLUMN FROM OPENROWSET( BULK N'D:/KF-25U2-B.xml', SINGLE_BLOB) AS x
INSERT INTO 家电下乡空调 VALUES ('KF-25U2-B', @doc) |
|
（二）SQL Server对XML字段的查询操作。
在定义了一个XML类型的数据之后，我们最常用的就是查询操作，下面我们来介绍如何使用SQL语句来进行查询操作。
在T—SQL中，提供了两个对XML类型数据进行查询的函数，分别是query(xquery)和value(xquery, dataType)。其中，query(xquery)得到的是带有标签的数据，而value(xquery, dataType)得到的则是标签的内容。下面，我们分别使用这两个函数来进行查询。
1．使用query(xquery)查询。
该函数通过传递某一元素的“XPath路径”为参数，查找该路径下的元素。如，我们需要获取上例中的第一个产品(
|
DECLARE @doc xml; DECLARE @DocHandle int SET @doc=(SELECT 明细 FROM 家电下乡空调) SELECT @doc.query('(/ROOT/PRODUCT/PD_SERIAL)[1]') |
|
2．使用value(xquery, dataType)查询。
该函数通过传递某一元素的“XPath路径”和要返回值的数据类型为参数，查找该路径下的元素的值。如，在上例中，要查找第一个产品(
|
DECLARE @doc xml; DECLARE @DocHandle int SET @doc=(SELECT 明细 FROM 家电下乡空调) SELECT @doc.value('(/ROOT/PRODUCT/PD_SERIAL)[1]', 'NVARCHAR(MAX)') |
|
使用上述两种方法能够较快地定位XML中某一个元素的值，但是在实际运用过程中，如果需要大量获取数据则显得比较吃力。如，某型号空调家电下乡补贴明细中含有300条产品信息，即
对于这种情况，SQL Server数据库提供了一种将XML文件转换成关系型数据表的方法。
（三）使用OPENXML将XML转换成关系型数据表。
OPENXML通过XML文档提供行集视图。由于OPENXML是行集提供程序，因此可在会出现行集提供程序（如表、视图或OPENROWSET函数）的Transact-SQL语句中使用OPENXML。其语法表示如下：
|
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH (SchemaDeclaration) ] |
idoc: XML文档的内部表式形式的文档句柄。通过调用 sp_xml_preparedocument创建XML文档的内部表式形式。
rowpattern: XPath模式，用来标识要作为行处理的节点（这些节点在XML文档中，该文档的句柄由idoc参数传递）。
flags: 指示应在XML数据和关系行集间使用映射以及应如何填充溢出列。flags为可选输入参数，可以是下列值之一。
|
字节值 |
说明 |
|
0 |
默认为“以属性为中心”的映射。 |
|
1 |
使用“以属性为中心”的映射。 可以与 XML_ELEMENTS 一起使用。 这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。 |
|
2 |
使用“以元素为中心”的映射。 可以与 XML_ATTRIBUTES 一起使用。 这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。 |
|
8 |
可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑或）。 在检索的上下文中，该标志指示不应将已使用的数据复制到溢出属性 @mp:xmltext。 |
如，要将上述空调家电下乡推广明细的XML文件转换成关系型数据表，其语句如下：
|
DECLARE @doc xml; DECLARE @DocHandle int DECLARE @model nvarchar(255) SET @model='KF-25U2-B' SET @doc=(SELECT 明细 FROM 家电下乡空调 WHERE 型号=@model) EXEC sp_xml_preparedocument @DocHandle OUTPUT, @doc SELECT @model AS 型号, * FROM OPENXML (@DocHandle, '/ROOT/PRODUCT',2) WITH (PD_NAME nvarchar(max), PD_ID nvarchar(max), PD_TYPE nvarchar(max), PD_SERIAL nvarchar(max)) EXEC sp_xml_removedocument @DocHandle |
|
三、SQL Server对XML的支持在审计中的应用
通过上述介绍，在对企业生产的空调产品家电下乡补贴数据和节能推广数据进行比对时，可以先将家电下乡补贴数据转换成关系型数据表“家电下乡补贴数据明细”，以机内码（即
|
SELECT * FROM [KMB_空调数据].[dbo].[空调数据] WHERE 室内机编码 IN ( SELECT PD_SERIAL FROM 家电下乡补贴数据明细) |
| 【关闭】 【打印】 |
