批量导入和导出 XML 文档的示例 (SQL Server)Examples of Bulk Import and Export of XML Documents (SQL Server)

适用对象: 是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

可以将 XML 文档大容量导入到 SQL ServerSQL Server 数据库中,也可以从 SQL ServerSQL Server 数据库中大容量导出 XML 文档。You can bulk import XML documents into a SQL ServerSQL Server database or bulk export them from a SQL ServerSQL Server database. 本主题提供了这两种情况的示例。This topic provides examples of both.

若要将数据从一个数据文件大容量导入 SQL ServerSQL Server 表或未分区视图,可以使用以下工具或命令:To bulk import data from a data file into a SQL ServerSQL Server table or non-partitioned view, you can use the following:

  • bcp 实用工具bcp utility
    还可以使用 bcp 实用工具将数据从可执行 SELECT 语句的 SQL ServerSQL Server 数据库的任意位置(包括分区视图)导出。You can also use the bcp utility to export data from anywhere in a SQL ServerSQL Server database that a SELECT statement works, including partitioned views.

  • BULK INSERTBULK INSERT

  • INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...)

有关详细信息,请参阅以下主题。For more information, see the following topics.

示例Examples

下列示例说明了以下操作内容:The examples are the following:

以二进制字节流的形式大容量导入 XML 数据Bulk importing XML data as a binary byte stream

在从文件大容量导入 XML 数据时,如果文件中包含要应用的编码声明,则应在 OPENROWSET(BULK…) 子句中指定 SINGLE_BLOB 选项。When you bulk import XML data from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB option in the OPENROWSET(BULK...) clause. SINGLE_BLOB 选项可确保 SQL ServerSQL Server 中的 XML 分析器根据 XML 声明中指定的编码方案导入数据。The SINGLE_BLOB option ensures that the XML parser in SQL ServerSQL Server imports the data according to the encoding scheme specified in the XML declaration.

示例表Sample Table

若要测试下方的示例 A,请创建示例表 TTo test example A below, create sample table T.

USE tempdb  
CREATE TABLE T (IntCol int, XmlCol xml);  
GO  

示例数据文件Sample Data File

在运行示例 A 之前,必须先创建一个 UTF-8 编码文件 (C:\SampleFolder\SampleData3.txt),该文件应包含指定 UTF-8 编码方案的以下示例实例。Before you can run example A, you must create a UTF-8 encoded file (C:\SampleFolder\SampleData3.txt) that contains the following sample instance that specifies the UTF-8 encoding scheme.

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

示例 AExample A

此示例使用 SINGLE_BLOB 语句中的 INSERT ... SELECT * FROM OPENROWSET(BULK...) 选项从名为 SampleData3.txt 的文件中导入数据,并在包含单列的示例表 T中插入一个 XML 实例。This example uses the SINGLE_BLOB option in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement to import data from a file named SampleData3.txt and insert an XML instance in the single-column table, sample table T.

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

RemarksRemarks

在这个例子中,通过使用 SINGLE_BLOB,可以避免 XML 文档的编码(由 XML 编码声明所指定)与服务器隐含使用的字符串代码页不匹配的问题。By using SINGLE_BLOB in this case, you can avoid a mismatch between the encoding of the XML document (as specified by the XML encoding declaration) and the string codepage implied by the server.

如果使用 NCLOB 或 CLOB 数据类型且遇到代码页或编码冲突,则必须执行下列操作之一:If you use NCLOB or CLOB data types and run into a codepage or encoding conflict, you must do one of the following:

  • 删除 XML 声明,以成功导入 XML 数据文件的内容。Remove the XML declaration to successfully import the contents of the XML data file.

  • 在查询的 CODEPAGE 选项中指定一个代码页,该代码页须与 XML 声明中使用的编码方案相匹配。Specify a code page in the CODEPAGE option of the query that matches the encoding scheme that is used in the XML declaration.

  • 使用非 Unicode XML 编码方案匹配或解析数据库排序规则设置。Match, or resolve, the database collation settings with a non-Unicode XML encoding scheme.

[返回页首][Top]

将 XML 数据大容量导入现有行中Bulk importing XML data in an existing row

此示例使用 OPENROWSET 大容量行集提供程序向示例表 T中的现有行添加一个 XML 实例。This example uses the OPENROWSET bulk rowset provider to add an XML instance to an existing row or rows in sample table T.

备注

若要运行此示例,必须先完成示例 A 中提供的测试脚本。该示例创建了 tempdb.dbo.T 表,并从 SampleData3.txt中大容量导入数据。To run this example, you must first complete the test script provided in example A. That example creates the tempdb.dbo.T table and bulk imports data from SampleData3.txt.

示例数据文件Sample Data File

示例 B 使用的是上例所使用 SampleData3.txt 示例数据文件的修改版本。Example B uses a modified version of the SampleData3.txt sample data file from the preceding example. 若要运行此示例,请按如下所示修改此文件的内容:To run this example, modify the content of this file as follows:

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

示例 BExample 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 数据Bulk importing XML data from a file that contains a DTD

重要

若非您的 XML 环境有特殊要求,建议不要启用对文档类型定义 (DTD) 的支持。We recommended that you not enable support for Document Type Definitions (DTDs) if it is not required in your XML environment. 启用 DTD 支持会增加服务器的可攻击外围应用,并且可能会使它受到拒绝服务攻击。Turning on DTD support increases the attackable surface area of your server, and may expose it to a denial-of-service attack. 如果必须启用 DTD 支持,可以通过仅处理可信的 XML 文档来降低安全风险。If you must enable DTD support, you can reduce this security risk by processing only trusted XML documents.

在尝试使用 bcp 命令从包含 DTD 的文件中导入 XML 数据的过程中,可能会出现如下错误:During an attempt to use a bcp command to import XML data from a file that contains a DTD, an error similar to the following can occur:

“SQLState = 42000,NativeError = 6359”"SQLState = 42000, NativeError = 6359"

“错误 = [Microsoft][SQL Server Native Client][SQL Server]不允许使用内部子集 DTD 分析 XML。"Error = [Microsoft][SQL Server Native Client][ SQL Server]Parsing XML with internal subset DTDs not allowed. 请将 CONVERT 与样式选项 2 一起使用,以启用有限的内部子集 DTD 支持。”Use CONVERT with style option 2 to enable limited internal subset DTD support."

“BCP 复制 %s 失败”"BCP copy %s failed"

若要解决此问题,可以使用 OPENROWSET(BULK...) 函数,并在命令的 CONVERT 子句中指定 SELECT 选项,以从包含 DTD 的数据文件中导入 XML 数据。To work around this problem, you can import XML data from a data file that contains a DTD by using the OPENROWSET(BULK...) function and then specifying the CONVERT option in the SELECT clause of the command. 该命令的基本语法如下:The basic syntax for the command is:

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

示例数据文件Sample Data File

在测试此批量导入示例之前,需要先创建一个包含以下示例实例的文件 (C:\temp\Dtdfile.xml):Before you can test this bulk import example, create a file (C:\temp\Dtdfile.xml) that contains the following sample instance:

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

示例表Sample Table

示例 C 使用由以下 T1 语句创建的 CREATE TABLE 示例表:Example C uses the T1 sample table that is created by the following CREATE TABLE statement:

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

示例 CExample C

此示例使用 OPENROWSET(BULK...) ,并在 CONVERT 子句中指定了 SELECT 选项,从而将 XML 数据从 Dtdfile.xml 导入到了示例表 T1中。This example uses OPENROWSET(BULK...) and specifies the CONVERT option in the SELECT clause to import the XML data from Dtdfile.xml into sample table T1.

INSERT T1  
  SELECT CONVERT(xml, BulkColumn, 2) FROM   
    OPENROWSET(Bulk 'c:\temp\Dtdfile.xml', SINGLE_BLOB) [rowsetresults];  

执行 INSERT 语句后,会将 DTD 从 XML 中提取出来,并存储到 T1 表中。After the INSERT statement executes, the DTD is stripped from the XML and stored in the T1 table.

[返回页首][Top]

使用格式化文件显式指定字段终止符Specifying the field terminator explicitly using a format file

下面的示例说明如何大容量导入 XML 文档 Xmltable.datThe following example shows how to bulk import the following XML document, Xmltable.dat.

示例数据文件Sample Data File

Xmltable.dat 中的文档包含两个 XML 值,每行一个。The document in Xmltable.dat contains two XML values, one for each row. 第一个 XML 值的编码为 UTF-16,第二个值的编码为 UTF-8。The first XML value is encoded with UTF-16, and the second value is encoded with UTF-8.

下面的十六进制转储显示了此数据文件的内容:The contents of this data file are shown in the following Hex dump:

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*  

示例表Sample Table

大容量导入或导出 XML 文档时,应当使用在任何文档中都不可能出现的 字段终止符 ;例如,在连续四个 Null (\0) 后紧跟字母 z\0\0\0\0zWhen you bulk import or export an XML document, you should use a field terminator that cannot possibly appear in any of the documents; for example, a series of four nulls (\0) followed by the letter z: \0\0\0\0z.

此示例说明如何为 xTable 示例表使用此字段终止符。This example shows how to use this field terminator for the xTable sample table. 若要创建此示例表,请使用下列 CREATE TABLE 语句:To create this sample table, use the following CREATE TABLE statement:

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

示例格式化文件Sample Format File

必须在格式化文件中指定字段终止符。The field terminator must be specified in the format file. 示例 D 使用了一个名为 Xmltable.fmt 的非 XML 格式化文件,该文件包含以下内容:Example D uses a non-XML format file named Xmltable.fmt that contains the following:

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

可以使用此格式化文件并通过 xTable 命令、 bcp 语句或 BULK INSERT 语句将 XML 文档大容量导入到 INSERT ... SELECT * FROM OPENROWSET(BULK...) 表中。You can use this format file to bulk import XML documents into the xTable table by using a bcp command or a BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement.

示例 DExample D

此示例在 Xmltable.fmt 语句中使用 BULK INSERT 格式化文件来导入 XML 数据文件 Xmltable.dat中的内容。This example uses the Xmltable.fmt format file in a BULK INSERT statement to import the contents of an XML data file named Xmltable.dat.

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

[返回页首][Top]

大容量导出 XML 数据Bulk exporting XML data

下面的示例使用 bcp 命令和同一个 XML 格式化文件从上一示例所创建的表中大容量导出 XML 数据。The following example uses bcp to bulk export XML data from the table that is created in the preceding example by using the same XML format file. 在下面的 bcp 命令中, <server_name><instance_name> 代表必须使用相应的值替换的占位符:In the following bcp command, <server_name> and <instance_name> represent placeholders that must be replaced with appropriate values:

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

备注

SQL ServerSQL Server 不保存 XML 编码。does not save the XML encoding when XML data is persisted in the database. 因此,在导出 XML 数据时,XML 字段的原始编码将不可用。Therefore, the original encoding of XML fields is not available when XML data is exported. SQL ServerSQL Server 导出 XML 数据时,使用 UTF-16 编码。uses UTF-16 encoding when exporting XML data.

另请参阅See Also

INSERT (Transact-SQL) INSERT (Transact-SQL)
SELECT 子句 (Transact-SQL) SELECT Clause (Transact-SQL)
bcp 实用工具 bcp Utility
批量导入和导出数据 (SQL Server) Bulk Import and Export of Data (SQL Server)
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)OPENROWSET (Transact-SQL)