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:

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.

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.:

INSERT INTO TesteXML
(codigo,dados)
VALUES
(100,
'<?xml version="1.0"?>
<aluno cod="100">
<nome>Jose Carlos Macoratti</nome>              
<historico>
<materia nome="Portugues" nota="10"/>
<materia nome="Matemtaica" nota="8"/>
<materia nome="Fisica" nota="6"/>
<materia nome="Ingles" nota="9"/>
</historico>
</aluno>
'
)

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:

UPDATE TesteXML
SET dados.modify
('insert <materia nome="Quimica" nota="7" />
into (/aluno/historico)[1]
')
WHERE codigo = 100

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:

UPDATE TesteXML
SET dados.modify
('insert <materia nome="Biologia" nota="8" />
before (/aluno/historico/materia)[2]
')
WHERE codigo = 100

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:

UPDATE TesteXML
SET dados.modify('
replace value of (/aluno/historico/materia/@nota)[last()]
with "5"'
)
WHERE codigo = 100

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:

UPDATE TesteXML
SET dados.modify('
delete /aluno/historico/materia/@nome[contains(.,"ica")]')

 

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:

UPDATE TesteXML
SET dadaos.modify('
delete /aluno')
WHERE codigo = 100

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...)

Até o próximo artigo .NET 


José Carlos Macoratti