Exemplos de importação e exportação em massa de documentos XML (SQL Server)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Você pode importar documentos XML em massa para um banco de dados SQL Server ou exportá-los em massa de um banco de dados SQL Server. Este artigo fornece exemplos de ambos.

Para importar dados em massa de um arquivo de dados para uma tabela SQL Server ou exibição não particionada, você pode usar as seguintes opções:

  • utilitáriobcp

    Você também pode usar o utilitário bcp para exportar dados de qualquer lugar em um banco de dados SQL Server que uma SELECT instrução funcione, incluindo exibições particionadas.

  • BULK INSERT

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

Para obter mais informações, consulte os seguintes artigos:

Exemplos

Importação em massa de dados XML como um fluxo de bytes binários

Ao importar dados XML em massa de um arquivo que contém uma declaração de codificação que você deseja aplicar, especifique a opção SINGLE_BLOB na OPENROWSET(BULK...) cláusula . A SINGLE_BLOB opção garante que o analisador XML no SQL Server importe os dados de acordo com o esquema de codificação especificado na declaração XML.

Tabela de exemplo

Para testar o exemplo A, crie a tabela de exemplo T.

USE tempdb;
GO

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

Arquivo de dados de exemplo

Antes de poder executar o exemplo A, você deve criar um arquivo codificado UTF-8 (C:\SampleFolder\SampleData3.txt) que contenha a seguinte instância de exemplo que especifica o esquema de codificação UTF-8 .

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

Exemplo A

Esse exemplo usa a opção SINGLE_BLOB em uma instrução INSERT ... SELECT * FROM OPENROWSET(BULK...) para importar dados de um arquivo nomeado como SampleData3.txt e inserir uma instância XML na tabela com uma coluna, tabela de exemplo T.

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

Você também pode especificar explicitamente os nomes de coluna da seguinte maneira:

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

Comentários

SINGLE_BLOB Usando nesse caso, você pode evitar uma incompatibilidade entre a codificação do documento XML (conforme especificado pela declaração de codificação XML) e a página de código da cadeia de caracteres implícita pelo servidor.

Se você usar tipos de dados NCLOB ou CLOB e executar em uma página de código ou conflito de código, você deve adotar um dos seguintes procedimentos:

  • Remova a declaração XML para importar o conteúdo do arquivo de dados XML com êxito.

  • Especifique uma página de código na opção CODEPAGE da consulta que corresponda ao esquema de codificação usado na declaração XML.

  • Faça a correspondência ou resolva as definições de ordenação do banco de dados com um esquema de codificação XML não Unicode.

[Início]

Importação em massa de dados XML em uma linha existente

Este exemplo usa o provedor de conjunto de linhas em massa OPENROWSET para adicionar uma instância XML a uma linha ou linhas existentes na tabela de exemplo T.

Observação

Para executar esse exemplo, você deve completar o teste de script fornecido no exemplo A, que cria a tabela tempdb.dbo.T e importa em massa os dados do SampleData3.txt.

Arquivo de dados de exemplo

O exemplo B usa uma versão modificada do arquivo de dados de exemplo SampleData3.txt do exemplo anterior. Para executar esse exemplo, modifique o conteúdo desse arquivo como indicado abaixo:

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

Exemplo 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

[Início]

Importação em massa de dados XML de um arquivo que contém um DTD

Importante

Recomendamos que você não habilite o suporte para DTDs (Definições de Tipo de Documento) se não for necessário em seu ambiente XML. Ativar o suporte a DTD aumenta a área da superfície atacável de seu servidor e pode expô-lo a um ataque de negação de serviço. Caso seja necessário habilitar o suporte a DTD, você pode reduzir esse risco de segurança processando somente documentos XML confiáveis.

Você pode receber o seguinte erro ao usar bcp para importar dados XML de um arquivo que contém um 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

Para resolver esse problema, você pode importar dados XML de um arquivo de dados que contém um DTD usando a função OPENROWSET(BULK...) e especificando a opção CONVERT na cláusula de comando SELECT . A sintaxe básica do comando é:

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

Arquivo de dados de exemplo

Antes de poder testar esse exemplo de importação em massa, crie um arquivo (C:\SampleFolder\Dtdfile.xml) que contém a seguinte instância de exemplo:

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

Tabela de exemplo

O exemplo que C usa a tabela de exemplo T1 , criada pela seguinte instrução CREATE TABLE :

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

Exemplo C

Este exemplo usa OPENROWSET(BULK...) e especifica a opção CONVERT na cláusula SELECT para importar os dados XML do Dtdfile.xml na tabela de exemplo T1.

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

Depois que a instrução INSERT é executada, o DTD é eliminado do XML e armazenado na tabela T1 .

[Início]

Especificar o terminador de campo explicitamente usando um arquivo de formato

O exemplo abaixo mostra como importar em massa o seguinte documento XML, Xmltable.dat.

Arquivo de dados de exemplo

O documento em Xmltable.dat contém dois valores XML, um para cada linha. O primeiro valor XML é codificado com UTF-16 e o segundo valor é codificado com UTF-8.

O conteúdo desse arquivo de dados é mostrado no seguinte despejo hexadecimal:

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*

Tabela de exemplo

Ao importar ou exportar em massa um documento XML, você deve usar um terminador de campo que não possa aparecer em nenhum dos documentos; por exemplo, uma série de quatro nulos (\0) seguidos pela letra z: \0\0\0\0z.

Esse exemplo mostra como usar esse terminador de campo na tabela de exemplo xTable . Para criar essa tabela exemplo, use a seguinte instrução CREATE TABLE :

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

Arquivo de formato de exemplo

O terminador de campo deve ser especificado no arquivo de formato. O exemplo D usa um arquivo de formato não XML chamado Xmltable.fmt que contém a seguinte saída:

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

Você pode usar esse arquivo de formato para importar documentos XML em massa na tabela xTable usando um comando bcp ou uma instrução BULK INSERT ou INSERT ... SELECT * FROM OPENROWSET(BULK...) .

Exemplo D

Este exemplo usa o arquivo de formato Xmltable.fmt em uma instrução BULK INSERT para importar o conteúdo de um arquivo de dados XML chamado Xmltable.dat.

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

[Início]

Exportação em massa de dados XML

O exemplo abaixo usa o bcp para exportar dados XML em massa da tabela criada no exemplo anterior usando o mesmo arquivo de formato XML. No comando bcp abaixo, <server_name> e <instance_name> representam espaços reservados que devem ser substituídos com os valores apropriados:

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

Observação

SQL Server não salva a codificação XML quando os dados XML são persistentes no banco de dados. Portanto, a codificação original de campos XML não está disponível quando dados XML são exportados. SQL Server usa a codificação UTF-16 ao exportar dados XML.

Confira também