XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
適用対象: SQL Server 2016 (13.x) 以降
Azure SQL Database
Azure SQL Managed Instance
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 を実行する前に、次のサンプル インスタンスが指定するC:\SampleFolder\SampleData3.txt
エンコード体系を含む、UTF-8 エンコードのファイル ( 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 インスタンスを 1 列のテーブル (サンプル テーブル 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 宣言を削除します。
XML 宣言で使用される
CODEPAGE
エンコード スキームに一致するクエリのオプションでコード ページを指定します。データベースの照合順序の設定を Unicode 以外の XML エンコード体系に一致 (解決) させます。
既存の行に XML データの一括インポートを行う
この例では OPENROWSET
一括行セット プロパイダを使用して、XML インスタンスを既存のサンプル テーブル T
の 1 行または複数の行に追加します。
Note
この例を実行するには、まず例 A で提供されたテスト スクリプトを完了する必要があります。例 A では、 tempdb.dbo.T
テーブルを作成し、 SampleData3.txt
からデータを一括インポートをしています。
サンプル データ ファイル
例 B では、例 A の SampleData3.txt
サンプル データ ファイルを変更したバージョンを使用します。 例 B を実行するには、このファイルの内容を次のように修正します。
<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
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
この問題を回避するには、XML データを DTD を含むデータ ファイルからインポートする際に 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
オプションを指定し、 Dtdfile.xml
から 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
に格納されます。
フォーマット ファイルを使用してフィールド ターミネータを明示的に指定する
次の例では、XML ドキュメント Xmltable.dat
を一括インポートする方法を示します。
サンプル データ ファイル
Xmltable.dat
のドキュメントには 2 つの XML 値が、1 行に 1 つずつ含まれています。 最初の XML 値は UTF-16 でエンコードされており、2 番目の値は UTF-8 でエンコードされています。
このデータ ファイルの内容は、次の 16 進ダンプに示されています。
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 ドキュメントを一括インポートまたはエクスポートする場合は、どのドキュメントにも表示できない フィールド ターミネータ を使用する必要があります。たとえば、一連の 4 つの 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
フォーマット ファイルを使用して、 Xmltable.dat
という XML データ ファイルの内容をインポートします。
BULK INSERT xTable
FROM 'C:\SampleFolder\Xmltable.dat'
WITH (FORMATFILE = 'C:\SampleFolder\Xmltable.fmt');
GO
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 エンコードを使用します。