Delen via

Voorbeelden van bulksgewijs importeren en exporteren van XML-documenten (SQL Server)

  • Artikel

van toepassing op: SQL Server 2016 (13.x) en hoger Azure SQL DatabaseAzure SQL Managed Instance

U kunt XML-documenten bulksgewijs importeren in een SQL Server-database of ze bulksgewijs exporteren vanuit een SQL Server-database. Dit artikel bevat voorbeelden van beide.

Als u gegevens bulksgewijs wilt importeren uit een gegevensbestand in een SQL Server-tabel of niet-gepartitioneerde weergave, kunt u de volgende opties gebruiken:

  • bcp-hulpprogramma voor

    U kunt ook het hulpprogramma bcp gebruiken om gegevens te exporteren vanaf elke locatie in een SQL Server-database waarin een SELECT instructie werkt, inclusief gepartitioneerde weergaven.

  • BULK INSERT

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

Zie de volgende artikelen voor meer informatie:

Voorbeelden

XML-gegevens bulksgewijs importeren als een binaire bytestroom

Wanneer u XML-gegevens bulksgewijs importeert uit een bestand dat een coderingsdeclaratie bevat die u wilt toepassen, geeft u de optie SINGLE_BLOB op in de OPENROWSET(BULK...) component. De optie SINGLE_BLOB zorgt ervoor dat de XML-parser in SQL Server de gegevens importeert volgens het coderingsschema dat is opgegeven in de XML-declaratie.

Voorbeeldtabel

Als u voorbeeld A wilt testen, maakt u een voorbeeldtabel T.

USE tempdb;
GO

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

Voorbeeldgegevensbestand

Voordat u voorbeeld A kunt uitvoeren, moet u een met UTF-8 gecodeerd bestand (C:\SampleFolder\SampleData3.txt) maken dat het volgende voorbeeldexemplaren bevat dat het UTF-8 coderingsschema aangeeft.

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

Voorbeeld A

In dit voorbeeld wordt de optie SINGLE_BLOB in een INSERT ... SELECT * FROM OPENROWSET(BULK...)-instructie gebruikt om gegevens te importeren uit een bestand met de naam SampleData3.txt en een XML-exemplaar in te voegen in de tabel met één kolom, voorbeeldtabel T.

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

U kunt ook expliciet de kolomnamen als volgt opgeven:

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

Opmerkingen

Met behulp van SINGLE_BLOB in dit geval kunt u voorkomen dat de codering van het XML-document (zoals opgegeven door de XML-coderingsdeclaratie) en de tekenreekscodepagina die door de server wordt geïmpliceerd, niet overeenkomen.

Als u NCLOB- of CLOB-gegevenstypen gebruikt en te maken krijgt met een codepagina of coderingsconflict, moet u een van de volgende handelingen uitvoeren:

  • Verwijder de XML-declaratie om de inhoud van het XML-gegevensbestand succesvol te importeren.

  • Geef een codepagina op in de CODEPAGE optie van de query die overeenkomt met het coderingsschema dat wordt gebruikt in de XML-declaratie.

  • De databasesorteringsinstellingen vergelijken of oplossen met een niet-Unicode XML-coderingsschema.

[Boven]

XML-gegevens bulksgewijs importeren in een bestaande rij

In dit voorbeeld wordt de OPENROWSET provider voor bulkrijsets gebruikt om een XML-exemplaar toe te voegen aan een bestaande rij of rijen in de voorbeeldtabel T.

Notitie

Als u dit voorbeeld wilt uitvoeren, moet u eerst het testscript in voorbeeld A voltooien. In dat voorbeeld wordt de tempdb.dbo.T tabel gemaakt en worden gegevens bulksgewijs geïmporteerd uit SampleData3.txt.

Voorbeeldgegevensbestand

Voorbeeld B maakt gebruik van een gewijzigde versie van het SampleData3.txt voorbeeldgegevensbestand uit het voorgaande voorbeeld. Als u dit voorbeeld wilt uitvoeren, wijzigt u de inhoud van dit bestand als volgt:

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

Voorbeeld 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

[Boven]

XML-gegevens bulksgewijs importeren uit een bestand dat een DTD bevat

Belangrijk

Het is raadzaam om geen ondersteuning in te schakelen voor documenttypedefinities (DTD's) als dit niet vereist is in uw XML-omgeving. Als u DTD-ondersteuning inschakelt, wordt het aanvulbare oppervlak van uw server vergroot en kan deze mogelijk worden blootgesteld aan een DoS-aanval. Als u DTD-ondersteuning moet inschakelen, kunt u dit beveiligingsrisico beperken door alleen vertrouwde XML-documenten te verwerken.

Mogelijk krijgt u de volgende fout wanneer u bcp- gebruikt om XML-gegevens te importeren uit een bestand dat een DTD bevat:

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

U kunt dit probleem omzeilen door XML-gegevens te importeren uit een gegevensbestand dat een DTD bevat met behulp van de functie OPENROWSET(BULK...) en vervolgens de optie CONVERT op te geven in de SELECT component van de opdracht. De basissyntaxis voor de opdracht is:

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

Voorbeeldgegevensbestand

Voordat u dit voorbeeld van bulkimport kunt testen, maakt u een bestand (C:\SampleFolder\Dtdfile.xml) dat het volgende voorbeeldexemplaren bevat:

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

Voorbeeldtabel

Voorbeeld C maakt gebruik van de T1 voorbeeldtabel die is gemaakt met de volgende CREATE TABLE-instructie:

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

Voorbeeld C

In dit voorbeeld wordt OPENROWSET(BULK...) gebruikt en wordt de optie CONVERT in de SELECT component opgegeven om de XML-gegevens uit Dtdfile.xml in de voorbeeldtabel T1te importeren.

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

Nadat de INSERT-instructie is uitgevoerd, wordt de DTD verwijderd uit de XML en opgeslagen in de T1 tabel.

[Boven]

Geef het veldeindteken expliciet op met behulp van een indelingsbestand

In het volgende voorbeeld ziet u hoe u het volgende XML-document bulksgewijs importeert Xmltable.dat.

Voorbeeldgegevensbestand

Het document in Xmltable.dat bevat twee XML-waarden, één voor elke rij. De eerste XML-waarde wordt gecodeerd met UTF-16 en de tweede waarde wordt gecodeerd met UTF-8.

De inhoud van dit gegevensbestand wordt weergegeven in de volgende hexdump:

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*

Voorbeeldtabel

Wanneer u een XML-document bulksgewijs importeert of exporteert, moet u een veldeindteken gebruiken dat niet mogelijk in een van de documenten kan worden weergegeven; Bijvoorbeeld een reeks van vier null-waarden (\0) gevolgd door de letter z: \0\0\0\0z.

In dit voorbeeld ziet u hoe u dit veldeindteken gebruikt voor de xTable voorbeeldtabel. Gebruik de volgende CREATE TABLE instructie om deze voorbeeldtabel te maken:

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

Voorbeeldbestandindeling

Het veldbegrenzingsteken moet worden opgegeven in het indelingsbestand. Voorbeeld D maakt gebruik van een bestand met een niet-XML-indeling met de naam Xmltable.fmt die de volgende uitvoer bevat:

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

U kunt dit indelingsbestand gebruiken om XML-documenten bulksgewijs te importeren in de xTable tabel met behulp van een bcp opdracht of een BULK INSERT of INSERT ... SELECT * FROM OPENROWSET(BULK...) instructie.

Voorbeeld D

In dit voorbeeld wordt het Xmltable.fmt-indelingformaat gebruikt in een BULK INSERT-instructie om de inhoud van een XML-gegevensbestand met de naam Xmltable.datte importeren.

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

[Boven]

XML-gegevens bulksgewijs exporteren

In het volgende voorbeeld wordt bcp- gebruikt om XML-gegevens bulksgewijs te exporteren uit de tabel die in het vorige voorbeeld is gemaakt met behulp van hetzelfde XML-indelingsbestand. In de volgende bcp opdracht vertegenwoordigen <server_name> en <instance_name> tijdelijke aanduidingen die moeten worden vervangen door de juiste waarden:

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

Notitie

SQL Server slaat de XML-codering niet op wanneer XML-gegevens in de database worden opgeslagen. Daarom is de oorspronkelijke codering van XML-velden niet beschikbaar wanneer XML-gegevens worden geëxporteerd. SQL Server maakt gebruik van UTF-16-codering bij het exporteren van XML-gegevens.

Zie ook