Matrizes no SQL Server 2005 – Parte II

Na dica anterior, demonstrei como utilizar matrizes no SQL Server 2005 através da delimitação de uma string com um separador retornando uma tabela com os elementos da matriz. É uma técnica bem simples e que pode ajudar em diversas situações, mas é também uma técnica bem limitada e serve para trabalhar com matrizes cujo o elemento tenha apenas uma única característica (possivelmente o nome).

Reafirmo que o SQL Server não possui tipos Array nativos, mas sim formas de contornar as necessidades deles e de simulá-los. Hoje demonstrarei como fazer isso através da utilização do XML no SQL Server 2005.

Um pouco sobre XML

O XML é um formato semi-estruturado. Diz-se semi-estruturado porque ele obedece a algumas regras de formação no que diz respeito a declaração de tags, presença de um elemento root, etc, mas ao mesmo tempo é um formato bastante flexível uma vez que existe total liberdade na criação de elementos, atributos, etc. Por padrão não há regras para a quantidade de elementos, atributos, aninhamentos, etc. Essa natureza semi-estruturada permite a livre confecção do XML da forma que melhor convier a quem o projeta. Como o XML não é tão rígido quanto estruturas relacionais é possível declarar uma estrutura de documento XML de forma muito flexível além de superar as limitações de uma representação tabular constituída de linhas e colunas.

Convém lembrar que toda essa liberdade possui um preço. A ausência de limites claramente definidos na montagem de um documento XML pode tornar difícil a tarefa de administrá-lo. Se forem criadas elementos e atributos de forma anárquica, ou seja sem regras preestabelecidas, pode ficar difícil de gerenciar um documento XML. Sem uma estrutura padronizada de um documento XML, haverá dificuldades em consultá-lo e atualizá-lo. Para tornar a tarefa de padronização de um documento XML possível, temos à disposição o padrão XML Schema cuja implementação no SQL Server 2005 se dá através de uma XML Schema Collection. Os interessados em consultar mais sobre esse assunto podem consultar um artigo de minha autoria disponível no site do Plugmasters.

O XML realmente permite uma ampla flexibilidade que deve ser trabalhada para torná-lo gerenciável. Impor regras de validade a um documento XML seria de certa forma semelhante às regras impostas nas tabelas e colunas. Em todo caso, em documentos XML podemos praticamente escolher até onde limitá-lo enquanto que as restrições tabulares são mais rígidas e difíceis de transpor. É esse aspecto que o torna tão poderoso já que permite representações tabulares, hierárquicas, complexas, etc. Utilizando os padrões XPath e XQuery pode-se inclusive realizar consultas contra documentos XML o que amplia ainda mais as possibilidades. Utilizaremos o poder do XML e suas implementações no SQL Server 2005 para trabalhar com Arrays.

XML e Arrays

Para iniciarmos o uso do XML, utilizarei-me dos scripts presentes na dica anterior. Criaremos as mesmas tabelas e registros utilizados.

— Cria uma tabela de Clientes
CREATE TABLE tblClientes (Nome VARCHAR(80), UF CHAR(2))

— Popula a tabela de Clientes
INSERT INTO tblClientes VALUES (‘Aline’,‘SP’)
INSERT INTO tblClientes VALUES (‘Lívia’,‘DF’)
INSERT INTO tblClientes VALUES (‘Jaqueline’,‘DF’)
INSERT INTO tblClientes VALUES (‘Cecília’,‘MG’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)
INSERT INTO tblClientes VALUES (‘Ronaldo’,‘DF’)
INSERT INTO tblClientes VALUES (‘Gilvan’,‘PR’)
INSERT INTO tblClientes VALUES (‘Dayane’,‘DF’)
INSERT INTO tblClientes VALUES (‘Victor’,‘RJ’)
INSERT INTO tblClientes VALUES (‘Vinicius’,‘PR’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)

O desafio anterior era procurar repassar um Array para realizar a pesquisa dos clientes nos estados São Paulo, Rio de Janeiro e Rio Grande do Sul. Possivelmente esses valores serão repassados através de um string. Utilizarei os mesmos estados, mas antes é importante um conversão da string para um formato XML conforme o script abaixo:

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra o conteúdo em XML bem formado
SELECT @EstadosXML

O que o script fez foi transformar a string que separava os estados em um formato XML bem formado. Aparentemente esse mudança é mínima, mas para trabalhar com XML é esperado que utilizemos um formato XML e não uma string pura. A mínima mudança entretanto é bem mais significativa do que parece. O resultado em XML não é uma mera representação. Uma vez que os dados estejam em XML é possível utilizar alguns métodos para trabalhá-lo. O script abaixo demonstra como converter o XML em um formato relacional.

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra os elementos do XML em formato tabular
SELECT es.e.value(‘.’,‘char(2)’) As Estado
FROM @EstadosXML.nodes(‘/es/e’) es (e)

Uma vez que os dados estejam em formato tabular fica bem mais fácil aplicar utilizar esse formato para realizar pesquisas.

DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

SELECT Nome, UF FROM tblClientes WHERE UF IN (
SELECT es.e.value(‘.’,‘char(2)’) As Estado
FROM @EstadosXML.nodes(‘/es/e’) es (e))

Esse foi apenas um exemplo do que o XML pode fazer para simular aplicações com Array. É importante perceber que os métodos de pesquisa baseados em XPath e XQuery também permitem obter outras propriedades de um Array. O script abaixo conta quantos elementos existem em um Array baseado em XML e também mostra o valor do penúltimo elemento.

— Declara variáveis
DECLARE @Estados VARCHAR(20), @EstadosXML XML
SET @Estados = ‘SP,RJ,RS’
SET @EstadosXML = ‘<es><e>’ + REPLACE(@Estados,‘,’,‘</e><e>’) + ‘</e></es>’

— Mostra quantos elementos existem no Array
SELECT @EstadosXML.value(‘count(/es/e)’,‘int’) As TotalEstados

— Mostra o penúltimo elemento
SELECT @EstadosXML.value(‘(/es/e[last()-1])[1]’,‘CHAR(2)’) As TotalEstados

Um exemplo mais elaborado

Os exemplos acima são interessantes, mas ainda estão incorrendo na natureza unidimensional da solução apontada na dica anterior. O XML apenas tornou as coisas mais fáceis e menos procedurais, mas ainda não superou as limitações da delimitação da string. O próximo exemplo supera um pouco esses limites. Ele consiste no cadastro de um cliente e seus telefones através de uma stored procedure. Embora os dados de clientes e telefones estejam normalmente em tabelas separadas, todos os dados são repassados para uma única stored procedure capaz de cadastrar tantos os clientes quanto os telefones. Para esse exemplo não me preocupei com as constraints, pois, a idéia básica é demonstrar como o XML pode ajudar a montar um Array de telefones.

— Cria as tabelas
CREATE TABLE tblClientes (IDCliente INT Identity(1,1), Nome VARCHAR(80), RG VARCHAR(20))
CREATE TABLE tblTelefones (IDCliente INT, Tipo VARCHAR(20), Numero CHAR(8))
GO

— Cria Stored Procedure
CREATE PROCEDURE uspInsereClientes
    @DadosCliente XML
AS

— Captura os dados do cliente
DECLARE @Nome VARCHAR(80), @RG VARCHAR(20)
SET @Nome = @DadosCliente.value(‘(/Cliente/@Nome)[1]’,‘VARCHAR(80)’)
SET @RG = @DadosCliente.value(‘(/Cliente/@RG)[1]’,‘VARCHAR(20)’)

— Insere um novo cliente
INSERT INTO tblClientes (Nome, RG) VALUES (@Nome,@RG)

— Captura o ID do novo cliente
DECLARE @IDCliente INT
SET @IDCliente = SCOPE_IDENTITY()

— Insere os telefones do cliente
INSERT INTO tblTelefones (IDCliente, Numero, Tipo)
SELECT @IDCliente,
    tels.tel.value(‘./@Numero’,‘CHAR(8)’),
    tels.tel.value(‘./@Tipo’,‘VARCHAR(20)’)
FROM
    @DadosCliente.nodes(‘/Cliente/Telefones/Telefone’) tels(tel)
GO

A stored procedure espera receber um documento XML que contenha os dados de nome e RG do cliente. Como apenas um cliente por vez é informado, é seguro armazenar os dados de nome e RG desse cliente em variáveis cujo valores são extraídos do documento XML via XPath. Ainda no documento XML podem aparecer um ou mais telefones que devem ser cadastrados na tabela de telefones. O número de telefones é variável e é representado por um Array de telefones cujo o tamanho é desconhecido. Uma vez que a estrutura esteja montada, basta executar a chamada a stored procedure com dados de exemplo:

— Declara uma variável XML
DECLARE @DadosClientePar XML
SET @DadosClientePar = ‘<Cliente Nome="Mário Sérgio" RG="1536799 – SSP/DF">
<Telefones>
<Telefone Tipo="Celular" Numero="92314520"/>
<Telefone Tipo="Residencial" Numero="32262378"/>
<Telefone Tipo="Trabalho" Numero="34145677"/>
</Telefones></Cliente>’

— Executa a procedure passando como parâmetro o XML
EXEC uspInsereClientes @DadosCliente = @DadosClientePar

— Consulta a tabela de clientes para conferência
SELECT IDCliente, Nome, RG FROM tblClientes

— Consulta a tabela de telefones para conferência
SELECT IDCliente, Tipo, Numero FROM tblTelefones

Como pode ser observado, o cliente foi cadastrado corretamente e os seus telefones também aproveitando o ID recém inserido. Dessa vez o Array de telefones não incluiu apenas uma característica de telefone. Foram trabalhadas duas características (o número e o tipo). A stored procedure foi chamada uma única vez passando todos os dados necessários. Não foi necessário sequer um único loop para percorrer os registro do Array (seja via SQL ou aplicação). Em todo caso, o tamanho do documento XML é maior do que os dados em si já que tags e quebras de linhas foram adicionadas.

Os exemplos postados mostram o poder do XML para simular um tipo Array. É necessário apenas utilizar as declarações de elementos do XML para adicionar membros ao Array. A utilização de consultas XPath / XQuery também permitem obter outras informações úteis a cerca do Array como a quantidade de elementos, elementos com base em posição, etc. Como é possível aninhar elementos dentro de elementos, pode-se inclusive criar matrizes dentro de matrizes. Com a utilização da XQuery fica fácil transpor matrizes bidimensionais, tridimensionais, etc para uma estrutura relacional compatível com a SQL.

Nessa dica acabei utilizando diversos recursos e funções do SQL Server 2005. Caso os termos XPath, XQuery, Scope_Identity, etc não lhe sejam familiares eu recomendo a leitura de outros artigos que publiquei no site do Plugmasters. A leitura prévia dos mesmos pode tornar o uso de Arrays com XML ainda mais fácil.

Para os que gostariam de simular algo do tipo no SQL Server 2000, não deixei de pesquisar o Kb da Microsoft no link abaixo:

How to pass array of values into SQL Server stored procedure using XML and Visual Basic .NET
http://support.microsoft.com/kb/555266/en-us

[ ]s,

Gustavo

2 Respostas para “Matrizes no SQL Server 2005 – Parte II

  1. Pingback: Alex Souza

  2. Pingback: “Array” no 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