Artigos Banco de Dados Carregando o conteúdo de arquivos XML em tabelas do SQL Server com T-SQL

O formato XML (abreviação do inglês “Extensible Markup Language”) revolucionou, sem sombra de dúvidas, a forma como muitas aplicações compartilham informações. Baseado em uma estrutura hierárquica e extremamente flexível (não existem tags definidas como em HTML), este padrão vem sendo utilizado extensivamente por Web Services na integração de sistemas, como repositório para o armazenamento de configurações de um software (algo bastante comum em soluções concebidas nas plataformas .NET e Java) ou ainda, sob a forma de arquivos empregados no carregamento em lote de um conjunto de dados.

Este último uso para documentos no formato XML está comumente associado a situações como:

  • O processamento de informações relativas a produtos (listas de preço, especificações) e contatos de clientes/fornecedores, por exemplo;
  • À carga de dados que servirão de base para a geração de análises quantitativas/qualitativas em sistemas de BI (sigla em inglês para “Business Intelligence”). Nos últimos tempos aplicações deste tipo vêm ganhando um grande espaço em organizações dos mais variados tipos, uma vez que oferecem a profissionais da área de gestão um importante embasamento para a realização de atividades relacionadas à tomada de decisões.

O SQL Server representa a solução da Microsoft na área de banco de dados relacionais, dispondo de mecanismos que viabilizam a manipulação de grandes volumes de informações. Dentre as funcionalidades oferecidas, está a possibilidade de se importar arquivos XML através de recursos próprios deste gerenciador de bancos de dados. O objetivo deste artigo é descrever justamente como isto pode ser feito, a partir de um exemplo que envolve a utilização de instruções T-SQL para o carregamento das informações que constam em um documento XML.

Conteúdo do arquivo XML utilizado no exemplo

Na Listagem 1 é apresentado o conteúdo do arquivo XML (Prestadores.xml) que servirá de base para o exemplo apresentado mais adiante.

No arquivo “Prestadores.xml” encontram-se dados de prestadores de serviço contratados junto a uma hipotética Consultoria de Tecnologia. É possível constatar neste documento XML a presença dos seguintes campos:

  • CPF: CPF do profissional;
  • NomeProfissional: nome do prestador de serviços;
  • Empresa: empresa da qual o prestador é proprietário;
  • CNPJ: CNPJ da empresa prestadora de serviços;
  • Cidade: cidade em que está aberta a empresa prestadora;
  • Estado: estado da empresa prestadora;
  • InscricaoEstadual: inscrição estadual da empresa prestadora de serviços (o conteúdo deste campo é opcional);

Listagem 1: Arquivo Prestadores.xml


<?xml version="1.0" encoding="utf-8"?>
<Prestadores>
  <Prestador>
    <CPF>111.111.111-11</CPF>
    <NomeProfissional>JOÃO DA SILVA</NomeProfissional>
    <Empresa>SILVA CONSULTORIA EM INFORMÁTICA LTDA</Empresa>
    <CNPJ>11.111.111/1111-11</CNPJ>
    <Cidade>São Paulo</Cidade>
    <Estado>SP</Estado>
    <InscricaoEstadual>1111-1</InscricaoEstadual>
  </Prestador>
  <Prestador>
    <CPF>222.222.222-22</CPF>
    <NomeProfissional>JOAQUIM DE OLIVEIRA</NomeProfissional>
    <Empresa>SERVIÇOS DE TECNOLOGIA OLIVEIRA ME</Empresa>
    <CNPJ>22.222.222/2222-22</CNPJ>
    <Cidade>Belo Horizonte</Cidade>
    <Estado>MG</Estado>
    <InscricaoEstadual></InscricaoEstadual>
  </Prestador>
  <Prestador>
    <CPF>333.333.333-33</CPF>
    <NomeProfissional>MARIA MARTINS</NomeProfissional>
    <Empresa>MARTINS TECNOLOGIA LTDA</Empresa>
    <CNPJ>33.333.333/3333-33</CNPJ>
    <Cidade>Rio de Janeiro</Cidade>
    <Estado>RJ</Estado>
    <InscricaoEstadual>33333</InscricaoEstadual>
  </Prestador>
  <Prestador>
    <CPF>444.444.444-44</CPF>
    <NomeProfissional>JOANA SANTOS</NomeProfissional>
    <Empresa>CONSULTORIA SANTOS LTDA</Empresa>
    <CNPJ>44.444.444/4444-44</CNPJ>
    <Cidade>São Paulo</Cidade>
    <Estado>SP</Estado>
    <InscricaoEstadual></InscricaoEstadual>
  </Prestador>
</Prestadores>

Definindo a tabela em que será importado o conteúdo do arquivo XML de Prestadores

A Listagem 2 apresenta o código responsável pela geração da tabela (TB_PRESTADOR), na qual constarão as informações originárias do arquivo XML de Prestadores. Essa estrutura está sendo criada em um banco de dados chamado TesteXMLSQLServer, fazendo-se uso para isto do SQL Server 2012.

Listagem 2: Script para criação da tabela TB_PRESTADOR


USE [TesteXMLSQLServer]
GO

CREATE TABLE [dbo].[TB_PRESTADOR](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CPF] [char](14) NOT NULL,
	[NmProfissional] [varchar](50) NOT NULL,
	[NmEmpresa] [varchar](50) NOT NULL,
	[CNPJ] [char](18) NOT NULL,
	[NmCidade] [varchar](40) NOT NULL,
	[CdEstado] [char](2) NOT NULL,
	[CdInscricaoEstadual] [varchar](20) NULL,
 CONSTRAINT [PK_TB_PRESTADOR] PRIMARY KEY ([Id]),
 CONSTRAINT [UK_TB_PRESTADOR] UNIQUE ([CNPJ])
)
GO

Carregando as informações do documento XML na base de dados

Na Listagem 3 estão de instruções T-SQL que permitem inserir/atualizar na tabela TB_PRESTADOR as informações constantes no arquivo “Prestadores.xml”:

  • Inicialmente é criada uma tabela temporária de nome #CARGA_PRESTADORES. Essa estrutura receberá os dados existentes no arquivo “Prestadores.xml”, antes de se prosseguir com a transferência definitiva das informações para a tabela TB_PRESTADOR;
  • Num segundo momento ocorrerá o carregamento dos dados na tabela temporária #CARGA_PRESTADORES (via instrução INSERT). Isto é possível graças a um comando SELECT combinado ao uso da função OPENROWSET, com o funcionamento desta última sendo detalhada mais adiante;
  • Na sequência é criado um cursor (crPrestadores), a fim de possibilitar a navegação através dos registros inseridos na tabela temporária #CARGA_PRESTADORES. Uma nova transação é então iniciada via instrução BEGIN TRANSACTION, antes do loop WHILE em que acontecerá a leitura dos dados de #CARGA_PRESTADORES. Se um prestador de serviços não constar na tabela TB_PRESTADOR, um novo registro será incluído nessa estrutura; caso contrário, os dados existentes serão substituídos pelas informações que constam no arquivo “Prestadores.xml”;
  • Por fim, é efetuada uma checagem para determinar se ocorreram erros ou não durante a atualização da tabela TB_PRESTADOR. Caso os procedimentos anteriores tenham sido realizados com sucesso, a transação é encerrada com a gravação definitiva dos dados na base (via instrução COMMIT TRANSACTION).

Quanto ao uso da função OPENROWSET, construções baseadas neste comando contam com o seguinte funcionamento:

  • A instrução BULK possibilita a leitura dos dados que estão em um arquivo (no exemplo foi utilizado o documento XML cujo caminho é "C:\Devmedia\TesteXMLSqlServer\Prestadores.xml"). No caso da opção SINGLE_BLOB, a presença deste elemento faz com que todo o conteúdo de um arquivo seja retornado em uma única linha/coluna, a qual precisará ser manipulada posteriormente (há um CAST que converte o valor correspondente para o tipo XML);
  • Já a instrução CROSS APPLY em conjunto com o método nodes permite a navegação pelos elementos de um documento XML. Neste caso, foram selecionados todos os elementos de nome Prestador, os quais correspondem aos diferentes registros que deverão estar sendo importados;
  • Por último, cada elemento Prestador é acessado e, em seguida, aciona-se o método query, com o intuito de se proceder com a leitura de cada campo/elemento que fará parte de um registro a ser incluído na base de dados.

Listagem 3: Script para carregamento do arquivo Classe ArquivoPrestadores


USE [TesteXMLSQLServer]
GO

-- Criação de tabela temporária

IF OBJECT_ID('tempdb..#CARGA_PRESTADORES') IS NOT NULL
BEGIN
    DROP TABLE #CARGA_PRESTADORES
END

CREATE TABLE #CARGA_PRESTADORES (
    [CPF] [char](14),
    [NmProfissional] [varchar](50),
    [NmEmpresa] [varchar](50),
    [CNPJ] [char](18),
    [NmCidade] [varchar](40),
    [CdEstado] [char](2),
    [CdInscricaoEstadual] [varchar](20)
)


-- Carregando os dados a partir do arquivo XML

INSERT INTO #CARGA_PRESTADORES
           (CPF
           ,NmProfissional
           ,NmEmpresa
           ,CNPJ
           ,NmCidade
           ,CdEstado
           ,CdInscricaoEstadual)
SELECT
    X.Prestador.query('CPF').value('.', 'CHAR(14)'),
    X.Prestador.query('NomeProfissional').value('.', 'VARCHAR(50)'),
    X.Prestador.query('Empresa').value('.', 'VARCHAR(50)'),
    X.Prestador.query('CNPJ').value('.', 'CHAR(18)'),
    X.Prestador.query('Cidade').value('.', 'VARCHAR(40)'),
    X.Prestador.query('Estado').value('.', 'CHAR(2)'),
    X.Prestador.query('InscricaoEstadual').value('.', 'VARCHAR(20)')
FROM
( 	
    SELECT CAST(X AS XML)
    FROM OPENROWSET(
        BULK 'C:\Devmedia\TesteXMLSqlServer\Prestadores.xml',
        SINGLE_BLOB) AS T(X)
) AS T(X)
CROSS APPLY X.nodes('Prestadores/Prestador') AS X(Prestador);


-- Incluindo as informações na tabela TB_PRESTADOR

DECLARE @CPF CHAR(14)
DECLARE @NmProfissional VARCHAR(50)
DECLARE @NmEmpresa VARCHAR(50)
DECLARE @CNPJ CHAR(18)
DECLARE @NmCidade VARCHAR(40)
DECLARE @CdEstado CHAR(2)
DECLARE @CdInscricaoEstadual VARCHAR(20)

DECLARE crPrestadores CURSOR FOR
SELECT CPF
      ,NmProfissional
      ,NmEmpresa
      ,CNPJ
      ,NmCidade
      ,CdEstado
      ,CdInscricaoEstadual
FROM #CARGA_PRESTADORES
ORDER BY CPF

OPEN crPrestadores

FETCH NEXT FROM crPrestadores INTO
    @CPF, @NmProfissional, @NmEmpresa,
    @CNPJ, @NmCidade, @CdEstado,  @CdInscricaoEstadual

BEGIN TRANSACTION -- Inicia uma nova transação

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (LTRIM(RTRIM(@CdInscricaoEstadual)) = '')
	    SET @CdInscricaoEstadual = NULL
    
	IF (NOT EXISTS(SELECT 1 FROM dbo.TB_PRESTADOR WHERE CPF = @CPF))
    BEGIN
        INSERT INTO dbo.TB_PRESTADOR
                   (CPF
                   ,NmProfissional
                   ,NmEmpresa
                   ,CNPJ
                   ,NmCidade
                   ,CdEstado
                   ,CdInscricaoEstadual)
             VALUES
                   (@CPF
                   ,@NmProfissional
                   ,@NmEmpresa
                   ,@CNPJ
                   ,@NmCidade
                   ,@CdEstado
                   ,@CdInscricaoEstadual)
    END
    ELSE
    BEGIN
        UPDATE dbo.TB_PRESTADOR
           SET CPF = @CPF
              ,NmProfissional = @NmProfissional
              ,NmEmpresa = @NmEmpresa
              ,CNPJ = @CNPJ
              ,NmCidade = @NmCidade
              ,CdEstado = @CdEstado
              ,CdInscricaoEstadual = @CdInscricaoEstadual
        WHERE CPF = @CPF
    END

    FETCH NEXT FROM crPrestadores INTO
        @CPF, @NmProfissional, @NmEmpresa,
        @CNPJ, @NmCidade, @CdEstado,  @CdInscricaoEstadual
END

CLOSE crPrestadores

DEALLOCATE crPrestadores


-- Verifica a ocorrência de erros e, em caso negativo, confirma
-- a transação iniciada anteriormente

IF (@@ERROR = 0)
BEGIN
    COMMIT TRANSACTION
END
ELSE
BEGIN
    ROLLBACK TRANSACTION
END

Já na Listagem 4 está um exemplo de consulta à tabela TB_PRESTADOR. A execução desta instrução após o processamento do arquivo “Prestadores.xml” produzirá um resultado similar ao que consta na Figura 1.

Listagem 4: Exemplo de consulta à tabela TB_PRESTADOR


USE [TesteXMLSQLServer]
GO

SELECT *
FROM dbo.TB_PRESTADOR
ORDER BY Id
Consulta efetuada à tabela TB_PRESTADOR

Figura 1: Consulta efetuada à tabela TB_PRESTADOR

Conclusão

Procurei com este artigo demonstrar como arquivos XML podem ser importados a partir de instruções T-SQL. A necessidade de se carregar documentos deste tipo pode estar associada tanto a cenários de integração entre diferentes sistemas, quanto ao processamento de dados para a posterior produção de informações em sistemas de Business Intelligence.

Espero que o conteúdo aqui apresentado possa ser útil no seu dia-a-dia.

Até uma próxima oportunidade!