4.4.1 使用大容量加载

第4章   XML数据处理（节选）

SQL Server 2005为XML数据处理提供了广泛支持：新增了xml数据类型及相关的一系列处理方法，同时，也大大改进了FOR XML。在SQL Server 2005中，用户可以：

l 将XML值存储在根据XML架构集合类型化或保持非类型化的xml数据类型列中。
l 对xml类型的列创建索引。
l 使用XQuery和XML DML（XML DML是针对数据修改的扩展）对XML数据进行各种操作。

本章主要介绍在SQL SERVER 2005中，如何操作和生成xml类型的数据。

4.4 构建xml数据

相较于SQL Server中的其他数据类型，xml数据类型的数据显得比较复杂，它里面可以包含各种类型的节点及指令；当在数据库中使用xml数据类型时，它与数据库中的现有数据进行交互又显得特别重要。因此，讨论如何在数据库中构建XML数据是很有意义的。

由于SQL Server 2005之前的版本不支持xml数据类型，因此，使用这些版本时，一般是把XML数据存储在text、ntext或者image类型的列中。当升级到SQL Server 2005时，可以将这些存储XML数据的字段类型修改为xml类型：image类型的列需要先修改为varbinary(max)，然后再修改为xml类型；text、ntext类型可以直接修改为xml类型。

下面的示例演示如何将存储xml数据的text、ntext和image类型的列修改为xml数据类型。示例首先定义了一个测试表，测试表包含text、ntext和image三种类型的列，并存储了xml数据；随后通过ALTER TABLE语句将这些列的类型转换为xml类型。

-- 建立测试环境, 使用text/ntext/image 类型的列存储xml数据
CREATE TABLE #t(
col1 text,
col2 ntext,
col3 image)
INSERT #t(
col1,
col2,
col3)
VALUES(
'',
N'测试',
CONVERT(varbinary(8000), N'image列'))
GO

-- 将text/ntext 列的类型修改为xml 类型
ALTER TABLE #t ALTER COLUMN col1 xml
ALTER TABLE #t ALTER COLUMN col2 xml

-- 将image 列类型修改为varbinary(max), 以便于能直接转换为xml 类型
ALTER TABLE #t ALTER COLUMN col3 varbinary(max)
ALTER TABLE #t ALTER COLUMN col3 xml
GO

-- 显示结果
SELECT * FROM #t
GO

-- 删除测试环境
DROP TABLE #t

示例的执行结果如下：

col1     col2            col3
-------- --------------- -----------------
    测试    image列

如果要直接构建XML数据，则XQuery应该是最适合的（参考本章关于XQuery的介绍），借助于SQL Server XQuery扩展函数，也能在XQuery中方便地引用SQL数据。

除了XQuery外，还可以从外部XML文件直接加载XML数据，或者使用FOR XML子句将关系表直接生成XML数据。

4.4.1 使用大容量加载

可以通过BCP命令提示符工具，或者是自行编写程序将XML文件中的数据加载到xml类型的数据列中，也可以使用T-SQL语句BULK INSERT直接加载XML文件的数据到数据列中。除此之外，借助增强的OPENROWSET函数，现在可以直接读取XML数据文件的内容，这样，可以选择性地把加载的数据放入变量，或者是满足指定条件的XML数据列中。
下面的示例说明如何使用OPENROWSET加载XML文件数据。示例通过OPENROWSET，将c:\test.xml的内容以单行单列的结果集返回（通过BULK配置SINGLE_BLOB选项），返回的数据类型为varbinary(max)，因此，需要通过CONVERT将其转换为xml数据类型。另外，因为查询返回的是结果集，所以需要在函数后指定返回结果集的名字及列名，以便引用；由于返回的是结果集，因此，可以使用普通结果集的任何引用操作来操作它，例如，通过JOIN语句与数据库中的某个表关联进行查询。

SELECT 
FileInfo = CONVERT(xml, T.c)
FROM OPENROWSET(BULK 'c:\test.xml', SINGLE_BLOB) T(c)

在加载XML文件的处理中，需要注意的是编码的问题。SQL Server以Unicode（UTF-16）存储xml类型数据。如果是从采用Unicode（UCS-2、UTF-16）格式存储的XML文件加载数据，则不会有问题。如果XML文件的编码不是Unicode而是隐式的，则加载时数据时应该采用SINGLE_BLOB选项，将数据加载为varbinary(max)，它可以避免XML文档的编码（由XML编码声明所指定）与服务器隐含使用的字符串代码页不匹配的问题。

另外需要注意的是，加载编码不是Unicode的XML文件时，如果XML文件中不包含编码说明，则加载时应该显式指定编码，以避免数据编码问题。

例如，将下面的XML数据内容保存成XML文件（假设文件名为c:\test.xml），文件的存储编码选择ANSI。

测试

则在加载c:\text.ml文件的数据时，可以采用下面的T-SQL语句，通过显式指定编码进行加载。

-- 定义编码
DECLARE @encoding varbinary(max)
SET @encoding = CONVERT(varbinary(max), '')

-- 加载xml 文件中的数据
SELECT 
Doc = CONVERT(xml, @encoding + T.c)
FROM OPENROWSET(BULK 'c:\test.xml', SINGLE_BLOB) T(c)

如果加载时不显式指定编码，则会收到类似下面的错误信息。

消息9420，级别16，状态1，第2行
XML 分析: 行1，字符4，非法的xml字符

如果将编码指令“”加入到XML文件中，则在加载XML文件数据时，不需要指定编码。

注：
SQL Server始终以Unicode（UTF-16）存储xml类型数据，编码指令仅影响数据加载时的编码解析，

不会影响到数据存储到数据库中时的实际编码。