Como importar arquivos XML para o SQL Server – Parte II

Bom Dia Pessoal,

No último artigo demonstrei uma forma de importar e validar arquivos XML para o SQL Server 2005 e 2008 através de comandos TSQL. É uma alternativa interessante, visto que o processo de carga pode ser automatizado através de simples comandos TSQL. Embora o SQL Server tenha melhorado significativamente o suporte a BLOBs e possibilitado a importação da maneira que foi apresentada anteriormente, não acho uma boa prática embutir dentro do Engine do SQL Server mecanismos de importação e validação de arquivos XML. A leitura de arquivos XML muito grandes ou em quantidade significativa pode sacrificar recursos de memória e disco e talvez não seja interessante comprometê-los principalmente se os arquivos forem carregados em um horário de forte concorrência.

Uma outra alternativa é a utilização do SSIS que é uma ferramenta mais preparada para lidar com cargas de dados e possui um bom suporte a importação de arquivos XML. Para demonstrar como fazer isso, utilizarei três arquivos XML que possuem ofertas de câmeras digitais. Os arquivos estão com vários elementos e atributos organizados de forma hierárquica. Os caminhos e o nome dos arquivos também são expostos, mas é possível alterá-los livremente desde que o projeto contemple os novos nomes de caminhos.

Arquivo 1 – G:\XML\Oferta01.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Submarino" Data="2009-01-24">
  <Produto>
   <Nome>Câmera Digital 5MP LCD 2</Nome>
   <Marca>Nikon</Marca>
   <Modelo>L10</Modelo>
   <Garantia>12</Garantia>
   <Preco>299.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 8.2MP Easy Share</Nome>
   <Marca>Nikon</Marca>
   <Modelo>C813</Modelo>
   <Garantia>12</Garantia>
   <Preco>549.00</Preco>
  </Produto>
 </Produtos>
</Oferta>

Arquivo 2 – G:\XML\Oferta02.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Kabum" Data="2009-01-22">
  <Produto>
   <Nome>Câmera Digital Cyber-shot 7.2</Nome>
   <Marca>Sony</Marca>
   <Modelo>DSC-W110</Modelo>
   <Garantia>12</Garantia>
   <Preco>648.90</Preco>
  </Produto>
 </Produtos>
</Oferta>

Arquivo 3 – G:\XML\Oferta03.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<Oferta>
 <Produtos Ofertante="Wal Mart" Data="2009-01-26">
  <Produto>
   <Nome>Camera Digital 7.1 MP</Nome>
   <Marca>Canon</Marca>
   <Modelo>A470</Modelo>
   <Garantia>12</Garantia>
   <Preco>499.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 9.1MP</Nome>
   <Marca>Panasonic</Marca>
   <Modelo>DMC TZ5PL K</Modelo>
   <Garantia>12</Garantia>
   <Preco>1299.00</Preco>
  </Produto>
  <Produto>
   <Nome>Câmera Digital 10.0 Megapixels</Nome>
   <Marca>FujiFilm</Marca>
   <Modelo>S1000</Modelo>
   <Garantia>12</Garantia>
   <Preco>1349.90</Preco>
  </Produto>
 </Produtos>
</Oferta>

Para que esses dados possam ser carregados e validados é necessário um arquivo que especifique o esquema dos documentos XML.

Arquivo de Validação – G:\XML\Ofertas.xsd

<?xml version="1.0"?>
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema">
<xs:element name="Oferta">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="Produtos">
     <xs:complexType>
      <xs:sequence>
       <xs:element minOccurs="1" maxOccurs="3" name="Produto">
        <xs:complexType>
         <xs:sequence>
          <xs:element name="Nome" type="xs:string"/>
          <xs:element name="Marca" type="xs:string"/>
          <xs:element name="Modelo" type="xs:string"/>
          <xs:element name="Garantia" type="xs:integer"/>
          <xs:element name="Preco" type="xs:decimal"/>
         </xs:sequence>
        </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="Ofertante" type="xs:string" use="required"/>
      <xs:attribute name="Data" type="xs:date" use="required"/>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
</xs:element>
</xs:schema>

Para que a carga dos arquivos XML possa ser realizada é necessário primeiro criar as tabelas de destino para receber os dados do XML. Essa etapa deve ser feita antes da carga de dados. Para efeitos de simplicidade já que o objetivo é extrair informações do XML (e não prover um roteiro completo de carga) criarei uma única tabela desnormalizada. As colunas textuais estão como NVARCHAR, pois, o XML trabalha os dados em UNICODE. Se as colunas forem do tipo VARCHAR ou CHAR, um erro de execução ocorrerá durante o pacote.

CREATE TABLE Ofertas (
    Ofertante NVARCHAR(50), Data SMALLDATETIME,
    NomeProduto NVARCHAR(80), Marca NVARCHAR(30),
    Modelo NVARCHAR(20), Garantia TINYINT,
    Preco SMALLMONEY
    )

Eu poderia utilizar o SSIS do SQL Server 2008 na demonstração, mas para manter a compatibilidade, utilizei o SSIS do SQL Server 2005 para a tarefa de importar os arquivos XML para dentro do SQL Server. Vale a pena lembrar que para esse exemplo não há diferenças entre o SSIS 2005 e o 2008. O primeiro passo naturalmente é criar um novo projeto no SSIS através do BIDS (Business Intelligence Development Studio) do tipo Integration Services Project.

Como a importação será de vários arquivos, é necessário utilizar um Container do tipo ForEachLoopContainer, pois, faremos um loop até que todos os arquivos XML sejam carregados através de uma tarefa Data Flow. As seguintes propriedades devem estar preenchidas com os respectivos valores (feche e abra as propriedades do objeto antes de definir Variable Mapping):

Objeto General Collection Variable Mapping
ForEachLoop Container

Name: VarrerArquivosXML

Enumerator: For Each File Enumerator
Folder: G:\XML
Files: *.xml
Retrieve File Name: Fully Qualified

Variable: User::ArquivoXML
Index: 0
Crie um nova variável (New Variable)

    Container: VarrerArquivosXML
    Name: ArquivoXML
    NameSpace: User
    Value Type: string
Data Flow Name: ImportarArquivosXML

O resultado final deve ser algo semelhante a figura abaixo:

A parte de ControlFlow está finalizada. Já possuímos um container que irá varrer todos os arquivos .xml de uma determinada pasta e o nome do arquivo ficará disponível em uma variável chamada User::ArquivoXML. O próximo passo é montar a parte de Data Flow.

Como a fonte de dados é XML, é necessário utilizar o XML Source para capturar os dados. As seguintes propriedades na guia Connection Manager devem ser configuradas (opcionalmente troque a propriedade Name para Produtos em Properties):

  • Data Access Mode: XML File From Variable
  • Variable Name: User::ArquivoXML
  • XSD Location: G:\XML\Ofertas.xsd

Como o XML é hierárquico, ao clicar na guia Columns, no momento de mapear os dados aparecerão duas saídas (Output Name) conforme a figura abaixo:

Isso ocorre porque o XML em questão possui múltiplos elementos e atributos e portanto múltiplas saídas são geradas. Existem basicamente dois conjuntos de resultados. O primeiro é "Produto" que contém as características do produto (Nome, Marca, Modelo, Garantia e Preço). O segundo é "Produto" que contém o nome do ofertante e a data da oferta. Como existe apenas uma tabela para receber esses dados será necessário combinar essas saídas para carregar a tabela de Ofertas.

Em ambas as saídas (na própria guia Columns), há uma coluna chamada Produtos_ID que não está presente no XML. Essa coluna é criada pelo SSIS é através dela que é feito o Link entre as saídas "Produto" e "Produtos". Utilizaremos essa coluna logo em seguida para fornecer uma única saída através da transformação MERGE JOIN.

Antes de prosseguir há uma tarefa que se faz necessária em relação à transformação MERGE JOIN. Essa transformação requer que os dados estejam ordenados para funcionar. Para que possamos ordenar as saídas "Produto" e "Produtos" é necessário seguir os seguintes passos:

  • Clicar sobre produtos com o botão direito e em seguida (Show Advanced Editor)
  • Ir até a aba Input and Output Properties
  • Clicar sobre a saída Produto e marcar a propriedade IsSorted como True
  • Expandir a saída Produto, Output Columns e em seguida clicar Produtos_ID
  • Marcar a propriedade SortKeyPosition como 1
  • Repetir o mesmo processo para a saída "Produtos"

As figuras abaixo podem ser úteis para realizar o processo

Configuração da Propriedade IsSorted

Configuração da Propriedade SortKeyPosition

O próximo passo é adicionar a transformação MERGE JOIN para realizar a junção das saídas "Produto" e "Produtos". Após adicionar a transformação MERGE JOIN é necessário ligar as saídas da origem XML Source (ou Produtos) para a transformação MERGE JOIN através da seta verde em XML Source (Produtos). Como são duas saídas, a seta terá de ser ligada duas vezes.

Ao fazer a ligação entre XML Source (Produtos) e MERGE JOIN aparecerá uma caixa de diálogo com as opções OUTPUT e INPUT. Escolha como OUTPUT a opção "Produto" e como INPUT a opção "Merge Join Right Input". Faça uma nova ligação entre Xml Source (Produtos) e MERGE JOIN (nada acontecerá, mas ficarão duas setas verdes). Posteriormente dê dois clique sobre MERGE JOIN. Uma tela muito similar ao Query Designer aparecerá:

Marque as colunas de "Produtos" e em seguida de "Produto" para que todas as colunas do XML possam ser contempladas. Troque o Output Alias de Nome para NomeProduto, pois, esse é o nome da coluna na tabela (essa tarefa não é obrigatória, mas diminui necessidades de mapeamento). Posteriormente basta adicionar uma fonte de dados de destino (OleDB Destination), configurá-la para conectar-se ao SQL Server (no banco e tabela criados) e ligar a transformação MERGE JOIN na fonte que conecta-se ao SQL Server. Algo parecido com a figura abaixo:

Existem um aviso (Warning) em OLEDB Source Destination (eu renomeie para SQL Server) relacionado ao tamanho das colunas "Truncation may occur due…". Essa mensagem aparece porque os dados que foram definidos no esquema (Ofertas.xsd) define apenas que algumas colunas são do tipo xs:string sem delimitação (situação típica de esquemas que foram gerados através de ferramentas). Com algumas alterações no esquema esse aviso desaparecerá (embora ele não impede que a carga seja executada). Se o pacote for executado, a tabela Ofertas será populada e poderá ser consultada após a carga.

A carga de documentos XML através do SSIS pode parecer um pouco mais trabalhosa, mas vale a pena lembrar que as capacidades são bem maiores já que se trata de uma ferramenta adaptada para carga de dados. É possível carregar documentos XML não só para bases SQL Server, como ORACLE, DB2, etc. Com a diversidade de tarefas existentes nas abas Control Flow e Data Flow há a possibilidade de excluir os arquivos após processamento (recomendável para os casos em que a importação ocorrer com êxito), de comparar o conteúdo do arquivo XML com o existente no banco de dados, combinar os dados XML com outras fontes antes de carregá-los, etc. Essas possibilidades tornam o SSIS uma excelente alternativa para importar arquivos XML para o SQL Server.

Embora o SSIS tenha muitos recursos, tenho apenas duas ressalvas quanto a sua utilização para importação de dados a partir de documentos XML. A primeira ressalva é que o esquema (Oferta.xsd) não valida de fato os documentos XML a serem importados. Ele serve para mapear a estrutura XML em Rowsets para posterior tranformação ou carga, mas a validação não ocorre efetivamente. No arquivo em questão, colocou-se a restrição maxOccurs = 3 para elementos do tipo produto. O arquivo Ofertas03.xml tem a relação de três produtos. Se a restrição for diminuída para 2, por exemplo, o arquivo Oferta03.xml deveria apresentar erros, pois, viola o esquema definido, mas isso não ocorre. A segunda ressalva (e talvez mais grave) é que a importação de arquivos xml mais complexos necessita do operador MERGE JOIN para combinar os rowsets. Como o operador MERGE JOIN está limitado a combinar duas fontes de dados (Left e Right), caso o XML produza mais de dois rowsets, o operador MERGE JOIN não poderá ser utilizado para extrair todo o conteúdo do XML. Nesse caso será necessário prover os dados em um formato com menos rowsets ou aplicar uma transformação prévia do tipo XSLT. Embora o SSIS possa fazê-lo, isso, pode limitar seu desempenho para arquivos XML muito grandes.

Para os iniciantes em SSIS e aqueles que tiveram pouco contato com essa ferramenta, alguns pontos desse artigo podem ficar um pouco confusos. Para facilitar o entendimento, disponibilizei o projeto e os arquivos necessários em minha pasta pública. Há duas versões do pacote (uma para 2005 e uma para 2008). Talvez seja necessário fazer os devidos ajustes nos diretórios. Nos pacotes em anexo, o XML Schema utilizado já contempla o tamanho máximo e o Warning será ocultado. Os interessados podem navegá-la ou clicar no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/ImportarXMLSSIS.zip

[ ]s,

Gustavo

6 Respostas para “Como importar arquivos XML para o SQL Server – Parte II

  1. Obrigado Gustavo tava esperando a 2 parte desse tutorial

  2. Olá Will,Aguarde, pois, as partes III e IV já estão chegando…

  3. Ola Gustavo…Encontrei seu live por acaso em uma pesquisa sobre SQL que estava fazendo… achei ele muito bom mesmo… realmente adorei…Estava lendo essa postagem, mas acredito que ouve algo de errado com as figuras, pois nao estou conseguindo visualizar… a partir do item "Como o XML é hierárquico, ao clicar na guia Columns, no momento de mapear os dados aparecerão duas saídas (Output Name) conforme a figura abaixo: " nenhuma figura eu estou visualizando…Acredito que o trabalho que tem para apresentar novidades e sanar dúvidas é muito importante para diversas pessoas, inclusive para mim…Até logo

  4. Olá Renata,Esse artigo realmente deu alguns problemas na hora de postar, mas achei que estavam corrigidos.Obrigado pelo Feedback. Vou providenciar assim que possível.Abs,

  5. Eu gostaria de criar as tabelas a partir de um arquivo XSD, e possivel????
    Contato: messias.gama@gmail.com

    • Oi Messias,

      É possível criar sim as tabelas a partir de um arquivo XSD, mas não há nada no Integration Services que faça isso durante a execução do pacote. O que você pode fazer é utilizar uma aplicação em .NET que faça a conversão de um XmlDocument para uma Data Table e aí efetuar a criação. É uma trabalho mais maçante admito, mas confesso que do ponto de vista de banco de dados não conheço nada que possa automatizar isso senão recorrer à programação.

      [ ]s,

      Gustavo

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