Partilhar via

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

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

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

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

  • bcp utilitário

    Você também pode usar o utilitário bcp para exportar dados de qualquer lugar em um banco de dados do SQL Server que uma instrução SELECT 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 em massa dados XML de um arquivo que contém uma declaração de codificação que deseja aplicar, especifique a opção SINGLE_BLOB na cláusula OPENROWSET(BULK...). A opção SINGLE_BLOB 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 uma 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 executar o exemplo A, você deve criar um arquivo codificado UTF-8 (C:\SampleFolder\SampleData3.txt) que contém 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

Este exemplo usa a opção SINGLE_BLOB em uma instrução INSERT ... SELECT * FROM OPENROWSET(BULK...) para importar dados de um arquivo chamado SampleData3.txt e inserir uma instância XML na tabela de coluna única, 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 das colunas da seguinte maneira:

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

Comentários

Usando SINGLE_BLOB 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 de cadeia de caracteres implícita pelo servidor.

Se você usar tipos de dados NCLOB ou CLOB e se deparar com uma página de código ou conflito de codificação, deverá seguir um destes procedimentos:

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

  • 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.

  • Corresponder ou resolver as configurações de agrupamento de banco de dados com um esquema de codificação XML não Unicode.

[Topo]

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 este exemplo, você deve primeiro concluir o script de teste fornecido no exemplo A. Esse exemplo cria a tabela tempdb.dbo.T e importa dados em massa de 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 este exemplo, modifique o conteúdo deste ficheiro da seguinte forma:

<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

[Topo]

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 ele não for necessário em seu ambiente XML. Ativar o suporte DTD aumenta a área de superfície que pode ser atacada do servidor e pode expô-lo a um ataque de negação de serviço. Se você precisar habilitar o suporte a DTD, poderá reduzir esse risco de segurança processando apenas documentos XML confiáveis.

Você pode obter o seguinte erro, quando você usa 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 contornar esse problema, você pode importar dados XML de um arquivo de dados que contém um DTD usando a função OPENROWSET(BULK...) e, em seguida, especificando a opção CONVERT na cláusula SELECT do comando. A sintaxe básica para o comando é:

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

Arquivo de dados de exemplo

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

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

Tabela de exemplo

O exemplo 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 para a 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 é removido do XML e armazenado na tabela T1.

[Topo]

Especifique o terminador de campo explicitamente usando um arquivo de formato

O exemplo a seguir 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, deve-se utilizar um terminador de campo que não pode aparecer em nenhum dos documentos; por exemplo, uma série de quatro nulos (\0) seguida da letra z: \0\0\0\0z.

Este exemplo mostra como usar esse terminador de campo para a tabela de exemplo xTable. Para criar esta tabela de 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 para a 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

[Topo]

Exportação em massa de dados XML

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

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

Observação

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 os dados XML são exportados. O SQL Server usa codificação UTF-16 ao exportar dados XML.

Ver também

  • Last updated on