Como importar arquivos XML para o SQL Server – Parte IV

Bom Dia Pessoal,

Chegando ao final da nossa série "Como importar arquivos XML" irei demonstrar uma outra alternativa para validar e importar arquivos XML para o SQL Server. No último artigo demonstrei como fazer essa importação através do CLR que juntamente com o comando OPENROWSET e o SSIS compõe as principais soluções para importar arquivos desse tipo.

Não é intenção minha esgotar todas as alternativas sobre esse assunto (certamente existem várias), mas para que a série fique de fato completa, hoje mostrarei como montar uma pequena aplicação em .NET para fazer essa carga. No passado os preços das ferramentas de ETL eram demasiadamente elevados (não que hoje sejam baratas) e quase sempre os SGBDs não forneciam um suporte vasto para cargas de arquivos (ainda não o fornecem, pois, não é esse seu principal objetivo). Nesse época era bastante comum a criação de pequenos aplicativos para realizar a carga de dados seja uma simples leitura de um arquivo texto até uma complexa validação de planilhas eletrônicas, arquivos DBase, etc. Hoje essa não é mais a prática dominante (nem tampouco a melhor na maioria dos casos), mas construir pequenos aplicativos ainda é uma alternativa a se considerar. Vou explorar essa possibilidade para carregar dados XML.

Seria muito simples transformar os códigos em CLR para uma aplicação .NET, mas dessa vez demonstrarei algumas possibilidades bem mais interessantes além de algumas classes que são bastante úteis nesse tipo de carga. Para tornar o aplicativo "agendável", não irei codificar uma aplicação desktop, mas sim em linha de comando. Isso possibilita utilizá-lo através de tarefas agendadas, chamá-los em arquivos .bat, etc mas por favor não coloquem a chamada desse aplicativo em uma xp_cmdshell (isso pode degradar sensivelmente o desempenho do SQL Server).

Irei utilizar os mesmos arquivos XML e XSD do artigo anterior. Caso eles não estejam disponíveis, recomendo dar uma lida no artigo para obtê-los.

Antes de postar o código, mostrarei as tarefas genéricas que a aplicação .NET irá realizar.

Leitura do arquivo de configuração

Criei um arquivo de configuração para tornar a parametrização do aplicativo mais fácil, o arquivo de configuração está localizado em G:\CLR\Config.xml e tem a seguinte estrutura:

<?xml version="1.0" encoding="iso-8859-1"?>
<Configuracoes>
 <Diretorio>G:\XML\CLR\</Diretorio>
 <ArquivoXSD>G:\XML\CLR\Filmes.xsd</ArquivoXSD>
 <ServidorSQL>SQLTest</ServidorSQL>
 <BancoSQL>Banco</BancoSQL>
</Configuracoes>

O objetivo desse arquivo é indicar o diretório onde os arquivos XML estão localizados, o local do arquivo de validação (XSD), o nome do servidor SQL onde os dados serão descarregados e o banco de dados nesse servidor onde os dados serão descarregados. Por padrão estou utilizando a autenticação integrada.

Criação das tabelas necessárias

Uma vez que o arquivo XSD descreva a estrutura de um arquivo XML, podemos utilizá-lo para gerar as tabelas necessárias para a carga. Esse é um ponto interessante, pois, as tabelas necessárias serão criadas a partir do XSD e não teremos que conhecer previamente os detalhes do arquivo XML para criar tabelas compatíveis. Com base no XSD, as tabelas serão criadas (algo semelhante foi feito quando fizemos o mapeamento usando o SSIS).

Carga dos arquivos XML

Todos os arquivos XML presentes no diretório especificado no arquivo de configuração serão validados conforme o arquivo XSD e carregados para o servidor e o banco especificados no arquivo de configuração.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Schema;
using System.Xml.XPath;

namespace CargaXML
{
  class Program
  {
    static void Main(string[] args)
    {
      // Captura o local do arquivo de configuracao
      string ArquivoConf = args[0];

      // Captura os parâmetros especificados no XML
      XmlDocument xDoc = new XmlDocument();
      xDoc.Load(ArquivoConf);

      // Coloca os parâmetros em variáveis
      string Diretorio = xDoc.SelectSingleNode(
        "/Configuracoes/Diretorio").InnerText;

      string ArquivoXSD = xDoc.SelectSingleNode(
        "/Configuracoes/ArquivoXSD").InnerText;

      string ServidorSQL = xDoc.SelectSingleNode(
        "/Configuracoes/ServidorSQL").InnerText;

      string BancoSQL = xDoc.SelectSingleNode(
        "/Configuracoes/BancoSQL").InnerText;

      Program p = new Program();

      // Tenta fazer a importação
      try
      {
        // Cria as tabelas necessárias
        p.CriarTabelasSQL(ArquivoXSD, ServidorSQL, BancoSQL);

        // Para cada arquivo Xml encontrado no diretório
        foreach (string Arq in p.Arquivos(Diretorio))
        {
          // Faz a carga do arquivo
          p.CarregarArquivoXML(Arq, ArquivoXSD, ServidorSQL, BancoSQL);
        }

        Console.WriteLine("Importação Finalizada");
        Console.WriteLine("Aperte alguma tecla para finalizar");
        Console.ReadLine();
      }
      catch (Exception e)
      {
        Console.WriteLine("Ocorreu um erro inesperado");
        Console.WriteLine(e.Message);
        Console.WriteLine("Aperte alguma tecla para finalizar");
        Console.ReadLine();
      }
    }

    public List<string> Arquivos (string Diretorio)
    {
      DirectoryInfo di = new DirectoryInfo(Diretorio);
      List<string> ArquivosRet = new List<string>();

      foreach (FileInfo f in di.GetFiles("*.xml"))
      {
        ArquivosRet.Add(f.FullName);
      }

      return ArquivosRet;
    }

    public string MTipDados (string TipoDados)
    {
      // Declara uma variável para mostrar o tipo final
      string TipoDadosSQL = "";

      switch (TipoDados)
      {
        case "System.String":
          TipoDadosSQL = "VARCHAR(100)";
          break;

        case "System.Int32":
          TipoDadosSQL = "INT";
          break;

        case "System.DateTime":
          TipoDadosSQL = "SMALLDATETIME";
          break;

        default:
          TipoDadosSQL = "VARCHAR(MAX)";
          break;
      }

      return TipoDadosSQL;
    }

    public void CriarTabelasSQL(string ArquivoXSD,
      string ServidorSQL, string BancoSQL)
    {
      // Cria uma conexão com o SQL Server
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = "Integrated Security=true;Server=" +
        ServidorSQL + ";Database=" + BancoSQL;
      cn.Open();

      // Carrega o XSD para um DataSet
      DataSet ds = new DataSet();
      ds.ReadXmlSchema(ArquivoXSD);

      // Para cada tabela existente no DataSet
      foreach (DataTable dt in ds.Tables)
      {
        // Cria uma variável para armazenar o comando
        StringBuilder strSQL = new StringBuilder(@"CREATE TABLE ");       

        // Adiciona o nome da tabela ao comando
        strSQL.Append(dt.TableName);
        strSQL.Append(" (ArquivoXML VARCHAR(50)");

        // Para cada coluna encontrada na uma tabela
        foreach (DataColumn dc in dt.Columns)
        {
          // Verifica o tipo de dados
          strSQL.Append(", ");
          strSQL.Append(dc.ColumnName);
          strSQL.Append(" ");
          strSQL.Append(MTipDados(dc.DataType.ToString()));
        }

        // Adiciona o parêntese
        strSQL.Append(")");

        // Dispara o comando
        SqlCommand cmd = cn.CreateCommand();
        cmd.CommandText = strSQL.ToString();
        cmd.ExecuteNonQuery();
      }

      // Fecha e elimina a conexão
      cn.Close();
      cn.Dispose();
    }

    public void CarregarArquivoXML(
        string ArquivoXML, string ArquivoXSD,
        string ServidorSQL, string BancoSQL)
    {
      // Carrega o esquema para montar o DataSet
      DataSet ds = new DataSet();
      ds.ReadXmlSchema(ArquivoXSD);

      // Abre uma conexão para enviar os dados
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = "Integrated Security=true;Server=" +
        ServidorSQL + ";Database=" + BancoSQL;
      cn.Open();

      // Carrega o arquivo XML para o DataSet
      ds.ReadXml(ArquivoXML, XmlReadMode.ReadSchema);

      // Para cada tabela encontrada no DataSet
      foreach (DataTable dt in ds.Tables)
      {
        // Adiciona uma coluna para o nome do arquivo
        DataColumn dc = new DataColumn("ArquivoXML",
          Type.GetType("System.String"), "", MappingType.Attribute);

        dc.DefaultValue = (string)ArquivoXML;
        dt.Columns.Add(dc);

        // Cria um DataAdapter para enviar os dados
        SqlDataAdapter da = new SqlDataAdapter(
          "SELECT * FROM " + dt.TableName, cn);

        SqlCommandBuilder scb = new SqlCommandBuilder(da);
        da.Update(ds, dt.TableName);

        Console.WriteLine("Tabela " + dt.TableName +
          " importada com êxito");
      }

      // Fecha e elimina a conexão
      cn.Close();
      cn.Dispose();

      Console.WriteLine("Arquivo " + ArquivoXML + " importado");
    }
  }
}

Caso não o Visual Studio Studio não esteja instalado, basta seguir os passos abaixo para compilar o executável:

  • Copie e cole o código em um arquivo .cs (Ex: G:\XML\cargaxml.cs)
  • Abra um prompt de comando
  • Navegue até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /out:cargaxml.exe G:\XML\cargaxml.cs (supondo que o arquivo esteja em G:\XML\)
  • Opcionalmente mover o arquivo cargaxml.exe para outro diretório (Ex: G:\XML\)
  • Executar o arquivo cargaxml.exe localizado no diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou o utilizado anteriormente)

Antes de executá-lo, vale a pena comentar alguns dos métodos do aplicativo:

  • Arquivos: Esse método recebe como parâmetro o nome de um diretório e retorna uma lista com todos os arquivos xml encontrados no diretório (não considera subdiretórios)
  • MTipDados: O XSD possui seus tipos de dados que sofrem uma conversão para os tipos do C#. Esses tipos não são exatamente os mesmos tipos do SQL Server. Esse método faz a troca dos tipos do C# para os compatíveis mais próximos no SQL Server. Contemplei apenas os tipos String, Int32 e DateTime. Outros tipos poderiam ser utilizados, mas não os coloquei porque nos arquivos utilizados só existem esses tipos de dados.
  • CriarTabelasSQL: Esse método carrega o esquema no arquivo XSD para um DataSet convertendo a estrutura XML para uma estrutura relacional. Como o XML é semi-estruturado, é possível (e provável) que um arquivo XML gere mais de uma tabela no DataSet. Para cada tabela presente no DataSet, é feito uma varredura em todas as colunas do DataSet para montar dinamicamente o comando de CREATE TABLE e executá-lo posteriormente. Adicionalmente, foi incluida uma coluna chamada ArquivoXML para incluir o nome do arquivo XML utilizado (veremos a utilidade dessa coluna posteriormente).
  • CarregarArquivoXML: Esse método carrega uma arquivo XML especificado para o DataSet. Adicionalmente para cada tabela dentro do DataSet é adicionada uma coluna contendo o nome do arquivo XML que está sendo carregado. Esse passo é necessário para que a tabela do DataSet possa ser mapeada diretamente para a tabela do banco de dados.

Como o diretório onde os arquivos estão localizados pode variar, o aplicativo dispõe de uma forma de configurá-los. Coloque o arquivo de configuração (config.xml) em um diretório apropriado e altere o elemento Diretorio para o diretório onde os arquivos XML (filmes01.xml, filmes02.xml, filmes03.xml). Faça as alterações necessárias para o elemento ArquivoXSD para mostrar o caminho completo do arquivo XSD. Posteriormente basta abrir um prompt de comando, navegar até a pasta onde o executável cargaxml.exe está presente e executar cargaxml.exe CaminhoCompletoDoArquivoDeConfiguracao.

Após rodar o executável, podemos ver que os dados foram importados com êxito. Para conferir se de fatos os dados foram carregados, podemos nos conectar ao servidor SQL Server e ao banco informados no arquivo de configuração. Foram criadas duas tabelas (Genero e Filme) com a estrutura abaixo

SELECT
    TABLE_NAME As Tabela, COLUMN_NAME As Coluna,
    ORDINAL_POSITION As Ordem, DATA_TYPE As Tipo,
    CHARACTER_MAXIMUM_LENGTH As Tamanho
FROM INFORMATION_SCHEMA.COLUMNS

Tabela

Coluna

Ordem

Tipo

Tamanho

Genero

ArquivoXML

1

varchar

50

Genero

Tipo

2

varchar

100

Genero

Genero_Id

3

int

NULL

Filme

ArquivoXML

1

varchar

50

Filme

DataPrevista

2

smalldatetime

NULL

Filme

Filme_text

3

varchar

100

Filme

Genero_Id

4

int

NULL

No arquivo XML temos os nós Tipo, DataPrevista e Filme. Durante a leitura dos dados, foi adicionada propositalmente uma coluna ArquivoXML para guardar o nome do arquivo XML a ser importado. A coluna Filme foi complementada com o sufixo "_text" porque "Filme" é o único nó do documento XML que não está em forma de atributo. Como a consulta XPath para retornar valores de elementos inclui a navegação (/text()), por padrão esse sufixo é incluso. Curiosamente apareceu a coluna Genero_ID na tabela Genero e na tabela Filme mesmo sem que a tenhamos especificado. Ela parece ser "desejada" já que liga os filmes aos gêneros, mas é preciso investigá-la um pouco mais para não haver um "erro" de interpretação.

Como o XML é uma representação hierárquica e o DataSet é uma representação relacional, é necessário utilizar alguns artifícios para que esse mapeamento possa ser feito. O arquivo xml especifica um gênero e os filmes daquele gênero são elementos apresentados logo após o gênero. Se essa estrutura em XML irá derivar duas tabelas (Genero e Filme) como saber a qual gênero pertence determinado filme se um arquivo pode conter vários gêneros e vários filmes ? Para tornar esse mapeamento possível, o framework .NET inclui silenciosamente essa coluna nas tabelas do DataSet e faz o devido relacionamento.

Embora possa parecer que a coluna Genero_ID seja de fato uma chave primária em Genero e uma chave estrangeira em Filme, é preciso ter muita cautela em assumir que isso seja verdadeiro (pois quase sempre não é). Vejamos uma simples consulta na tabela Genero:

SELECT ArquivoXML, Tipo, Genero_ID FROM Genero

Essa consulta produz o seguinte resultado:

ArquivoXML

Tipo

Genero_ID

G:\XML\CLR\Filmes01.xml

Ação

0

G:\XML\CLR\Filmes01.xml

Animação

1

G:\XML\CLR\Filmes02.xml

Comédia

0

G:\XML\CLR\Filmes02.xml

Romance

1

G:\XML\CLR\Filmes02.xml

Documentário

2

G:\XML\CLR\Filmes03.xml

Drama

0

G:\XML\CLR\Filmes03.xml

Ficção

1

G:\XML\CLR\Filmes03.xml

Policial

2

G:\XML\CLR\Filmes03.xml

Suspense

3

G:\XML\CLR\Filmes03.xml

Terror

4

Se for observado, a coluna Genero_ID não é um identificador único como poderia parecer. Os IDs 0 e 1 se repetem três vezes cada e o ID 2 duas vezes enquanto apenas os IDs 3 e 4 são exclusivos. Isso ocorre porque cada arquivo XML possui seus próprios gêneros e o framework .NET irá manter o relacionamento entre gêneros e filmes através do Genero_Id mas apenas para um arquivo XML por vez. Quando o próximo arquivo XML for carregado, o Genero_ID será zerado novamente. Isso significa que ele sozinho não pode ser utilizado como identificador já que ele é único por arquivo (esse mesmo comportamento ocorre com o SSIS).

Se o Genero_ID é único por arquivo e o nome do arquivo é conhecido, pode-se fazer um JOIN para obter a lista completa de filmes. Ex:

SELECT
    G.ArquivoXML, G.Tipo, F.Filme_text As Filme, DataPrevista
FROM
    Genero As G INNER JOIN Filme As F ON
        G.Genero_ID = F.Genero_ID AND
        G.ArquivoXML = F.ArquivoXML

Essa consulta possui o seguinte resultado (abreviado):

Arquivo XML

Gênero

Filme

Data Prevista

G:\XML\CLR\Filmes01.xml

Ação

Avatar

18/12/2009

G:\XML\CLR\Filmes01.xml

Ação

G.I. Joe: A Origem de Cobra

07/08/2009

G:\XML\CLR\Filmes01.xml

Ação

Street Fighter: The Legend of Chun-Li

13/03/2009

G:\XML\CLR\Filmes01.xml

Ação

Transformers 2

17/07/2009

G:\XML\CLR\Filmes01.xml

Animação

Coraline e o Mundo Secreto

13/02/2009

G:\XML\CLR\Filmes01.xml

Animação

Monstros vs. Alienígenas

03/04/2009

G:\XML\CLR\Filmes02.xml

Comédia

A Pantera Cor-de-Rosa 2

20/02/2009

G:\XML\CLR\Filmes02.xml

Comédia

Os Piratas do Rock

29/05/2009

G:\XML\CLR\Filmes02.xml

Romance

Ele Não Está Tão a Fim de Você

10/04/2009

G:\XML\CLR\Filmes02.xml

Romance

The Proposal

07/08/2009

G:\XML\CLR\Filmes02.xml

Documentário

Mamonas, o Documentário

06/03/2009

G:\XML\CLR\Filmes03.xml

Drama

Efeito Borboleta 3

15/05/2009

G:\XML\CLR\Filmes03.xml

Drama

Veronika Decide Morrer

01/05/2009

G:\XML\CLR\Filmes03.xml

Ficção

District 9

09/10/2009

G:\XML\CLR\Filmes03.xml

Ficção

The Box

17/04/2009

G:\XML\CLR\Filmes03.xml

Policial

Anjos e Demônios

15/05/2009

G:\XML\CLR\Filmes03.xml

Policial

Public Enemies

03/07/2009

G:\XML\CLR\Filmes03.xml

Suspense

Alma Perdida

27/02/2009

G:\XML\CLR\Filmes03.xml

Terror

Dia dos Namorados Macabro 3D

13/03/2009

G:\XML\CLR\Filmes03.xml

Terror

Sexta-Feira 13

20/02/2009

Se a mesma consulta for feita visualmente contra os arquivos XML, ou ainda se a solução baseada em CLR for utilizada (pois possui os mesmos arquivos), os resultados serão exatamente os mesmos, ou seja, a carga foi realizada com êxito.

O ponto forte dessa solução em relação as demais apresentadas é que ela não depende da criação prévia das tabelas no banco de dados. Não é necessário sequer conhecer o XSD para que a carga seja realizada. O próprio aplicativo encarrega-se de interpretar o arquivo XSD e derivar as tabelas necessárias para montar uma estrutura relacional equivalente. Ao realizar um teste com os arquivos XML de ofertas de produtos utilizado na solução do SSIS, a solução mostrou que a importação ocorre sem problemas.

Uma vez que o aplicativo não demande o conhecimento prévio das estruturas (seja relacional ou XML) ele está mais apto para a importação de documentos do que as soluções anteriores. Ainda assim ele não é um aplicativo completo. O tratamento de erro é precário, o mapeamento dos tipos do C# para o SQL Server está incompleto e não é permitido outra autenticação que não a integrada ao Windows. Isso foi proposital, pois, a idéia era demonstrar como fazer um pequeno aplicativo e não uma solução completa de ETL. Com essa alternativa, demonstro algumas das soluções comuns para lidar com a importação de arquivos XML para o SQL Server. Espero que importar os dados em XML para o SQL Server não seja mais uma dor de cabeça.

[ ]s,

Gustavo

7 Respostas para “Como importar arquivos XML para o SQL Server – Parte IV

  1. Obrigado Gustavo,a parte 4 do tutorial fico muito boa.Abs

  2. Oi Will,Que bom que gostou. Espero que ajude você e muitos outros.Abs,

  3. Gustavo, quero parabenizar pela iniciativa que é muito louvavel. Sou programador Delphi, estou migrando para o c#.Pesquisando pela internet encontrei seus artigos e então fique muito animado em continuar com o #.No artigo "Como importar arquivos XML para o SQL Server – Parte IV" não estou conseguindo Cria as tabelas necessáriasna linha "p.CriarTabelasSQL(ArquivoXSD, ServidorSQL, BancoSQL);", da a seguinte mensagem: Error 1 The name \’BancoSQL\’ does not exist in the current context. Você pode me dizer onde estou errando?

  4. Olá Edson,Obrigado pelo feedback. Já programei em Delphi e C# (há algum tempo) e particularmente prefiro o C#. Os métodos que declarei precisam de uma variável BancoSQL para definir em qual banco serão criadas as tabelas. A julgar pela mensagem de erro em questão, me parece que você passou para a variável BancoSQL a string "BancoSql". Não há problema nisso, mas é necessário que no seu servidor exista um "BancoSQL" para que o código funcione, ou que você informe o nome do banco corretamente. No meu exemplo, o banco no SQL Server chama-se simples "Banco", mas poderia ser BDLojas, BDProdutos, etc. Basta que no arquivo XML você defina o nome correto. Verifique também se a sua conta tem as permissões necessárias.[ ]s,Gustavo

  5. Gustavo, obrigado por retornar a minha mensagem. O nome do arquivo config é Config.xml, então mudei a seguinte linha// Captura o local do arquivo de configuracao string ArquivoConf = args[0]; para "string Config = args[0];" esta correto?

  6. Glauber Pinto

    Gustavo, Parabéns pela post muito bom mesmo. continue assim. ajudou bastante.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s