Como importar arquivos XML para o SQL Server – Parte III

Olá Pessoal,

Dando continuidade a exportação de arquivos XML para o SQL Server, hoje demonstrarei mais uma forma de como fazer isso utilizando os recursos aplicados ao SQL Server 2005 e 2008. Já mostramos como fazer isso através de um código TSQL simples e através de uma solução mais robusta utilizando o SSIS. A próxima alternativa utiliza um recurso disponível nessas versões que é o SQLCLR (Common Language Runtime).

Já falei do SQLCLR em alguns artigos anteriores como Concatenando registros e Matrizes no SQL Server 2005 – Parte III. Há uma vasta bibliografia sobre assunto além de muitos artigos técnicos aprofundados. Caso haja interesse, há um artigo muito bom sobre esse assunto nos links abaixo:

Como e quando programar em CLR no Microsoft SQL Server 2005
http://www.microsoft.com/brasil/msdn/Tecnologias/sql/CLRSQLServer2005.mspx

O MSDN Experience também possui alguns vídeos sobre SQLCLR que são interessantes. Há mais informações sobre o MSDN Experience no link abaixo:

MSDN Experience SQL Server
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!158.entry

A utilização do SQLCLR provê uma excelente extensão para a parte de programação de banco de dados. Uma vez que as classes .NET estão disponíveis para utilização plena e integrada ao SQL Server, será possível construir stored procedures poderosas para realizar o trabalho de extração de dados. Poderíamos utilizar um function para recuperar o conteúdo do XML e posteriormente utilizar um comando TSQL para gravá-lo nas tabelas necessárias, mas essa solução seria muito semelhante à utilização do comando OPENROWSET apresentado em "Como importar arquivos XML para o SQL Server – Parte I" só que com muito mais esforço. Mostrarei algumas outras alternativas com o CLR.

Antes de demonstrar o código .NET que irá providenciar essa importação é necessário um exemplo para ser utilizado nas cargas. A Web não possui algumas listas de exemplo do nosso dia a dia, então pesquisando um pouco a respeito, consegui encontrar uma lista de filmes previstos para esse ano de 2009. Interessante que alguns eu não sabia nem ao menos que seria lançados (fiquei ansioso agora). Os seguintes arquivos XML serão utilizados como exemplo. Caso necessário altere os diretórios conforme a necessidade.

Arquivo 1 – G:\XML\CLR\Filmes01.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="
http://filmes.br/filmes">
 <Genero Tipo="Ação">
  <Filme DataPrevista="2009-03-20">Anjos da Noite: A Revolução</Filme>
  <Filme DataPrevista="2009-12-18">Avatar</Filme>
  <Filme DataPrevista="2009-08-14">G-Force</Filme>
  <Filme DataPrevista="2009-08-07">G.I. Joe: A Origem de Cobra</Filme>
  <Filme DataPrevista="2009-05-08">Star Trek</Filme>
  <Filme DataPrevista="2009-03-13">Street Fighter: The Legend of Chun-Li</Filme>
  <Filme DataPrevista="2009-07-17">Transformers 2</Filme>
  <Filme DataPrevista="2009-05-22">Uma Noite no Museu 2</Filme>
  <Filme DataPrevista="2009-06-05">Velozes e Furiosos 4</Filme>
  <Filme DataPrevista="2009-03-06">Watchmen – O Filme</Filme>
  <Filme DataPrevista="2009-04-30">X-Men Origins: Wolverine</Filme>
  <Filme DataPrevista="2009-04-09">Dragonball</Filme>
  <Filme DataPrevista="2009-06-05">O Exterminador do Futuro: A Salvação</Filme>
 </Genero>
 <Genero Tipo="Animação">
  <Filme DataPrevista="2009-02-13">Coraline e o Mundo Secreto</Filme>
  <Filme DataPrevista="2009-04-03">Monstros vs. Alienígenas</Filme>
 </Genero>
</Filmes>

Arquivo 2 – G:\XML\CLR\Filmes02.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="http://filmes.br/filmes">
 <Genero Tipo="Comédia">
  <Filme DataPrevista="2009-02-20">A Pantera Cor-de-Rosa 2</Filme>
  <Filme DataPrevista="2009-04-24">Eu Te Amo, Cara</Filme>
  <Filme DataPrevista="2009-02-06">Noivas em Guerra</Filme>
  <Filme DataPrevista="2009-06-12">NowhereLand</Filme>
  <Filme DataPrevista="2009-05-29">Os Piratas do Rock</Filme>
  <Filme DataPrevista="2009-02-20">Um Hotel Bom pra Cachorro</Filme>
 </Genero>
 <Genero Tipo="Romance">
  <Filme DataPrevista="2009-06-05">A Mulher Invisível</Filme>
  <Filme DataPrevista="2009-04-10">Delírios de Consumo de Becky Bloom</Filme>
  <Filme DataPrevista="2009-04-10">Ele Não Está Tão a Fim de Você</Filme>
  <Filme DataPrevista="2009-08-07">The Proposal</Filme>
 </Genero>
 <Genero Tipo="Documentário">
  <Filme DataPrevista="2009-03-06">Mamonas, o Documentário</Filme>
 </Genero>
</Filmes>

Arquivo 3 – G:\XML\CLR\Filmes03.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Filmes xmlns="http://filmes.br/filmes">
 <Genero Tipo="Drama">
  <Filme DataPrevista="2009-05-15">Efeito Borboleta 3</Filme>
  <Filme DataPrevista="2009-04-10">Hachiko: A Dog’s Story</Filme>
  <Filme DataPrevista="2009-05-01">Veronika Decide Morrer</Filme>
 </Genero>
 <Genero Tipo="Ficção">
  <Filme DataPrevista="2009-10-09">District 9</Filme>
  <Filme DataPrevista="2009-04-17">The Box</Filme>
 </Genero>
 <Genero Tipo="Policial">
  <Filme DataPrevista="2009-05-15">Anjos e Demônios</Filme>
  <Filme DataPrevista="2009-07-03">Public Enemies</Filme>
  <Filme DataPrevista="2009-04-24">State of Play</Filme>
 </Genero>
 <Genero Tipo="Suspense">
  <Filme DataPrevista="2009-02-27">Alma Perdida</Filme>
  <Filme DataPrevista="2009-04-17">Duplicidade</Filme>
  <Filme DataPrevista="2009-04-03">O Lobisomem</Filme>
 </Genero>
 <Genero Tipo="Terror">
  <Filme DataPrevista="2009-03-13">Dia dos Namorados Macabro 3D</Filme>
  <Filme DataPrevista="2009-05-29">O Mistério das Duas Irmãs</Filme>
  <Filme DataPrevista="2009-02-20">Sexta-Feira 13</Filme>
 </Genero>
</Filmes>

Para validar os arquivos com a lista de filmes, o schema abaixo será utilizado

Arquivo de validação – G:\XML\CLR\Filmes.xsd

<?xml version="1.0" encoding="iso-8859-1"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="http://filmes.br/filmes">
 <xs:element name="Filmes">
  <xs:complexType>
   <xs:sequence>
    <xs:element maxOccurs="5" name="Genero">
     <xs:complexType>
      <xs:sequence>
       <xs:element maxOccurs="15" name="Filme">
        <xs:complexType>
         <xs:simpleContent>
          <xs:extension base="xs:string">
           <xs:attribute name="DataPrevista" type="xs:date" use="required"/>
          </xs:extension>
         </xs:simpleContent>
        </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="Tipo" type="xs:string" use="required"/>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

Dessa vez há algumas diferenças em relação aos arquivos XML e XSD anteriores. Nos arquivos XML há um atributo xmlns no root e no arquivo XSD há um atributo targetNamespace ambos apontando para um endereço (http://filmes.br/filmes) existe ainda um atributo chamado elementFormDefault com o valor qualified. Não entrarei nos detalhes do que significam esses atributos, mas por hora basta saber que para o Framework .NET validar arquivos XML é necessário que os arquivos XML tenham o atributo xmlns (namespace padrão) e o arquivo xsd tenha a tag targetNamespace que é o namespace a ser validado e que os elementos sejam declarados como qualificados (ainda que haja um namespace default). Poderíamos utilizar também o atributo xsi:nonamespaceschemalocation, mas preferi utilizar os anteriores. Vale a pena lembrar que o link é apenas um nome e que ele não necessita existir realmente.

O próximo passo é criar uma stored procedure utilizando as classes para se trabalhar com o CLR e o SQL Server. Como será feita uma extração de dados em XML, será necessário utilizar os namedspaces para trabalhar com o XML. O script abaixo é introdutório para entendermos o que a stored procedure se propõe a fazer. O código está repleto de comentários auto-explicativos.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void UspCarregarXml(string ArquivoXML)
  {
    // Captura o conteúdo do arquivo XML
    XmlDocument xd = new XmlDocument();
    xd.Load(ArquivoXML);

    // Cria uma referência para o root do documento
    XmlElement xmlElem = xd.DocumentElement;

    // Faz os ajustes para trabalhar com Namedspace
    XmlNamespaceManager xnm = new XmlNamespaceManager(xd.NameTable);
    xnm.AddNamespace("f", @"
http://filmes.br/filmes&quot;);

    // Para cada gênero encontrado no XML
    foreach (XmlNode xmlGenero in xmlElem.SelectNodes(@"/f:Filmes/f:Genero", xnm))
    {
      // Captura o gênero
      string strGenero = xmlGenero.Attributes[0].Value;

      // Para cada filme encontrado no gênero
      foreach (XmlNode xmlFilme in xmlGenero.SelectNodes(@"f:Filme", xnm))
      {
        // Captura o data prevista do filme
        string strDataFilme = xmlFilme.Attributes[0].Value;
        string strFilme = xmlFilme.InnerText;

        // Gera os comandos de INSERT
        string strSQL = @"INSERT INTO Filmes (Genero, DataPrevista, Nome)" +
          " VALUES (‘[0]’,'[1]’,'[2]’)";

        strSQL = strSQL.Replace(@"[0]", strGenero);
        strSQL = strSQL.Replace(@"[1]", strDataFilme);
        strSQL = strSQL.Replace(@"[2]", strFilme);

        // Dispara a saída para o SQL em formato texto (estilo PRINT)
        SqlContext.Pipe.Send(strSQL);
      }
    }
  }
}

Como não necessariamente o Visual Studio está disponível, mostrarei como compilar esse código seu utilizá-lo. Basta seguir os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como UspGeraInsertsXML.cs em algum diretório (ex: G:\XML\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library G:\XML\CLR\UspGeraInsertsXML.cs (supondo que o arquivo esteja em G:\XML\CLR\)
  • Localizar o arquivo UspGeraInsertsXML.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: G:\XML\CLR\)

O próximo passo é registrar o Assembly no SQL Server para que seus métodos possam ser utilizados (no caso a stored procedures).

— Habilita o uso do CLR na instância do SQL Server
EXEC sp_configure ‘clr enabled’, 1

— Força que as alterações tenham efeito sem a necessidade de reiniciar o serviço
RECONFIGURE WITH OVERRIDE

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY ImportarXML FROM ‘G:\XML\CLR\UspGeraInsertsXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

Após tentar registrar o ASSEMBLY, uma mensagem de erro pode aparecer

Msg 10327, Level 14, State 1, Line 2
CREATE ASSEMBLY for assembly ‘UspGeraInsertsXML’ failed
    because assembly ‘UspGeraInsertsXML’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true:
   the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
   or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
   If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
   If not, use sp_changedbowner to fix the problem.

Normalmente um assembly é utilizado com o nível de permissão SAFE, ou seja, ele não acessará recursos externos ao SQL Server. Como será necessário importar alguns arquivos XML, será necessário acesso a recursos externos ao SQL Server e nesse caso a segurança fica um pouco mais comprometida senão há confiança no Assembly. Como o Assembly tem o seu código conhecido, será autorizada sua utilização através da configuração da propriedade Trustworthy do banco de dados.

— Altera a propriedade Trustworthy para ON
ALTER DATABASE BANCO SET TRUSTWORTHY ON

Agora o Assembly poderá ser registrado sem problemas (essa não é a melhor prática, mas evita a necessidade de se criar chaves e certificados para registrar o Assembly).

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY CarregarXML FROM ‘G:\XML\CLR\UspGeraInsertsXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

— Cria uma stored procedure baseada no Assembly
CREATE PROCEDURE UspCarregarXML
    @ArquivoXML NVARCHAR(128)
AS
    EXTERNAL NAME
[CarregarXML].[StoredProcedures].UspCarregarXml

— Executa a Stored Procedure
EXEC UspCarregarXML @ArquivoXML = ‘G:\XML\CLR\Filmes02.xml’

Esse exemplo foi apenas uma "pincelada" no que o CLR pode ajudar em relação a importação de arquivos XML. Esse é um exemplo muito simples. O código em questão não contempla a validação dos documentos XML através de um XML Schema, não nos permite escolher a tabela de destino, e não carrega efetivamente o documento XML (apenas lista os comandos de INSERT). O próximo exemplo é um pouco mais elaborado.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Schema;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void UspImportarXml(
    string ArquivoXML, string ArquivoXSD,
    string TabelaDestino, string ColunasDestino)
  {
    // Cria um documento XML com base no Arquivo XML
    XmlDocument xd = new XmlDocument();

    // Cria uma conexão com o banco de dados usando o contexto atual
    SqlConnection cn = new SqlConnection("Context Connection=True");

    try
    {
      // Cria os objetos necessários para validação
      XmlReaderSettings xrs = new XmlReaderSettings();
      xrs.Schemas.Add(@"
http://filmes.br/filmes&quot;, ArquivoXSD);
      xrs.ValidationType = ValidationType.Schema;

      // Carrega o XML segundos os critérios de validação do XSD
      XmlReader xr = XmlReader.Create(ArquivoXML, xrs);
      xd.Load(xr);

      // Cria uma referência para o root do documento
      XmlElement xmlElem = xd.DocumentElement;

      // Faz os ajustes para trabalhar com Namedspace
      XmlNamespaceManager xnm = new XmlNamespaceManager(xd.NameTable);
      xnm.AddNamespace("f", @"
http://filmes.br/filmes&quot;);

      // Abre uma conexão com o banco usando o contexto atual
      cn.Open();

      // Instancia um comando para inserir os registros
      SqlCommand cmd = cn.CreateCommand();

      // Para cada gênero encontrado no XML
      foreach (XmlNode xmlGenero in xmlElem.SelectNodes(@"/f:Filmes/f:Genero", xnm))
      {
        // Captura o gênero
        string strGenero = xmlGenero.Attributes[0].Value;

        // Para cada filme encontrado no gênero
        foreach (XmlNode xmlFilme in xmlGenero.SelectNodes(@"f:Filme", xnm))
        {
          // Captura o data prevista do filme
          string strDataFilme = xmlFilme.Attributes[0].Value;
          string strFilme = xmlFilme.InnerText;

          // Gera os comandos de INSERT
          string strSQL = @"INSERT INTO " + TabelaDestino + "(" +
              ColunasDestino + ")" + " VALUES (‘[0]’,'[1]’,'[2]’)";

          strSQL = strSQL.Replace(@"[0]", strGenero);
          strSQL = strSQL.Replace(@"[1]", strDataFilme);
          strSQL = strSQL.Replace(@"[2]", strFilme);

          // Insere os registros no banco de dados
          cmd.CommandText = strSQL;
          cmd.ExecuteNonQuery();
        }
      }

      string strTotalRegistros = "Foram inseridos " +
        xmlElem.SelectNodes(@"/f:Filmes/f:Genero/f:Filme", xnm).Count.ToString() +
        " registro(s)";

      SqlContext.Pipe.Send(strTotalRegistros);
    }

    catch (Exception e)
    {
      SqlContext.Pipe.Send("Ocorreram problemas na importação do documento XML");
      SqlContext.Pipe.Send("Erro: " + e.Message);
      SqlContext.Pipe.Send("Origem: " + e.Source);
    }

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

Não irei entrar em detalhes do código (o mesmo está repleto de comentários). Para compilar o código na ausência do Visual Studio basta seguir os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como UspImportarXML.cs em algum diretório (ex: G:\XML\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library G:\XML\CLR\UspImportarXML.cs (supondo que o arquivo esteja em G:\XML\CLR\)
  • Localizar o arquivo UspGeraInsertsXML.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: G:\XML\CLR\)

O próximo passo é registrar o Assembly e criar a stored procedure conforme script abaixo (pressupondo que o Trustworth já está ativo):

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY ImportarXML
FROM ‘G:\XML\CLR\UspImportarXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;

— Cria a stored procedure conforme o código
CREATE PROCEDURE UspImportarXml
    @ArquivoXML NVARCHAR(150),
    @ArquivoXSD NVARCHAR(150),
    @TabelaDestino NVARCHAR(50),
    @ColunasDestino NVARCHAR(100)
AS EXTERNAL NAME [ImportarXML].StoredProcedures.UspImportarXml

Antes de propriamente executar a stored procedure, é necessário criar uma tabela para receber os resultados.

— Declara uma tabela
CREATE TABLE Filmes (Genero VARCHAR(50),
    Data SMALLDATETIME, Nome VARCHAR(100))

— Importa os registros em XML validando-os
EXEC UspImportarXml
    @ArquivoXML = ‘G:\XML\CLR\Filmes01.xml’,
    @ArquivoXSD = ‘G:\XML\CLR\Filmes.xsd’,
    @TabelaDestino = ‘Filmes’,
    @ColunasDestino = ‘Genero, Data, Nome’

— Verifica o conteúdo da tabela
SELECT Genero, Data, Nome FROM Filmes

— Exclui a tabela
DROP TABLE Filmes

Um teste interessante para verificar se o XML está de fato "validando" o arquivo XML com base no arquivo XSD é mudar o XSD para provocar um problema de validação. Como o XSD especifica a tag <xs:element maxOccurs="5" name="Genero"> em outras palavras em cada arquivo XML podem haver apenas 5 gêneros no máximo. O arquivo Filme03.xml possui exatamente 5 gêneros (Drama, Ficção, Policial, Suspense e Terror). Se o elemento maxOccurs for reduzido para 4, o código abaixo irá gerar um erro.

— Declara uma tabela
CREATE TABLE Filmes (Genero VARCHAR(50),
    Data SMALLDATETIME, Nome VARCHAR(100))

— Importa os registros em XML validando-os
EXEC UspImportarXml
    @ArquivoXML = ‘G:\XML\CLR\Filmes03.xml’,
    @ArquivoXSD = ‘G:\XML\CLR\Filmes.xsd’,
    @TabelaDestino = ‘Filmes’,
    @ColunasDestino = ‘Genero, Data, Nome’

Após executar esse código, a mensagem de erro abaixo é exibida:

Ocorreram problemas na importação do documento XML
Erro: The element ‘Filmes’ in namespace ‘http://filmes.br/filmes&#8217; has invalid child element ‘Genero’ in namespace ‘http://filmes.br/filmes&#8217;.
Origem: System.Xml

Isso ocorre porque após mudar o atributo maxOccurs para 4, o número máximo de gêneros permitidos por arquivo será igual a 4. Como o arquivo 3 possui 5 gêneros, o último gênero é um elemento inesperado (invalid child element) dentro do elemento Filmes produzindo o erro. Isso mostra que os arquivos XML estão de fato sendo validados antes da importação. Se o maxOccurs voltara para 5, o código irá gravar os registros sem erros.

A sugestão pelo CLR é interessante uma vez que amplia bastante o poder do TSQL. Os códigos CLR demonstraram uma simples forma de carregar, mas de forma nenhuma esgotam as possibilidades (não coloquei um contexto transacional e nem proteções contra código SQL Injection por exemplo). Sugestões de melhoria incluem utilizar o método GetFiles de uma classe Directory para listar todos os arquivos do diretório e fazer a carga por diretório e não por arquivo ou quem sabe tentar incluir apenas os registros válidos para o Schema e rejeitar os inválidos. Tudo isso é possível com o CLR, mas fica como "dever de casa" para quem estiver interessado em investir nessa alternativa.

Como limitações, devemos lembrar que o CLR utilizará recursos do SQL Server. O CLR utiliza uma área de memória especial (e bem restrita) do SQL Server e não está incluída na área de memória do Buffer Pool. Essa área de memória especial é inferior a 2GB e é utilizada para manter planos de execução, propriedades de conexão, bloqueios, etc. Processar grandes documentos XML através do CLR pode levar a um rápido esgotamento dessa área provocando fortes contenções e prejudicando o desempenho do SQL Server. É recomendável uma certa cautela, pois, é muito tentador codificar em CLR.

Aos que desejarem utilizar arquivos em compartilhamento de rede não esqueçam que é necessário que a conta de serviço que roda o SQL Server tenha permissões de leitura no compartilhamento onde os arquivos estão localizados. Caso usuários não sysadmin sejam utilizados pode ser necessária a criação de uma credencial já que esses arquivos representam recursos externos ao SQL Server.

[ ]s,

Gustavo

Uma resposta para “Como importar arquivos XML para o SQL Server – Parte III

  1. Obrigado pela 3 parte do tutorial,ainda estou estudando os links do plugmasters tem um otimo material la de sua autoria.Abs

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