Desenvolvimento, design e outras coisas que enfrento no dia a dia…

Arrays, Coleções e Stored Procedures no SQL Server

Utilizados para solucionar problemas como número indeterminado de variáveis, indexar variáveis relacionadas e assim facilitar seu processamento, ordenação dinâmica, entre outros, os arrays e as demais coleções apresentam papel fundamental nas linguagens de programação.

Entretanto uma questão surge quando seus valores devem ser repassados/serializados isoladamente para um banco de dados através de stored procedures uma vez que o SQL Server não possui um tipo nativo que representa esses tipos de objetos e possa ser utilizado por um parâmetro.

Algumas das alternativas são: executar uma chamada para cada item, criar um novo tipo, utilizar SQL dinâmico e utilizar XML.

Eu fico com a última opção e mostrarei como utilizá-la.

O segredo está em duas stored procedures nativas (sp_xml_preparedocument e sp_xml_removedocument) e uma outra função (OPENXML) para transformar os dados do XML em, praticamente, uma tabela.

Supondo o seguinte XML:

   1: <?xml version="1.0" encoding="UTF-8"?>

   2: <pessoas>

   3:     <pessoa nome="Luiz" idade="60" />

   4:     <pessoa nome="Jo&#227;o" idade="40" />

   5:     <pessoa nome="Eduardo" idade="50" />

   6: </pessoas>

E uma tabela com apena duas colunas: Nome e Idade nossa stored procedure ficaria da seguinte maneira:

   1: CREATE PROCEDURE sp_SalvarPessoas

   2: ( @pessoasXML text )

   3: as

   4: BEGIN

   5:     DECLARE @docXML int

   6:

   7:     EXEC sp_xml_preparedocument @docXML output, @pessoasXML

   8:

   9:     insert into pessoas

  10:         select

  11:             *

  12:         from

  13:             OPENXML(@docXML, 'pessoas/pessoa', 0)

  14:                 with (nome varchar(50), idade int)

  15:

  16:     EXEC sp_xml_removedocument @docXML

  17: END

A procedure sp_xml_preparedocument analisa o XML, carrega-o em memória e pode receber até três parâmetros:

1. hdoc, onde é retornado o identificador do arquivo
2. xmltext, opcional, pode ser qualquer tipo texto (de char à ntext) e contém o arquivo XML que deve ser carregado.
3. xpath_namespaces, opcional, descreve os namespaces  utilizados pelo arquivo.

Como retorno o valor 0 indica sucesso já qualquer valor maior que isso indica a ocorrência de um erro na leitura do arquivo.

Já a função OPENXML monta a tabela contendo os dados do arquivo XML e funciona em duas etapas. Antes do “with” há três parâmetros:

1. idoc, identificador do arquivo que será utilizado;
2. rowpattern, XPATH que indica quais nós devem ser utilizados para formar as linhas
3. flags, indica como serão formadas as colunas, é possível obtê-las apenas dos atributos – como mostrado no exemplo –, utilizar outros nós ou ainda combinar ambos.

Após o “with” segue a descricão de como é a tabela que deve ser criada e seus campos. No exemplo, para a flag utilizada no método OPENXML os nomes das colunas devem casar com o nome dos atributos dos nós descritos no rowpattern, mas para outros flags é necessário adicionar um terceiro parâmetros para cada campo que descreve a origem do valor.

No final é necessário liberar a memória utilizada pelo arquivo XML, papel executado pela sp_xml_removedocument que recebe o identificador do arquivo e faz todo o trabalho sujo.

Lembretes:

• Atentar para o consumo de memória para o manuseio de grandes arquivos XML;
• Cuidado para não permitir “XML Injection” ao serializar seus dados e substituir caracteres especiais por suas respectivas entidades, notem como está o nome “João” no exemplo;
• Não esquecer de sempre liberar a memória utilizada pelo arquivo XML;

É isso aí.