Esempi di importazione ed esportazione bulk di documenti XML (SQL Server)

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL diIstanza gestita di SQL di Azure

È possibile eseguire importazioni in blocco di documenti XML in un database di SQL Server o esportazioni in blocco da un database di SQL Server. In questo articolo vengono forniti esempi di entrambe le situazioni.

Per l'importazione in blocco di dati da un file di dati a una tabella di SQL Server o a una vista non partizionata, è possibile usare le seguenti opzioni:

  • utilità bcp

    È possibile usare l'utilità bcp anche per esportare dati da qualunque posizione in un database di SQL Server su cui sia possibile usare l'istruzione SELECT, incluse le viste partizionate.

  • BULK INSERT

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

Per altre informazioni, vedere gli articoli seguenti:

Esempi

Importazione bulk di dati XML come flusso di byte binario

Quando si esegue un'importazione in blocco di dati XML da un file contenente una dichiarazione di codifica che si desidera applicare, specificare l'opzione SINGLE_BLOB nella clausola OPENROWSET(BULK...). L'opzione SINGLE_BLOB garantisce che il parser XML di SQL Server importi i dati in base allo schema di codifica specificato nella dichiarazione XML.

Tabella di esempio

Per testare l'esempio A, creare la tabella di esempio T.

USE tempdb;
GO

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

File di dati di esempio

Prima di eseguire l'esempio A, è necessario creare un file con codifica UTF-8 (C:\SampleFolder\SampleData3.txt) contenente l'istanza di esempio seguente che specifica lo schema di codifica UTF-8 .

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

Esempio A

In questo esempio viene utilizzata l'opzione SINGLE_BLOB in un'istruzione INSERT ... SELECT * FROM OPENROWSET(BULK...) per importare dati da un file denominato SampleData3.txt e inserire un'istanza XML in una tabella a colonna singola, ovvero la tabella di esempio T.

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

È inoltre possibile specificare in modo esplicito i nomi delle colonne come indicato di seguito:

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

Osservazioni:

L'utilizzo di SINGLE_BLOB in questo caso consente di evitare una mancata corrispondenza tra la codifica del documento XML (come specificata dalla dichiarazione di codifica XML) e la tabella codici della stringa implicita del server.

Se si utilizzano tipi di dati NCLOB o CLOB e si verifica un conflitto di tabella codici o di codifica, è necessario eseguire una delle operazioni seguenti:

  • Rimuovere la dichiarazione XML per importare correttamente il contenuto del file di dati XML.

  • Specificare una tabella codici nell'opzione CODEPAGE della query corrispondente allo schema di codifica utilizzato nella dichiarazione XML.

  • Verificare la corrispondenza o risolvere le impostazioni delle regole di confronto del database con uno schema di codifica XML non Unicode.

[Top]

Importazione bulk di dati XML in una riga esistente

In questo esempio viene utilizzato il provider di set di righe con lettura bulk OPENROWSET per aggiungere un'istanza XML a una o più righe esistenti nella tabella di esempio T.

Nota

Per eseguire questo esempio è necessario innanzitutto completare lo script di prova fornito nell'esempio A, nel quale viene creata la tabella tempdb.dbo.T e viene eseguita l'importazione bulk di dati da SampleData3.txt.

File di dati di esempio

Nell'esempio B viene utilizzata una versione modificata del file di dati di esempio SampleData3.txt dell'esempio precedente. Per eseguire questo esempio, modificare il contenuto del file nel modo seguente:

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

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

Importazione bulk di dati XML da un file contenente una definizione DTD

Importante

È consigliabile non abilitare il supporto per le definizioni DTD (Document Type Definition) se non è necessario nell'ambiente XML utilizzato. L'attivazione del supporto DTD aumenta la superficie di attacco del server esposta a rischi e può esporre quest'ultimo a un attacco Denial-of-Service. Se è necessario abilitare il supporto DTD, è possibile ridurre questo rischio per la sicurezza limitando l'elaborazione a documenti XML attendibili.

Quando si usa bcp per importare dati XML da un file contenente un DTD, è possibile che venga visualizzato il seguente errore:

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

Per risolvere il problema, è possibile importare dati XML da un file di dati contenente una definizione DTD utilizzando la funzione OPENROWSET(BULK...) e specificando quindi l'opzione CONVERT nella clausola SELECT del comando. La sintassi di base per il comando è la seguente:

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

File di dati di esempio

Prima di testare questo esempio di importazione bulk, creare un file (C:\SampleFolder\Dtdfile.xml) contenente l'istanza di esempio seguente:

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

Tabella di esempio

Nell'esempio C viene utilizzata la tabella di esempio T1 creata mediante l'istruzione CREATE TABLE seguente:

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

Esempio C

In questo esempio viene utilizzata la funzione OPENROWSET(BULK...) e viene specificata l'opzione CONVERT nella clausola SELECT per importare i dati XML da Dtdfile.xml nella tabella di esempio T1.

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

Dopo l'esecuzione dell'istruzione INSERT , la definizione DTD viene rimossa dai dati XML e archiviata nella tabella T1 .

[Top]

Specificare il carattere di terminazione del campo in modo esplicito tramite un file di formato

Nell'esempio seguente viene descritta la procedura per l'importazione bulk del documento XML Xmltable.dat.

File di dati di esempio

Il documento in Xmltable.dat contiene due valori XML, uno per ogni riga. Per il primo valore XML è stata utilizzata la codifica UTF-16, mentre per il secondo valore è stata utilizzata la codifica UTF-8.

Il contenuto di questo file di dati è illustrato nel seguente backup hex:

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*

Tabella di esempio

Per l'importazione o l'esportazione bulk di un documento XML, è consigliabile usare un carattere di terminazione del campo che non sia contenuto in alcun documento, ad esempio una serie di quattro valori Null (\0) seguita dalla lettera z: \0\0\0\0z.

In questo esempio viene illustrato come utilizzare questo carattere di terminazione del campo per la tabella di esempio xTable . Per creare questa tabella di esempio, utilizzare l'istruzione CREATE TABLE seguente:

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

File di formato di esempio

È necessario specificare il carattere di terminazione del campo nel file di formato. Nell'esempio D viene utilizzato un file di formato non XML denominato Xmltable.fmt che contiene il seguente output:

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

È possibile utilizzare tale file di formato per l'importazione bulk di documenti XML nella tabella xTable tramite un comando bcp o un'istruzione BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...) .

Esempio D

In questo esempio viene utilizzato il file di formato Xmltable.fmt in un'istruzione BULK INSERT per importare il contenuto di un file di dati XML denominato Xmltable.dat.

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

[Top]

Esportazione bulk di dati XML

Nell'esempio seguente viene usata l'utilità bcp per l'esportazione bulk di dati XML dalla tabella creata nell'esempio precedente usando lo stesso file di formato XML. Nel comando bcp seguente, <server_name> e <instance_name> rappresentano segnaposto da sostituire con valori appropriati:

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

Nota

SQL Server non salva la codifica XML in caso di persistenza di dati XML nel database. La codifica originale dei campi XML, pertanto, non è disponibile quando vengono esportati i dati XML. SQL Server usa la codifica UTF-16 durante l'esportazione di dati XML.

Vedi anche