大量匯入與匯出 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中的現有資料列。

範例資料檔案

範例 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 資料

您使用 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>

另請參閱

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