Contoh impor dan ekspor massal dokumen XML (SQL Server)
Berlaku untuk: 
SQL Server 2016 (13.x) dan Azure SQL Database Azure SQL Managed Instance yang lebih baru
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
SELECTpernyataan berfungsi, termasuk tampilan yang dipartisi.BULK INSERTINSERT ... 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
- J. 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 bidang secara eksplisit menggunakan file format
- 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 sampel
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 dalam INSERT ... SELECT * FROM OPENROWSET(BULK...) pernyataan untuk mengimpor data dari file bernama SampleData3.txt dan menyisipkan instans SINGLE_BLOB XML dalam tabel kolom tunggal, tabel Tsampel .
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
CODEPAGEopsi kueri yang cocok dengan skema pengodean yang digunakan dalam deklarasi XML.Cocokkan, atau atasi, pengaturan kolade database dengan skema pengodean XML non-Unicode.
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 sampel
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
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 sampel
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 SELECT klausul CONVERT 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.
Tentukan terminator bidang secara eksplisit menggunakan file format
Contoh berikut menunjukkan cara mengimpor dokumen XML secara massal berikut, Xmltable.dat.
File data sampel
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 terminator bidang yang mungkin tidak dapat muncul di salah satu dokumen; misalnya, serangkaian empat null (\0) diikuti dengan huruf z: \0\0\0\0z.
Contoh ini memperlihatkan cara menggunakan terminator bidang ini untuk xTable tabel sampel. Untuk membuat tabel sampel ini, gunakan pernyataan berikut CREATE TABLE :
USE tempdb;
GO
CREATE TABLE xTable (xCol XML);
GO
File format sampel
Terminator bidang harus ditentukan dalam file 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 xTable dalam tabel dengan menggunakan bcp perintah atau BULK INSERT pernyataan.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
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 tempat penampung 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.