Beispiele für den Massenimport und -export von XML-Dokumenten (SQL Server)

 

DIESES THEMA GILT FÜR:jaSQL Server (ab 2016)neinAzure SQL-DatenbankneinAzure SQL Data Warehouse neinParallel Data Warehouse


Sie können XML-Dokumente per Massenimport in eine SQL Server -Datenbank importieren bzw. aus einer SQL Server -Datenbank exportieren. Dieses Thema bietet Beispiele für diese beiden Situationen.

Verwenden Sie für den Massenimport von Daten aus einer Datendatei in eine SQL Server -Tabelle oder eine nicht partitionierte Sicht Folgendes:

  • bcp (Hilfsprogramm)
    Sie können das bcp -Hilfsprogramm auch verwenden, um Daten von einem beliebigen Speicherort in einer SQL Server -Datenbank zu exportieren, wo eine SELECT-Anweisung verwendet werden kann, einschließlich partitionierter Sichten.

  • BULK INSERT

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

Hinweis: Weitere Informationen finden Sie in den folgenden Themen

Es werden folgende Beispiele aufgeführt:

Geben Sie beim Massenimportieren von XML-Daten aus einer Datei mit einer Codierungsdeklaration, die Sie anwenden möchten, die Option SINGLE_BLOB in der OPENROWSET(BULK...)-Klausel an. Mit der Option SINGLE_BLOB stellen Sie sicher, dass der XML-Parser in SQL Server die Daten gemäß dem in der XML-Deklaration angegebenen Codierungsschema importiert.

Beispieltabelle

Zum Testen des unten aufgeführten Beispiels A erstellen Sie die folgende Beispieltabelle T.

USE tempdb  
CREATE TABLE T (IntCol int, XmlCol xml);  
GO  

Beispieldatendatei

Bevor Sie Beispiel A ausführen können, müssen Sie die UTF-8-codierte Datei (C:\SampleFolder\SampleData3.txt) erstellen, die die folgende Beispielinstanz enthält, die das UTF-8 -Codierungsschema angibt.

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

Beispiel A

In diesem Beispiel wird die SINGLE_BLOB -Option in einer INSERT ... SELECT * FROM OPENROWSET(BULK...) -Anweisung verwendet, um die Daten aus einer Datei namens SampleData3.txt zu importieren und eine XML-Instanz in eine Beispieltabelle mit einer einzelnen Spalte Teinzufügen.

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

Hinweise

Indem Sie SINGLE_BLOB verwenden, können Sie vermeiden, dass die Codierung des XML-Dokuments (wie in der XML-Codierungsdeklaration angegeben) und die Codierung der vom Server implizierten Codepage nicht übereinstimmen.

Wenn beim Verwenden der Datentypen NCLOB oder CLOB Codepage- oder Codierungskonflikte auftreten, führen Sie einen der folgenden Schritte aus:

  • Entfernen Sie die XML-Deklaration, um den Inhalt der XML-Datendatei erfolgreich zu importieren.

  • Geben Sie eine dem Codierungsschema der XML-Deklaration entsprechende Codepage in der CODEPAGE-Option der Abfrage an.

  • Stellen Sie eine Übereinstimmung oder Auflösung zwischen den Einstellungen der Datenbanksortierung und einem Nicht-Unicode-XML-Codierungsschema her.

[Nach oben]

In diesem Beispiel wird der OPENROWSET -Massenrowsetanbieter verwendet, um eine XML-Instanz einer vorhandenen Zeile bzw. vorhandenen Zeilen in der Beispieltabelle Thinzuzufügen.

System_CAPS_ICON_note.jpg Hinweis


Bevor Sie dieses Beispiel ausführen können, müssen Sie zunächst das Testskript aus Beispiel A abschließen. In Beispiel A wird nämlich die tempdb.dbo.T -Tabelle erstellt und der Massenimport von Daten aus SampleData3.txtdurchgeführt.

Beispieldatendatei

In Beispiel B wird eine veränderte Version der Beispieldatendatei SampleData3.txt aus dem vorhergehenden Beispiel verwendet. Ändern Sie den Inhalt dieser Datei wie folgt, um dieses Beispiel auszuführen:

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

Beispiel 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  

[Nach oben]

System_CAPS_ICON_important.jpg Wichtig


Es wird nicht empfohlen, die Unterstützung für DTDs (Document Type Definitions) zu aktivieren, es sei denn, dies ist in Ihrer XML-Umgebung erforderlich. Das Aktivieren der DTD-Unterstützung erhöht die Angriffsfläche Ihres Servers, und kann ihn einem Denial-of-Service-Angriff aussetzen. Wenn Sie die Unterstützung von DTDs aktivieren müssen, können Sie dieses Sicherheitsrisiko reduzieren, indem Sie ausschließlich vertrauenswürdige XML-Dokumente verarbeiten.

Wenn versucht wird, einen bcp -Befehl zum Importieren von XML-Daten aus einer Datei mit DTD zu verwenden, tritt möglicherweise ein Fehler wie der folgende auf:

"SQLState = 42000, NativeError = 6359"

"Error = [Microsoft][SQL Server Native Client][SQL Server]Das Analysieren von XML mit internen Teilmengen-DTDs ist unzulässig. Verwenden Sie CONVERT mit der Formatoption '2', um die begrenzte Unterstützung interner Teilmengen-DTDs zu ermöglichen."

"Fehler beim Kopieren von %s mit BCP"

Um dieses Problem zu umgehen, können Sie die XML-Daten aus einer Datendatei mit DTD importieren, indem Sie die OPENROWSET(BULK...) -Funktion verwenden und die CONVERT -Option in der SELECT -Klausel des Befehls angeben. Die Hauptsyntax für diesen Befehl lautet:

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

Beispieldatendatei

Bevor Sie dieses Beispiel für den Massenimport testen, müssen Sie zunächst die DateiC:\temp\Dtdfile.xmlerstellen, die die folgende Beispielinstanz enthält:

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

Beispieltabelle

In Beispiel C wird die Beispieltabelle T1 verwendet, die Sie durch die folgende CREATE TABLE -Anweisung erstellen:

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

Beispiel C

In diesem Beispiel wird mit OPENROWSET(BULK...) die CONVERT -Option in der SELECT -Klausel angegeben, um die XML-Daten aus Dtdfile.xml in die Beispieltabelle T1zu importieren.

INSERT T1  
  SELECT CONVERT(xml, BulkColumn, 2) FROM   
    OPENROWSET(Bulk 'c:\temp\Dtdfile.xml', SINGLE_BLOB) [rowsetresults];  

Nach dem Ausführen der INSERT-Anweisung wird die DTD aus der XML-Datei entfernt und in der Tabelle T1 gespeichert.

[Nach oben]

Im folgenden Beispiel wird gezeigt, wie das XML-Dokument Xmltable.datper Massenimport importiert wird.

Beispieldatendatei

Das Dokument in Xmltable.dat enthält zwei XML-Werte; einen für jede Zeile. Der erste XML-Wert ist UTF-16-codiert, der zweite ist UTF-8-codiert:

Der Inhalt dieser Datendatei wird im folgenden Hexadezimalabbild gezeigt:

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*  

Beispieltabelle

Beim Massenimport oder -export eines XML-Dokuments sollten Sie ein Feldabschlusszeichen verwenden, das keinesfalls in einem der Dokumente auftritt, beispielsweise eine Reihe von vier Nullen (\0) gefolgt vom Buchstaben z: \0\0\0\0z.

In diesem Beispiel wird gezeigt, wie Sie dieses Feldabschlusszeichen in der Beispieltabelle xTable verwenden. Verwenden Sie zum Erstellen dieser Beispieltabelle die folgende CREATE TABLE -Anweisung:

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

Beispielformatdatei

Das Feldabschlusszeichen muss in der Formatdatei angegeben werden. In Beispiel D wird eine Nicht-XML-Formatdatei namens Xmltable.fmt verwendet, die Folgendes enthält:

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

Sie können diese Formatdatei für den Massenimport von XML-Dokumenten in die xTable -Tabelle verwenden und dazu entweder einen bcp -Befehl oder eine BULK INSERT - oder eine INSERT ... SELECT * FROM OPENROWSET(BULK...) -Anweisung verwenden.

Beispiel D

In diesem Beispiel wird die Xmltable.fmt-Formatdatei in einer BULK INSERT-Anweisung verwendet, um den Inhalt einer XML-Datendatei namens Xmltable.dat zu importieren.

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

[Nach oben]

Im folgenden Beispiel werden XML-Daten mithilfe von bcp per Massenexport aus der im vorherigen Beispiel erstellten Tabelle exportiert. Dabei wird dieselbe XML-Formatdatei verwendet. Im folgenden bcp -Befehl stellen <server_name> und <instance_name> Platzhalter dar, die durch die entsprechenden Werte ersetzt werden müssen:

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

System_CAPS_ICON_note.jpg Hinweis


SQL Server speichert die XML-Codierung nicht, wenn XML-Daten in der Datenbank persistent gespeichert werden. Die ursprüngliche Codierung der XML-Felder ist also nicht mehr verfügbar, wenn die XML-Daten exportiert werden. SQL Server verwendet für den Export von XML-Daten die UTF-16-Codierung.

INSERT (Transact-SQL)
SELECT-Klausel (Transact-SQL)
bcp (Hilfsprogramm)
Massenimport und -export von Daten (SQL Server)
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

Community-Beiträge

HINZUFÜGEN
Anzeigen: