Contoh impor dan ekspor massal dokumen XML (SQL Server)

  • Artikel

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

Anda bisa 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 yang berfungsi oleh SELECT pernyataan, termasuk tampilan yang dipartisi.

  • BULK INSERT

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

Untuk informasi lebih lanjut, baca artikel berikut:

Contoh

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 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 CODEPAGE opsi kueri yang cocok dengan skema pengodean yang digunakan dalam deklarasi XML.

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

[Atas]

Mengimpor data XML secara massal di baris yang sudah ada

Contoh ini menggunakan penyedia set 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 SampleData3.txt file data sampel yang 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 Jenis 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 klausa 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.

[Atas]

Tentukan terminator bidang secara eksplisit menggunakan file format

Contoh berikut menunjukkan cara mengimpor dokumen XML berikut secara massal, 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.

Isi 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 tidak mungkin 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 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 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.

Lihat juga