SQL Server 2005 - Trabalhando com dados XML

Se você ainda não sabia , saiba que o SQL Server 2005 agora dá suporte nativo a dados do tipo XML. Boa notícia !!

Dentre os recursos suportados podemos citar:

• Armazenar XML na forma tipada onde o esquema é registrado no SQL Server e o XML precisa estar em conformidade com este esquema;
• Armazenar XML no formato não tipado onde nenhum esquema é requerido e o XML apenas precisa ser bem formado;
• Selecionar valores a partir de uma coluna XML em instruções SQL e stored procedures como qualquer outro tipo de coluna SQL;
• Efetuar uma consulta diretamente na columa XML usando XPath e linguagens XQuery para encontrar e retornar valores específicos;
• Modificar o XML na coluna através da inclusão ou remoçlão de nós ou pela alteração de valores nos nós existentes;
• Vincular XML a outro tipo de dados SQL , permitindo consultas para acessar valores em variáveis de stored procedures e outras colunas não XML;
• Acessar e retornar conjuntos de valores em nós XML;
• Criar índices na coluna XML para melhorar o desempenho durante a consulta ou atualização de dados

Nota: A XQuery é uma linguagem que usa a estrutura XML de forma a expressar consultas sobre dados no formato XML. Os dados não precisam estar armazenados em arquivos XML, ou simplesmente no formato XML. Você pode pensar na XQuery como sendo uma linguagem equivalente a SQL que pode ser usada para tratar informações XML.

Com isso podemos concluir que existem dois tipos de colunas XML : tipada e não-tipada.

• As colunas XML não-tipadas não possuem esquema e os dados são armazenados no formato caractere com elementos e atributos.
• As colunas XML tipadas possuem esquema e validação quando os dados são incluídos ou modificados;

Para armazenar documentos XML nas versões anteriores do SQL Server você tinha que usar os tipos de dados textos como: VARCHAR, NVARCHAR, TEXT e NTEXT. As alterações nos dados implicavam praticamente em excluir a versão antiga do documento e gravar uma nova versão com grande custo de memória e de CPU.

Agora é possível efetuar alterações no conteúdo das informações XML apenas alterando ou excluindo o trecho de informação de interesse.

Nota: Antes de continuar a leitura do arquivo recomendo que você leia o meu artigo sobre o XPath: Tutorial XPath

Criando colunas do tipo XML

Desde de que o tipo XML é um padrão no SQL Server 2005 para criar colunas XML basta declarar o XML como um tipo de dados. Vejamos a seguir um exemplo de como criar uma coluna padrão XML. A sintaxe para criar a tabela TesteXML com duas colunas é a seguinte:

Create Table TesteXML (CodigoID int, dados xml)

Abaixo temos o resultado da execução dessa instrução no SQL Management Studio 2005.

Podemos usar o Management Studio para também criar a coluna do tipo XML digitando diretamente a definição no editor conforme abaixo:

Tudo bem ! já críamos a tabela com duas colunas , uma delas do tipo xml. Como inserir dados nesta tabela ???

Lembra da instrução SQL INSERT INTO ????

Pois é , iremos usar a mesma instrução e a mesma sintaxe.:

A única diferença é que incluímos o arquivo XML contendo os dados do aluno na instrução. Veja abaixo como ficou a instrução criada e executada no Management Studio:

Tá curioso para ver o conteúdo da tabela ??? 

Pois clique com o botão direito sobre a tabela e selecione a opção Open Table para ver os dados na coluna XML.

E se você desejar incluir uma nova materia no histórico do aluno , ou seja, incluir um novo elemento ou atributo no documento XML armazenado. Neste caso vamos usar o mesmo comando usado para atualizar dados na linguagem SQL, o comando UPDATE, mas antes é preciso chamar o método modify.

O método modify permite a realização de alterações (inclusões e atualizações) e exclusões em documentos ou trechos do documento XML armazenado no SQL Server 2005. As instruções usadas são baseadas na XPath (por isso recomendei a leitura do assunto).

Voltando ao nosso exemplo vamos incluir um novo elemento referente a uma nova materia e nota na informação XML armazenada na tabela TesteXML criada:

Vamos inserir o elemento <materia nome="Quimica" nota="7" /> na coluna dados. A instrução usada é a seguinte:

Note o uso do método modify.

Abaixo temos a execução da instrução no Management Studio:

Para verificar se o comando foi executado corretamente vamos usar a instrução SELECT para exibir os dados da tabela :

SELECT [codigo] ,[dados] FROM [Livraria].[dbo].[TesteXML]

usando o Management Studio para executar a instrução teremos:

Clicando no link exibido na coluna dados iremos obter o arquivo XML abaixo:

E se você quiser incluir o elemento <materia nome="Biologia" nota="8" /> antes do elemento <materia nome="Matematica" nota="8" /> ?

Aqui novamente recorremos ao SQL e as instruções XPath; veja como fica a instrução SQL:

Perceba a utilização da cláusula before e que especificamos a materia (/aluno/historico/materia)[2]) através do índice 2 para indicar a posição depois da qual vamos incluir o elemento. Executando a instrução no Management Studio teremos:

Nota: Da mesma forma poderíamos ter usado a cláusula  after para incluir depois da posição indicada na instrução SQL.

E como faremos para efetuar uma alteração em dados XML já cadastrados ?

Neste caso podemos usar a instrução replace que é idêntica a a instrução Update e permite alterar valores ja cadastrados. A utilização da instrução replace não pode ser usada para alterar nós, mas somente valores.

No nosso exemplo vamos alterar o nome da materia Ingles para Espanhol usando a instrução replace. Veja como fica a instrução SQL no Management Studio;

A instrução XPath - /aluno/historico/materia(@nome="Ingles")[1] - vai procurar a primeira materia que tenha o nome 'Ingles' e vai substituí-lo pelo texto - 'Espanhol'. Para especificar que desejamos substituir o primeiro usamos o índice [1].

Após a execução da instrução vamos novamente usar uma instrução SQL Select para verificar se o valor foi realmente alterado.

SELECT [codigo] ,[dados] FROM [Livraria].[dbo].[TesteXML]

usando o Management Studio para executar a instrução teremos:

Clicando no link exibido na coluna dados iremos obter o arquivo XML abaixo:

Nota: Podemos usar o operador last() para direcionar para o último elemento independente da sua posição. Assim, para atualizar o valor da nota para a última materia para 5 usamos a seguinte instrução:

E a exclusão de registros , como ficaria ?

Para excluir usamos a cláusula DELETE já conhecida da SQL. Ela é usada para excluir atributos e elementos em um documento XML persistido no SQL Server 2005.

Tenha cuidado ao usar a cláusula DELETE pois ela pode excluir valores, atributos, elementos e até mesmo todo o documento XML persistido na base de dados.

Vejamos alguns exemplos usando DELETE:

Esta instrução exclui todos os elementos materia  cujo atributo nome contenha a string "ica"

Ao ser executada ela irá excluir as matérias de nome : Quimica, Fisica e Matematica pois todas contém a string "ica"

Por isso você sempre deve usar uma cláusula WHERE em uma instrução DELETE, como no exemplo abaixo:

A instrução acima exclui todo o conteúdo XML para o aluno de código igual a 100. Como aluno é a raiz do documento XML basta definir a raiz do documento que todos os elementos serão excluídos ao final o valor da coluna passa a conter uma string vazia ("").

Eu vou ficando por aqui mas quero lembrar que o assunto é extenso e que em artigos futuros pretendo continuar falando sobre os métodos para tipos XML, onde tratarei dos métodos de consulta em estruturas XML. Abaixo temos os métodos mais importantes a serem estudados: (aguarde...)

• query() - Efetua consultas em estruturas XML que retornam XML;
• value() - Efetua consultas em estruturas XML que retornam tipos SQL ;
• exist() - Efetua consultas para determinar a existência de um valor dentro de uma estrutura XML;
• nodes() - Utiliza para segmentar uma estrutura XML em vários registros;

Até o próximo artigo .NET 

José Carlos Macoratti