批量导入和导出 XML 文档的示例 (SQL Server)

项目
07/12/2024

适用于：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

可以将 XML 文档大容量导入到 SQL Server 数据库中，也可以从 SQL Server 数据库中批量导出 XML 文档。本文提供了这两种方法的示例。

若要将数据从一个数据文件批量导入 SQL Server 表或未分区视图，可以使用以下选项：

bcp 实用工具

还可以使用 bcp 实用工具将数据从可执行 SELECT 语句的 SQL Server 数据库的任意位置（包括分区视图）导出。
BULK INSERT
INSERT ... SELECT * FROM OPENROWSET(BULK...)

有关详细信息，请参阅以下文章：

以二进制字节流的形式大容量导入 XML 数据

在从文件批量导入 XML 数据时，如果文件中包含您要应用的编码声明，则应在 OPENROWSET(BULK...) 子句中指定 SINGLE_BLOB 选项。 SINGLE_BLOB 选项可确保 SQL Server 中的 XML 分析器根据 XML 声明中指定的编码方案导入数据。

示例表

若要测试示例 A，请创建示例表 T。

USE tempdb;
GO

CREATE TABLE T (
    IntCol INT IDENTITY(1,1),
    XmlCol XML
    );
GO

示例数据文件

在运行示例 A 之前，必须先创建一个 UTF-8 编码文件 (C:\SampleFolder\SampleData3.txt)，该文件应包含指定 UTF-8 编码方案的以下示例实例。

<?xml version="1.0" encoding="UTF-8"?>
<Root>
    <ProductDescription ProductModelID="5">
        <Summary>Some Text</Summary>
    </ProductDescription>
</Root>

示例 A

此示例使用 SINGLE_BLOB 语句中的 INSERT ... SELECT * FROM OPENROWSET(BULK...) 选项从名为 SampleData3.txt 的文件中导入数据，并在包含单列的示例表 T中插入一个 XML 实例。

INSERT INTO T (XmlCol)
SELECT *
FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
    SINGLE_BLOB)
AS x;

还可以按如下所示显式指定列名称：

INSERT INTO T (
    XmlCol
)
SELECT
    x.BulkColumn
FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
    SINGLE_BLOB)
AS x;

注解

在这个例子中，通过使用 SINGLE_BLOB，可以避免 XML 文档的编码（由 XML 编码声明所指定）与服务器隐含使用的字符串代码页不匹配的问题。

如果使用 NCLOB 或 CLOB 数据类型且遇到代码页或编码冲突，则必须执行下列操作之一：

删除 XML 声明，以成功导入 XML 数据文件的内容。
在查询的 CODEPAGE 选项中指定一个代码页，该代码页须与 XML 声明中使用的编码方案相匹配。
使用非 Unicode XML 编码方案匹配或解析数据库排序规则设置。

[Top]

将 XML 数据大容量导入现有行中

此示例使用 OPENROWSET 大容量行集提供程序向示例表 T中的现有行添加一个 XML 实例。

注意

若要运行此示例，必须先完成示例 A 中提供的测试脚本。该示例创建了 tempdb.dbo.T 表，并从 SampleData3.txt中大容量导入数据。

示例数据文件

示例 B 使用的是上例所使用 SampleData3.txt 示例数据文件的修改版本。若要运行此示例，请按如下所示修改此文件的内容：

<Root>
    <ProductDescription ProductModelID="10">
        <Summary>Some New Text</Summary>
    </ProductDescription>
</Root>

示例 B

-- Query before update shows initial state of XmlCol values.
SELECT * FROM T;

UPDATE T
SET XmlCol = (
    SELECT *
    FROM OPENROWSET(
    BULK 'C:\SampleFolder\SampleData3.txt',
            SINGLE_BLOB
    ) AS x
)
WHERE IntCol = 1;
GO

[Top]

从包含 DTD 的文件中大容量导入 XML 数据

重要

若非您的 XML 环境有特殊要求，建议不要启用对文档类型定义 (DTD) 的支持。启用 DTD 支持会增加服务器的可攻击外围应用，并且可能会使它受到拒绝服务攻击。如果必须启用 DTD 支持，可以通过仅处理可信的 XML 文档来降低安全风险。

使用 bcp 从包含 DTD 的文件导入 XML 数据时，可能会收到以下错误：

SQLState = 42000, NativeError = 6359

Error = [Microsoft][SQL Server Native Client][SQL Server]Parsing XML with internal subset DTDs not allowed.
Use CONVERT with style option 2 to enable limited internal subset DTD support.

BCP copy %s failed

若要解决此问题，可以使用 OPENROWSET(BULK...) 函数，并在命令的 CONVERT 子句中指定 SELECT 选项，以从包含 DTD 的数据文件中导入 XML 数据。该命令的基本语法如下：

INSERT ... SELECT CONVERT(...) FROM OPENROWSET(BULK...)

示例数据文件

在测试此批量导入示例之前，需要先创建一个包含以下示例实例的文件 (C:\SampleFolder\Dtdfile.xml)：

<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>

示例表

示例 C 使用由以下 T1 语句创建的 CREATE TABLE 示例表：

USE tempdb;
GO
CREATE TABLE T1(XmlCol XML);
GO

示例 C

此示例使用 OPENROWSET(BULK...) ，并在 CONVERT 子句中指定了 SELECT 选项，从而将 XML 数据从 Dtdfile.xml 导入到了示例表 T1中。

INSERT INTO T1
SELECT CONVERT(XML, BulkColumn, 2)
FROM OPENROWSET(
    BULK 'C:\SampleFolder\Dtdfile.xml',
    SINGLE_BLOB
) AS [rowsetresults];

执行 INSERT 语句后，会将 DTD 从 XML 中提取出来，并存储到 T1 表中。

[Top]

使用格式化文件显式指定字段终止符

下面的示例说明如何大容量导入 XML 文档 Xmltable.dat。

示例数据文件

Xmltable.dat 中的文档包含两个 XML 值，每行一个。第一个 XML 值的编码为 UTF-16，第二个值的编码为 UTF-8。

下面的十六进制转储显示了此数据文件的内容：

FF FE 3C 00 3F 00 78 00-6D 00 6C 00 20 00 76 00  *..\<.?.x.m.l. .v.*
65 00 72 00 73 00 69 00-6F 00 6E 00 3D 00 22 00  *e.r.s.i.o.n.=.".*
31 00 2E 00 30 00 22 00-20 00 65 00 6E 00 63 00  *1...0.". .e.n.c.*
6F 00 64 00 69 00 6E 00-67 00 3D 00 22 00 75 00  *o.d.i.n.g.=.".u.*
74 00 66 00 2D 00 31 00-36 00 22 00 3F 00 3E 00  *t.f.-.1.6.".?.>.*
3C 00 72 00 6F 00 6F 00-74 00 3E 00 A2 4F 9C 76  *\<.r.o.o.t.>..O.v*
0C FA 77 E4 80 00 89 00-00 06 90 06 91 2E 9B 2E  *..w.............*
99 34 A2 34 86 00 83 02-92 20 7F 02 4E C5 E4 A3  *.4.4..... ..N...*
34 B2 B7 B3 B7 FE F8 FF-F8 00 3C 00 2F 00 72 00  *4.........\<./.r.*
6F 00 6F 00 74 00 3E 00-00 00 00 00 7A EF BB BF  *o.o.t.>.....z...*
3C 3F 78 6D 6C 20 76 65-72 73 69 6F 6E 3D 22 31  *\<?xml version="1*
2E 30 22 20 65 6E 63 6F-64 69 6E 67 3D 22 75 74  *.0" encoding="ut*
66 2D 38 22 3F 3E 3C 72-6F 6F 74 3E E4 BE A2 E7  *f-8"?><root>....*
9A 9C EF A8 8C EE 91 B7-C2 80 C2 89 D8 80 DA 90  *................*
E2 BA 91 E2 BA 9B E3 92-99 E3 92 A2 C2 86 CA 83  *................*
E2 82 92 C9 BF EC 95 8E-EA 8F A4 EB 88 B4 EB 8E  *................*
B7 EF BA B7 EF BF B8 C3-B8 3C 2F 72 6F 6F 74 3E  *.........</root>*
00 00 00 00 7A                                   *....z*

示例表

批量导入或导出 XML 文档时，应当使用在任何文档中都不可能出现的字段终止符；例如，在连续四个 Null (\0) 后紧跟字母 z：\0\0\0\0z。

此示例说明如何为 xTable 示例表使用此字段终止符。若要创建此示例表，请使用下列 CREATE TABLE 语句：

USE tempdb;
GO
CREATE TABLE xTable (xCol XML);
GO

示例格式化文件

必须在格式化文件中指定字段终止符。示例 D 使用了一个名为 Xmltable.fmt 的非 XML 格式化文件，该文件包含以下输出：

9.0
1
1       SQLBINARY     0       0       "\0\0\0\0z"    1     xCol         ""

可以使用此格式化文件并通过 xTable 命令、 bcp 语句或 BULK INSERT 语句将 XML 文档大容量导入到 INSERT ... SELECT * FROM OPENROWSET(BULK...) 表中。

示例 D

此示例在 Xmltable.fmt 语句中使用 BULK INSERT 格式化文件来导入 XML 数据文件 Xmltable.dat中的内容。

BULK INSERT xTable
FROM 'C:\SampleFolder\Xmltable.dat'
WITH (FORMATFILE = 'C:\SampleFolder\Xmltable.fmt');
GO

[Top]

大容量导出 XML 数据

下面的示例使用 bcp 从表中批量导出 XML 数据，该表是在上一示例中使用同一个 XML 格式化文件创建的。在下面的 bcp 命令中， <server_name> 和 <instance_name> 代表必须使用相应的值替换的占位符：

bcp bulktest..xTable out a-wn.out -N -T -S<server_name>\<instance_name>

注意

SQL Server 不保存 XML 编码。因此，在导出 XML 数据时，XML 字段的原始编码将不可用。 SQL Server 导出 XML 数据时，使用 UTF-16 编码。

通过

批量导入和导出 XML 文档的示例 (SQL Server)

示例

以二进制字节流的形式大容量导入 XML 数据

示例表

示例数据文件

示例 A

注解

将 XML 数据大容量导入现有行中

示例数据文件

示例 B

从包含 DTD 的文件中大容量导入 XML 数据

示例数据文件

示例表

示例 C

使用格式化文件显式指定字段终止符

示例数据文件

示例表

示例格式化文件

示例 D

大容量导出 XML 数据

另请参阅

反馈

其他资源