Contoh impor dan ekspor massal dokumen XML (SQL Server)

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed Instance

Anda dapat mengimpor dokumen XML secara massal ke database SQL Server, atau mengekspornya secara massal dari database SQL Server. Artikel ini menyediakan contoh keduanya.

Untuk mengimpor data secara massal dari file data ke dalam tabel SQL Server atau tampilan non-partisi, Anda bisa menggunakan opsi berikut:

• utilitas bcp

  Anda juga dapat menggunakan utilitas bcp untuk mengekspor data dari mana saja dalam database SQL Server tempat SELECT pernyataan berfungsi, termasuk tampilan yang dipartisi.

• BULK INSERT

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

Untuk informasi lebih lanjut, baca artikel berikut:

• Mengimpor dan mengekspor data massal menggunakan bcp (SQL Server)
• Gunakan BULK INSERT atau OPENROWSET(BULK...) untuk mengimpor data ke SQL Server
• Cara mengimpor XML ke SQL Server dengan komponen Pemuatan Massal XML
• Koleksi skema XML (SQL Server)

Contoh

• A. Mengimpor data XML secara massal sebagai aliran byte biner
• B. Mengimpor data XML secara massal di baris yang sudah ada
• C. Mengimpor data XML secara massal dari file yang berisi DTD
• D. Menentukan terminator kolom secara eksplisit menggunakan format file
• E. Mengekspor data XML secara massal

Mengimpor data XML secara massal sebagai aliran byte biner

Saat Anda mengimpor data XML secara massal dari file yang berisi deklarasi pengodean yang ingin Anda terapkan, tentukan SINGLE_BLOB opsi dalam OPENROWSET(BULK...) klausa. Opsi ini SINGLE_BLOB memastikan bahwa pengurai XML di SQL Server mengimpor data sesuai dengan skema pengodean yang ditentukan dalam deklarasi XML.

Tabel sampel

Untuk menguji contoh A, buat tabel Tsampel .

USE tempdb;
GO

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

File data contoh

Sebelum dapat menjalankan contoh A, Anda harus membuat file yang dikodekan UTF-8 (C:\SampleFolder\SampleData3.txt) yang berisi contoh instans UTF-8 berikut yang menentukan skema pengodean.

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

Contoh A

Contoh ini menggunakan opsi SINGLE_BLOB dalam pernyataan INSERT ... SELECT * FROM OPENROWSET(BULK...) untuk mengimpor data dari file bernama SampleData3.txt dan menyisipkan sebuah instans XML ke dalam tabel kolom tunggal, tabel sampel T.

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

Anda juga dapat secara eksplisit menentukan nama kolom sebagai berikut:

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

Keterangan

Dengan menggunakan SINGLE_BLOB dalam hal ini, Anda dapat menghindari ketidakcocokan antara pengodean dokumen XML (seperti yang ditentukan oleh deklarasi pengodean XML) dan halaman kode string yang disiratkan oleh server.

Jika Anda menggunakan jenis data NCLOB atau CLOB dan mengalami konflik codepage atau pengodean, Anda harus melakukan salah satu hal berikut:

• Hapus deklarasi XML agar berhasil mengimpor konten file data XML.

• Tentukan halaman kode dalam CODEPAGE opsi kueri yang cocok dengan skema pengodean yang digunakan dalam deklarasi XML.

• Cocokkan, atau atasi, pengaturan kolade database dengan skema pengodean XML non-Unicode.

[Atas]

Mengimpor data XML secara massal di baris yang sudah ada

Contoh ini menggunakan penyedia himpunan baris massal untuk menambahkan instans OPENROWSET XML ke baris atau baris yang sudah ada dalam tabel Tsampel .

Catatan

Untuk menjalankan contoh ini, Anda harus terlebih dahulu menyelesaikan skrip pengujian yang disediakan dalam contoh A. Contoh tersebut tempdb.dbo.T membuat tabel dan mengimpor data secara massal dari SampleData3.txt.

File data contoh

Contoh B menggunakan versi file data sampel yang SampleData3.txt dimodifikasi dari contoh sebelumnya. Untuk menjalankan contoh ini, ubah konten file ini sebagai berikut:

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

Contoh 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

[Atas]

Mengimpor data XML secara massal dari file yang berisi DTD

Penting

Kami menyarankan agar Anda tidak mengaktifkan dukungan untuk Definisi Tipe Dokumen (DTD) jika tidak diperlukan di lingkungan XML Anda. Mengaktifkan dukungan DTD meningkatkan area permukaan server Anda yang dapat diserang, dan dapat mengeksposnya ke serangan penolakan layanan. Jika Anda harus mengaktifkan dukungan DTD, Anda dapat mengurangi risiko keamanan ini dengan hanya memproses dokumen XML tepercaya.

Anda mungkin mendapatkan kesalahan berikut, saat Anda menggunakan bcp untuk mengimpor data XML dari file yang berisi DTD:

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

Untuk mengatasi masalah ini, Anda dapat mengimpor data XML dari file data yang berisi DTD dengan menggunakan OPENROWSET(BULK...) fungsi lalu menentukan CONVERT opsi dalam SELECT klausul perintah. Sintaks dasar untuk perintah adalah:

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

File data contoh

Sebelum Anda dapat menguji contoh impor massal ini, buat file (C:\SampleFolder\Dtdfile.xml) yang berisi contoh instans berikut:

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

Tabel sampel

Contoh C menggunakan T1 tabel sampel yang dibuat oleh pernyataan berikut CREATE TABLE :

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

Contoh C

Contoh ini menggunakan OPENROWSET(BULK...) dan menentukan opsi dalam CONVERT klausul SELECT untuk mengimpor data XML dari Dtdfile.xml ke dalam tabel T1sampel .

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

INSERT Setelah pernyataan dijalankan, DTD dilucuti dari XML dan disimpan dalam T1 tabel.

[Atas]

Tentukan terminator bidang secara eksplisit menggunakan file format

Contoh berikut menunjukkan cara mengimpor dokumen XML secara massal berikut, Xmltable.dat.

File data contoh

Dokumen di Xmltable.dat berisi dua nilai XML, satu untuk setiap baris. Nilai XML pertama dikodekan dengan UTF-16, dan nilai kedua dikodekan dengan UTF-8.

Konten file data ini diperlihatkan dalam cadangan hex berikut:

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*

Tabel sampel

Saat mengimpor atau mengekspor dokumen XML secara massal, Anda harus menggunakan pembatas bidang yang tidak mungkin muncul di salah satu dokumen; misalnya, serangkaian empat null (\0) diikuti dengan huruf z: \0\0\0\0z.

Contoh ini menunjukkan cara menggunakan terminator bidang untuk tabel sampel xTable. Untuk membuat tabel sampel ini, gunakan pernyataan berikut CREATE TABLE :

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

Format file contoh

Pemisah bidang harus ditentukan dalam berkas format. Contoh D menggunakan file format non-XML bernama Xmltable.fmt yang berisi output berikut:

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

Anda dapat menggunakan file format ini untuk mengimpor dokumen XML secara massal ke tabel xTable dengan menggunakan perintah bcp atau pernyataan BULK INSERT atau INSERT ... SELECT * FROM OPENROWSET(BULK...).

Contoh D

Contoh ini menggunakan Xmltable.fmt file format dalam BULK INSERT pernyataan untuk mengimpor konten file data XML bernama Xmltable.dat.

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

[Atas]

Mengekspor data XML secara massal

Contoh berikut menggunakan bcp untuk mengekspor data XML secara massal dari tabel yang dibuat dalam contoh sebelumnya dengan menggunakan file format XML yang sama. Dalam perintah berikut bcp<server_name> dan <instance_name> mewakili placeholder yang harus diganti dengan nilai yang sesuai.

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

Catatan

SQL Server tidak menyimpan pengodean XML saat data XML disimpan dalam database. Oleh karena itu, pengodean asli bidang XML tidak tersedia saat data XML diekspor. SQL Server menggunakan pengodean UTF-16 saat mengekspor data XML.

Lihat juga

• INSERT (Transact-SQL)
• Klausul SELECT (Transact-SQL)
• utilitas bcp
• Impor dan Ekspor Data Massal (SQL Server)
• SISIPAN MASSAL (Transact-SQL)
• OPENROWSET (T-SQL)