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