Exempel på massimport och export av XML-dokument (SQL Server)

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed Instance

Du kan massimportera XML-dokument till en SQL Server-databas eller massexportera dem från en SQL Server-databas. Den här artikeln innehåller exempel på båda.

Om du vill massimportera data från en datafil till en SQL Server-tabell eller en icke-partitionerad vy kan du använda följande alternativ:

• bcp-verktyg

  Du kan också använda verktyget bcp för att exportera data var som helst i en SQL Server-databas som en SELECT-instruktion fungerar, inklusive partitionerade vyer.

• BULK INSERT

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

Mer information finns i följande artiklar:

• Importera och exportera massdata med bcp (SQL Server)
• Använd BULK INSERT eller OPENROWSET(BULK...) för att importera data till SQL Server-
• Så importerar du XML till SQL Server med komponenten XML Bulk Load
• XML-schemasamlingar (SQL Server)

Exempel

• A. Massimportera XML-data som en binär byteström
• B. Massimportera XML-data på en befintlig rad
• C. Massimportera XML-data från en fil som innehåller en DTD
• D. Ange fältavgränsaren explicit med hjälp av en formatfil
• E. Massexport av XML-data

Massimportera XML-data som en binär byteström

När du massimporterar XML-data från en fil som innehåller en kodningsdeklaration som du vill använda anger du alternativet SINGLE_BLOB i OPENROWSET(BULK...)-satsen. Alternativet SINGLE_BLOB säkerställer att XML-parsaren i SQL Server importerar data enligt kodningsschemat som anges i XML-deklarationen.

Exempeltabell

Om du vill testa exempel A skapar du exempeltabellen T.

USE tempdb;
GO

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

Exempeldatafil

Innan du kan köra exempel A måste du skapa en UTF-8-kodad fil (C:\SampleFolder\SampleData3.txt) som innehåller följande exempelinstans som anger UTF-8 kodningsschema.

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

Exempel A

I det här exemplet används alternativet SINGLE_BLOB i en INSERT ... SELECT * FROM OPENROWSET(BULK...)-instruktion för att importera data från en fil med namnet SampleData3.txt och infoga en XML-instans i tabellen med en kolumn, exempeltabellen T.

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

Du kan också uttryckligen ange kolumnnamnen på följande sätt:

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

Anmärkningar

Genom att använda SINGLE_BLOB i det här fallet kan du undvika ett matchningsfel mellan kodningen av XML-dokumentet (enligt XML-kodningsdeklarationen) och strängkodsidan som servern antyder.

Om du använder NCLOB- eller CLOB-datatyper och stöter på en kodningskonflikt, måste du göra något av följande:

• Ta bort XML-deklarationen för att importera innehållet i XML-datafilen.

• Ange en kodsida i alternativet CODEPAGE för frågan som matchar kodningsschemat som används i XML-deklarationen.

• Matcha eller lös databassorteringsinställningarna med ett XML-kodningsschema som inte är Unicode.

[Top]

Massimportera XML-data på en befintlig rad

I det här exemplet används bulk rowset-leverantören OPENROWSET för att lägga till en XML-instans till en befintlig rad eller rader i exempeltabellen T.

Anteckning

Om du vill köra det här exemplet måste du först slutföra testskriptet i exempel A. Det här exemplet skapar tabellen tempdb.dbo.T och massimporter data från SampleData3.txt.

Exempeldatafil

Exempel B använder en modifierad version av SampleData3.txt exempeldatafil från föregående exempel. Om du vill köra det här exemplet ändrar du innehållet i den här filen på följande sätt:

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

Exempel 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]

Massimportera XML-data från en fil som innehåller en DTD

Viktig

Vi rekommenderar att du inte aktiverar stöd för dokumenttypsdefinitioner (DTD) om det inte krävs i XML-miljön. Om du aktiverar DTD-stöd ökar den attackbara ytan på servern och kan utsätta den för en överbelastningsattack. Om du måste aktivera DTD-stöd kan du minska den här säkerhetsrisken genom att endast bearbeta betrodda XML-dokument.

Du kan få följande fel när du använder bcp för att importera XML-data från en fil som innehåller en 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

Du kan undvika det här problemet genom att importera XML-data från en datafil som innehåller en DTD med hjälp av funktionen OPENROWSET(BULK...) och sedan ange alternativet CONVERT i kommandots SELECT-sats. Den grundläggande syntaxen för kommandot är:

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

Exempeldatafil

Innan du kan testa det här massimportexemplet skapar du en fil (C:\SampleFolder\Dtdfile.xml) som innehåller följande exempelinstans:

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

Exempeltabell

Exempel C använder den T1 exempeltabell som skapas av följande CREATE TABLE-instruktion:

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

Exempel C

Det här exemplet använder OPENROWSET(BULK...) och anger alternativet CONVERT i satsen SELECT för att importera XML-data från Dtdfile.xml till exempeltabellen T1.

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

När INSERT-instruktionen har körts tas DTD bort från XML och lagras i tabellen T1.

[Top]

Ange fältavgränsaren explicit med hjälp av en formatfil

I följande exempel visas hur du massimportera följande XML-dokument Xmltable.dat.

Exempeldatafil

Dokumentet i Xmltable.dat innehåller två XML-värden, ett för varje rad. Det första XML-värdet kodas med UTF-16 och det andra värdet kodas med UTF-8.

Innehållet i den här datafilen visas i följande hexdumpning:

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*

Exempeltabell

När du massimportar eller exporterar ett XML-dokument bör du använda en fältavgränsare som inte kan visas i något av dokumenten. Till exempel en serie med fyra nullvärden (\0) följt av bokstaven z: \0\0\0\0z.

Det här exemplet visar hur du använder den här fältterminatorn för exempeltabellen xTable. Om du vill skapa den här exempeltabellen använder du följande CREATE TABLE-instruktion:

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

Exempelformatfil

Fältavgränsaren måste anges i formatfilen. Exempel D använder en fil med icke-XML-format med namnet Xmltable.fmt som innehåller följande utdata:

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

Du kan använda den här formatfilen för att massimportera XML-dokument till xTable-tabellen med hjälp av ett bcp-kommando eller en BULK INSERT- eller INSERT ... SELECT * FROM OPENROWSET(BULK...)-instruktion.

Exempel D

I det här exemplet används Xmltable.fmt-formatfilen i en BULK INSERT-instruktion för att importera innehållet i en XML-datafil med namnet Xmltable.dat.

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

[Top]

Massexport av XML-data

I följande exempel används bcp- för att massexportera XML-data från tabellen som skapas i föregående exempel med hjälp av samma XML-formatfil. I följande bcp kommando representerar <server_name> och <instance_name> platshållare som måste ersättas med lämpliga värden:

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

Anteckning

SQL Server sparar inte XML-kodningen när XML-data sparas i databasen. Därför är den ursprungliga kodningen av XML-fält inte tillgänglig när XML-data exporteras. SQL Server använder UTF-16-kodning vid export av XML-data.

Se även

• INSERT (Transact-SQL)
• SELECT-klausul (Transact-SQL)
• bcp-verktyg
• massimport och export av data (SQL Server)
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)