大量匯入與匯出 XML 文件的範例 (SQL Server)

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

您可以將 XML 文件大量匯入 SQL Server 資料庫或從 SQL Server 資料庫將其大量匯出。 本文提供這兩種做法的範例。

若要從資料檔案將資料大量匯入 SQL Server 資料表或非資料分割檢視，您可以使用下列選項：

• bcp 公用程式

  您也可以使用 bcp 公用程式，從 SQL Server 資料庫中能執行 SELECT 陳述式的任意位置 (包括資料分割檢視) 匯出資料。

• BULK INSERT

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

如需詳細資訊，請參閱下列文章：

• 使用 bcp 匯入和匯出大量資料 (SQL Server)
• 使用 BULK INSERT 或 OPENROWSET(BULK...) 將資料匯入 SQL Server
• 如何使用 XML 大量載入元件將 XML 匯入 SQL Server
• XML 結構描述集合 (SQL Server)

範例

• A. 以二進位位元組資料流大量匯入 XML 資料
• B. 在現有資料列中大量匯入 XML 資料
• C. 從包含 DTD 的檔案大量匯入 XML 資料
• D. 使用格式檔案明確指定欄位結束字元
• E. 大量匯出 XML 資料

以二進位位元組資料流大量匯入 XML 資料

從包含您要套用的編碼宣告之檔案大量匯入 XML 資料時，請在 SINGLE_BLOB 子句中指定 OPENROWSET(BULK...) 選項。 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 的檔案匯入資料，並將 XML 執行個體插入範例資料表 T這個單一資料行資料表。

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 大量資料列集提供者，將 XML 執行個體加入範例資料表 T中的現有資料列。

注意

若要執行這個範例，首先必須完成範例 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

若要解決這個問題，您可以從包含 DTD 的資料檔匯入 XML 資料，方法是使用 OPENROWSET(BULK...) 函數，然後在命令的 CONVERT 子句中指定 SELECT 選項。 此命令的基本語法如下：

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 陳述式之後，會從 XML 移除 DTD，並將它儲存在 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         ""

您可以使用這個格式檔案，將 XML 文件大量匯入 xTable 資料表，方法是使用 bcp 命令或 BULK INSERT 或 INSERT ... SELECT * FROM OPENROWSET(BULK...) 陳述式。

範例 D

這個範例在 Xmltable.fmt 陳述式中使用 BULK INSERT 格式檔案，以匯入名為 Xmltable.dat之 XML 資料檔案的內容。

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 資料之後，便無法使用 XML 欄位的原始編碼。 SQL Server 會在匯出 XML 資料時使用 UTF-16 編碼。

另請參閱

• INSERT (Transact-SQL)
• SELECT 子句 (Transact-SQL)
• bcp 公用程式
• 資料的大量匯入及匯出 (SQL Server)
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)