Como importar arquivos XML para o SQL Server – Parte I

Olá Pessoal,

Há uma certa dúvida que presencio já algum tempo e com uma certa freqüência. Eu nunca havia parado para abordá-la da forma que fato rotineiramente, mas recentemente um de meus ex-alunos (na verdade é uma aluna) dos cursos de SQL Server me abordou sobre essa dúvida. A dúvida consistia em como importar arquivos XML para o SQL Server 2000. Havia dois tipos de arquivos (xml e xsd). Os arquivos XML consistem nos dados a serem importados e os arquivos xsd (Xml Schema Definition) consistem nos descritores dos arquivos XML utilizados para validação.

O SQL Server 2000 tem um suporte a XML bem limitado e isso é perfeitamente compreensível. Estamos falando de uma release de quase uma década atrás e na ocasião do lançamento o XML estava bastante distante de ser o que é hoje. Alguns padrões como XPath, Xml Schema e XQuery (indispensáveis para trabalhar com XML hoje) tornaram-se recomendação oficial praticamente no ano 2000 e nessa época o SQL Server 2000 já estava "pronto" (o primeiro Beta data de outubro de 1999). Não era de se esperar que o suporte no SQL Server 2000 contemplasse todos esses padrões (A XQuery e a XPath 2.0 só foram finalizadas em 2007).

Embora o SQL Server 2000 não disponibilize nenhum meio nativo (ou pelo menos mais simples) de importar arquivos XML, existem algumas alternativas. Pode-se utilizar a tarefa Parallel Data Pump do DTS (não disponível graficamente), partir para a stored procedure sp_xml_preparedocument ou utilizar as stored procedures sp_OACreate para instanciar componentes COM+ e utilizar bibliotecas mais preparadas para o XML. Nenhuma dessas alternativas é fácil de trabalhar e algumas delas limitam muito o tamanho do XML em questão além de não serem nada performáticas. Há outras alternativas relacionadas ao ADO e ao XML Bulk Load, mas também não são muito triviais de se trabalhar.

Uma das funcionalidades mais poderosas que o SQL Server disponibilizou a partir do SQL Server 2005 na minha opinião foi um suporte melhor para o XML. Digo um suporte melhor, porque embora o SQL Server 2005 seja muito superior ao 2000 no que tange a XML, ele ainda não conseguiu oferecer todo o suporte necessário pelas mesmas razões do SQL Server 2000 (data de lançamento anterior ao amadurecimento dos padrões). Em todo caso, o SQL Server 2005 já cobre muitas das necessidades comuns que envolvem o XML e na versão 2008 esse suporte foi ainda mais trabalhado e de fato está muito próximo das possibilidades oferecidas pelo W3C.

Conversando com minha ex-aluna, ela me disse que possui o SQL Server 2005 em seu ambiente. Combinei com ela de demonstrar como fazer a importação de dados em XML para o 2005, pois, se ela tiver êxito, poderá exportar os dados em formato tabular do SQL Server 2005 para o 2000 usando o DTS ou o SSIS. Me dedicarei nos próximos artigos a demonstrar que mecanismos o SQL Server 2005 e o SQL Server 2008 possuem para importar arquivos XML.

Antes de prosseguirmos, utilizarei um arquivo XML de estrutura bem simples localizado em C:\CategoriasDados.xml (o caminho pode ser alterado se necessário).

<?xml version="1.0" encoding="ISO-8859-1"?>
<Categorias>
  <Categoria Nome="Informática e Acessórios">
    <Produto Nome="MP5 Player" Marca="Image" Valor="369.90"/>
    <Produto Nome="Notebook NR350 Core 2 Duo" Marca="Sony" Valor="2999.99"/>
    <Produto Nome="Macbook Core 2 Duo" Marca="Apple Brasil" Valor="3999.99"/>
    <Produto Nome="Roteador Wireless WRT54G-LA" Marca="Linksys" Valor="199.00"/>
  </Categoria>
  <Categoria Nome="CDs">
    <Produto Nome="Seal – Soul" Marca="Warner" Valor="38.90"/>
    <Produto Nome="Sem Limite: Roupa Nova- Duplo" Marca="Universal" Valor="16.90"/>
    <Produto Nome="Originals: Bob Marley" Marca="Neo" Valor="12.90"/>
  </Categoria>
  <Categoria Nome="Vinhos, Bebidas e Comidas">
    <Produto Nome="Chocolate Recheado c/ Capuccino 120g" Marca="Kron" Valor="9.90"/>
    <Produto Nome="Whisky 18 Anos" Marca="Buchanans" Valor="449.00"/>
    <Produto Nome="Vinho Tinto Afincado" Marca="Moet Hennessy" Valor="269.00"/>
    <Produto Nome="Salada de Alho Espanhola" Marca="Falani" Valor="3.90"/>
  </Categoria>
</Categorias>

O XSD para validação a ser utilizado é descrito abaixo e está localizado em C:\CategoriasValidacao.xsd (o caminho pode ser alterado se necessário).:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsd:schema xmlns:xsd="
http://www.w3.org/2001/XMLSchema">
  <!– Criação do tipo produto –>
  <xsd:complexType name="tProduto">
    <xsd:attribute name="Nome" type="xsd:string" use="required"/>
    <xsd:attribute name="Marca" type="xsd:string" use="required"/>
    <xsd:attribute name="Valor" type="xsd:decimal" use="required"/>
  </xsd:complexType>
  <!– Criação do tipo Categoria –>
  <xsd:complexType name="tCategoria">
    <xsd:sequence>
      <xsd:element name="Produto" type="tProduto" minOccurs="1" maxOccurs="4"/>
    </xsd:sequence>
    <xsd:attribute name="Nome" type="xsd:string" use="required"/>
  </xsd:complexType>
  <!– Criação do tipo Categorias –>
  <xsd:complexType name="tCategorias">
    <xsd:sequence>
      <xsd:element name="Categoria" type="tCategoria" minOccurs="1" maxOccurs="3"/>
    </xsd:sequence>
  </xsd:complexType>
  <!– Declaraçao de um elemento de Categorias –>
  <xsd:element name="Categorias" type="tCategorias"/>
</xsd:schema>

O suporte a carga de BLOBs (incluindo o XML) foi bastante melhorado nas versões 2005 e 2008. Anteriormente, para carregar um blob diretamente via Query era praticamente impossível. A única alternativa era utilizar o utilitário TextCopy.exe ou confiar nas APIs da aplicação para fazer isso. Já no SQL Server 2005 e 2008 contamos com a instrução OPENROWSET com suporte a importação de BLOBs. Embora o comando OPENROWSET seja bloqueado por padrão não é necessário habilitá-lo para que a importação de BLOBs possa funcionar. O comando abaixo faz a leitura do arquivo C:\CategoriasDados.xml

SELECT BulkColumn FROM
OPENROWSET
(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
AS Arquivo

O código exibido pela instrução é binário e embora a extensão seja XML, o SQL Server não está a par disso. É necessário converter o valor para um tipo válido. A conversão pode ser em XML ou VARCHAR(MAX). Como estamos falando de XML, a conversão para XML é mais indicada. O comando abaixo faz a leitura corretamente.

SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
AS Arquivo

Antes de pensarmos em carregar alguma tabela com o conteúdo do XML, devemos verificar se o mesmo é válido. Para isso, é necessário importar o conteúdo do XSD e criar um esquema de validação através de objetos XML Schema Collection. O script abaixo, importa o conteúdo do XSD (que também é um XML) e cria um esquema de validação.

— Importa o XSD para uma variável
DECLARE @XSD XML
SET @XSD = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasValidacao.xsd’, SINGLE_BLOB)
    AS Arquivo)

— Cria um novo schema de validação
CREATE XML SCHEMA COLLECTION xsdProdutos AS @XSD

Agora que há um esquema de validação de XML, poderemos visualizar se o arquivo XML é ou não válido através do objeto XML Schema Collection. Para tornar o exemplo um pouco mais interessante, vamos editar o XML para provocar propositalmente um erro de validação. A primeira tag <Categoria> deve ser substituídas por <Cat> deixando o XML da seguinte forma:

<?xml version="1.0" encoding="ISO-8859-1"?>
<Categorias>
  <Cat Nome="Informática e Acessórios">
    <Produto Nome="MP5 Player" Marca="Image" Valor="369.90"/>
    <Produto Nome="Notebook NR350 Core 2 Duo" Marca="Sony" Valor="2999.99"/>
    <Produto Nome="Macbook Core 2 Duo" Marca="Apple Brasil" Valor="3999.99"/>
    <Produto Nome="Roteador Wireless WRT54G-LA" Marca="Linksys" Valor="199.00"/>
  </Cat>
  <Categoria Nome="CDs">
    <Produto Nome="Seal – Soul" Marca="Warner" Valor="38.90"/>
    <Produto Nome="Sem Limite: Roupa Nova- Duplo" Marca="Universal" Valor="16.90"/>
    <Produto Nome="Originals: Bob Marley" Marca="Neo" Valor="12.90"/>
  </Categoria>
  <Categoria Nome="Vinhos, Bebidas e Comidas">
    <Produto Nome="Chocolate Recheado c/ Capuccino 120g" Marca="Kron" Valor="9.90"/>
    <Produto Nome="Whisky 18 Anos" Marca="Buchanans" Valor="449.00"/>
    <Produto Nome="Vinho Tinto Afincado" Marca="Moet Hennessy" Valor="269.00"/>
    <Produto Nome="Salada de Alho Espanhola" Marca="Falani" Valor="3.90"/>
  </Categoria>
</Categorias>

O script abaixo tenta importar os dados e verificar se o  XML é válido de acordo com a XML Schema Colletion xsdProdutos definida com base no arquivo XSD.

— Importa o XML para uma variável fazendo validação
DECLARE @XML XML (xsdProdutos)
SET @XML = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
    AS Arquivo)

Após rodar essa instrução, um erro de validação é gerado:

Msg 6965, Level 16, State 1, Line 3
XML Validation: Invalid content. Expected element(s):Categoria where element ‘Cat’ was specified. Location: /*:Categorias[1]/*:Cat[1]

Esse erro é apresentado porque no esquema de validação, informou-se que o elemento "Categorias" deve possuir elemento "Categoria" e o elemento Cat não foi especificado fazendo com que o conteúdo do documento XML fosse considerado inválido. Se o XML for alterado para sua forma original, o script anterior será executado sem problemas.

Agora que a validação do XML foi realizada o passo final é desserializar o XML e convertê-lo para um formato relacional (Shredding). No SQL Server 2005 e 2008 poderíamos fazer esse trabalho através do OPENXML já que no 2005 e 2008 esse método também suporta o tipo XML. Entretanto, acho que o método Nodes é mais eficiente por dispensar o uso de ponteiros que o OPENXML utiliza, além de ser mais fácil. O script abaixo faz a recuperação das categorias e de seus produtos.

— Importa o XSD para uma variável fazendo validação
DECLARE @XML XML (xsdProdutos)
SET @XML = (
    SELECT CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK N‘C:\CategoriasDados.xml’, SINGLE_BLOB)
    AS Arquivo)

— Mostrar as categorias
SELECT Cats.Cat.value(‘@Nome’,‘nvarchar(80)’) AS Categorias
FROM @XML.nodes(‘/Categorias/Categoria’) AS Cats(Cat)

— Mostrar os produtos e as categorias
SELECT
    Cats.Cat.value(‘string(../@Nome)’,‘nvarchar(80)’) AS Categoria,
    Cats.Cat.value(‘@Nome’,‘nvarchar(80)’) AS Nome,
    Cats.Cat.value(‘@Marca’,‘nvarchar(30)’) AS Marca,
    Cats.Cat.value(‘@Valor’,‘smallmoney’) AS Valor
FROM @XML.nodes(‘/Categorias/Categoria/Produto’) AS Cats(Cat)

Uma vez que seja possível extrair os dados a partir do XML, bastaria uma simples instrução INSERT para copiar esses dados para suas tabelas de destino (possivelmente uma tabela de categorias e uma tabela de produtos). No caso da tabela de produtos, um JOIN talvez seja necessário para retornar o código da categoria e não o seu nome. Alguns mais familiarizados com a XQuery podem perguntar qual a finalidade da função string definida na recuperação do nome da categoria. Ela é necessário porque o XML foi tipado com base no esquema xsdProdutos e caso a mesma seja omitida a instrução não funciona. A função string poderia ser omitida somente se o XML não fosse tipado.

As instruções TSQL utilizadas também podem utilizar diretórios de rede desde que a conta de serviço do SQL Server tenha permissões de leitura no diretório desejado. A utilização de diretórios na rede e compartilhamentos evita que os arquivos tenham de ser descarregados diretamente no servidor do SQL Server. Para usuários que não sejam sysadmin pode ser necessário criar uma credencial, pois, arquivos XML são recursos externos ao SQL Server.

Essa solução baseou-se no tipo de dados XML e nos comandos TSQL. As limitações desse método estarão diretamente relacionadas à limitação desse tipo de dados. A principal refere-se ao fato de que o tipo de dados XML não suporta um tamanho maior do que 2GB. Isso significa que se o arquivo XML em questão tiver mais de 2GB, essa alternativa não irá funcionar. Felizmente, é muito difícil que um arquivo XML a ser carregado tenha tamanho superior a 2GB e caso isso ocorra, deverá ser solicitado que esse arquivo seja repartido em arquivos menores.

Essa alternativa baseada em TSQL deve ser utilizada quando houver a intenção de persistir os dados do arquivo XML no banco de dados. Ela não deve ser utilizada para validar arquivos XML que não serão persistidos ou que não serão úteis para o banco de dados. Se o objetivo for apenas validar arquivos XML e não persistir, a validação deve ser codificada em uma aplicação a parte e o SQL Server não deve ser utilizado para isso.

Bem, acho que com isso finalizo a primeira alternativa para importar e validar dados XML. Não irei expor todas as alternativas possíveis, mas em breve apresentarei mais algumas.

[ ]s,

Gustavo

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

  1. PERFEITO

  2. Gustavo, muito grato, era justamente isso que eu precisava!

    Eu usei o OpenXML para fazer o shredding, por achar mais fácil adaptar para o meu caso, mas estava procurando como usar o openrowset para ler o xml e só achei o seu exemplo, valeu mesmo.

    • Olá Gabriel,

      A importação com o OPENROWSET é fácil, mas lembre-se que irá consumir memória do SQL Server para importar o arquivo. É uma solução rápida, mas se você for fazer muitas cargas simultâneas, dê preferência a outra técnicas como o uso do SSIS. É mais trabalhoso, mas por não usar a mesma memória, normalmente é mais escalável.

      O uso do OpenXML para fazer o Shredding é eficaz, mas aconselho a usar o método Nodes em vez do OpenXML. O Nodes evita a abertura de ponteiros, é mais performático e deixa o código mais enxuto.

      [ ]s,

      Gustavo

  3. Olá Gustavo,

    Estou trabalhando no XML da NFe e o XSD que consegui tem o elemento INCLUDE e com isso não consigo carregar ele no SQL Server, pois ele não suporta.
    Saberia me dizer qual a melhor solução para o caso?

    • Oi Alfredo,

      Se for fazer no T-SQL, eu sugiro que importe para VARCHAR(MAX), dê um REPLACE no INCLUDE e tente converter para XML.
      O INCLUDE é XML Compliance (W3C), mas não é suportado no XML do SQL Server.

      [ ]s,

      Gustavo

  4. Pingback: Extraindo informações de arquivo XML para o SQL Server « Alex Souza

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