Udostępnij za pośrednictwem

Przykłady zbiorczego importowania i eksportowania dokumentów XML (SQL Server)

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowszych Azure SQL DatabaseAzure SQL Managed Instance

Możesz zbiorczo zaimportować dokumenty XML do bazy danych programu SQL Server lub wyeksportować je zbiorczo z bazy danych programu SQL Server. Ten artykuł zawiera przykłady obu tych elementów.

Aby zbiorczo zaimportować dane z pliku danych do tabeli programu SQL Server lub widoku bez partycji, możesz użyć następujących opcji:

  • narzędzie bcp

    Można również użyć narzędzia bcp do eksportowania danych z dowolnego miejsca bazy danych serwera SQL Server, gdzie działa instrukcja SELECT, w tym widoków partycjonowanych.

  • BULK INSERT

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

Aby uzyskać więcej informacji, zobacz następujące artykuły:

Przykłady

Zbiorcze importowanie danych XML jako strumienia bajtów binarnych

Podczas zbiorczego importowania danych XML z pliku zawierającego deklarację kodowania, którą chcesz zastosować, określ opcję SINGLE_BLOB w klauzuli OPENROWSET(BULK...). Opcja SINGLE_BLOB gwarantuje, że analizator XML w programie SQL Server importuje dane zgodnie ze schematem kodowania określonym w deklaracji XML.

Przykładowa tabela

Aby przetestować przykład A, utwórz przykładową tabelę T.

USE tempdb;
GO

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

Przykładowy plik danych

Przed uruchomieniem przykładu A należy utworzyć plik zakodowany w formacie UTF-8 (C:\SampleFolder\SampleData3.txt), który zawiera następujące przykładowe wystąpienie określające schemat kodowania UTF-8.

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

Przykład A

W tym przykładzie użyto opcji SINGLE_BLOB w instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...), aby zaimportować dane z pliku o nazwie SampleData3.txt i wstawić wystąpienie XML w tabeli jednokolumna, przykładowej tabeli T.

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

Możesz również jawnie określić nazwy kolumn w następujący sposób:

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

Uwagi

Korzystając z SINGLE_BLOB w tym przypadku, można uniknąć niezgodności między kodowaniem dokumentu XML (określonym przez deklarację kodowania XML) i strony kodowej ciągu implikowanej przez serwer.

Jeśli używasz typów danych NCLOB lub CLOB i napotkasz konflikt kodu lub kodowania, musisz wykonać jedną z następujących czynności:

  • Usuń deklarację XML, aby pomyślnie zaimportować zawartość pliku danych XML.

  • Określ stronę kodową w opcji CODEPAGE zapytania, która zgadza się ze schematem kodowania używanym w deklaracji XML.

  • Dopasuj lub rozwiąż ustawienia sortowania bazy danych ze schematem kodowania XML innego niż Unicode.

[Top]

Zbiorcze importowanie danych XML w istniejącym wierszu

W tym przykładzie użyto dostawcy zbiorczego zestawu wierszy OPENROWSET, aby dodać wystąpienie XML do istniejącego wiersza lub wierszy w przykładowej tabeli T.

Notatka

Aby uruchomić ten przykład, należy najpierw ukończyć skrypt testowy podany w przykładzie A. W tym przykładzie tworzona jest tabela tempdb.dbo.T i zbiorcze importowanie danych z SampleData3.txt.

Przykładowy plik danych

Przykład B używa zmodyfikowanej wersji przykładowego pliku danych SampleData3.txt z poprzedniego przykładu. Aby uruchomić ten przykład, zmodyfikuj zawartość tego pliku w następujący sposób:

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

Przykład 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]

Zbiorcze importowanie danych XML z pliku zawierającego dtD

Ważny

Zalecamy, aby nie włączać obsługi definicji typów dokumentów (DTD), jeśli nie jest to wymagane w środowisku XML. Włączenie obsługi DTD zwiększa powierzchnię podatną na ataki twojego serwera i może narazić go na atak typu "odmowa usługi". Jeśli musisz włączyć obsługę dtD, możesz zmniejszyć to zagrożenie bezpieczeństwa, przetwarzając tylko zaufane dokumenty XML.

Podczas używania bcp do importowania danych XML z pliku zawierającego dtD może wystąpić następujący błąd:

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

Aby obejść ten problem, można zaimportować dane XML z pliku danych zawierającego dtD przy użyciu funkcji OPENROWSET(BULK...), a następnie określić opcję CONVERT w klauzuli SELECT polecenia. Podstawowa składnia polecenia to:

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

Przykładowy plik danych

Przed przetestowaniem tego przykładu importu zbiorczego utwórz plik (C:\SampleFolder\Dtdfile.xml), który zawiera następujące przykładowe wystąpienie:

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

Przykładowa tabela

Przykładowy język C używa przykładowej tabeli T1 utworzonej przez następującą instrukcję CREATE TABLE:

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

Przykład C

W tym przykładzie użyto OPENROWSET(BULK...) i określono opcję CONVERT w klauzuli SELECT w celu zaimportowania danych XML z Dtdfile.xml do przykładowej tabeli T1.

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

Po wykonaniu instrukcji INSERT jednostka DTD jest usuwana z kodu XML i przechowywana w tabeli T1.

[Top]

Jawne określanie terminatora pola przy użyciu pliku formatu

W poniższym przykładzie pokazano, jak zbiorczo zaimportować następujący dokument XML, Xmltable.dat.

Przykładowy plik danych

Dokument w Xmltable.dat zawiera dwie wartości XML, po jednym dla każdego wiersza. Pierwsza wartość XML jest kodowana przy użyciu protokołu UTF-16, a druga wartość jest kodowana przy użyciu protokołu UTF-8.

Zawartość tego pliku danych jest przedstawiona w następującym zrzucie szesnastkowym.

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*

Przykładowa tabela

Podczas zbiorczego importowania lub eksportowania dokumentu XML należy użyć terminatora pola, które nie mogą być wyświetlane w żadnym z dokumentów; na przykład seria czterech wartości null (\0), po której następuje litera z: \0\0\0\0z.

W tym przykładzie pokazano, jak używać tego terminatora pola dla przykładowej tabeli xTable. Aby utworzyć tę przykładową tabelę, użyj następującej instrukcji CREATE TABLE:

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

Przykładowy plik formatu

Terminator pola musi być określony w pliku formatującym. Przykład D używa pliku formatu innego niż XML o nazwie Xmltable.fmt zawierającego następujące dane wyjściowe:

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

Ten plik formatu służy do zbiorczego importowania dokumentów XML do tabeli xTable przy użyciu polecenia bcp lub instrukcji BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK...).

Przykład D

W tym przykładzie użyto pliku formatu Xmltable.fmt w instrukcji BULK INSERT w celu zaimportowania zawartości pliku danych XML o nazwie Xmltable.dat.

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

[Top]

Zbiorcze eksportowanie danych XML

W poniższym przykładzie użyto bcp do zbiorczego eksportowania danych XML z tabeli utworzonej w poprzednim przykładzie przy użyciu tego samego pliku w formacie XML. W poniższym poleceniu bcp<server_name> i <instance_name> reprezentują symbole zastępcze, które muszą zostać zastąpione odpowiednimi wartościami:

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

Notatka

Program SQL Server nie zapisuje kodowania XML, gdy dane XML są utrwalane w bazie danych. W związku z tym oryginalne kodowanie pól XML nie jest dostępne podczas eksportowania danych XML. Program SQL Server używa kodowania UTF-16 podczas eksportowania danych XML.

Zobacz też