Buscando Valores de Tags em Campos XML no Microsoft SQL Server

Introdução

Nos bancos de dados temos a possibilidade de utilizar um campo que contenha um XML, e em determinadas situações necessitamos resgatar valores específicos que estão dentro de tags contidas nestes campos. Como primeira opção, sempre pensamos em abrir cada XML e coletar os dados de forma manual, porém, existem maneiras que podem salvar bons minutos ou horas a depender do volume de dados.

Cenário

Vamos supor que tenhamos no banco de dados, uma tabela que contenha algumas informações de configuração para um programa que será executado em N computadores, entre estes campos temos um XML com configurações mais específicas.

Problema

Você precisa fazer um relatório, levantando os seguintes dados:

Id (Essa informação se encontra em um campo da tabela)
ComputerIp (Essa informação se encontra em um campo da tabela)
Device (Essa informação se encontra dentro do campo XML)

Como podemos encontrar o valor Device sem precisar abrir cada XML e pegar o valor manualmente?

Solução

Para contornar o trabalho manual e possíveis erros no processo repetitivo de abrir cada XML para coletar os dados, podemos utilizar a seguinte solução:

SELECT
  Id,
  ComputerIp,
  Configuration.value('(/Root/Device)[1]','int') AS Device
FROM 
  ProgramConfigurations WITH(NOLOCK)
ORDER BY 
  Id

Onde o seguinte trecho é responsável por coletar o valor Device dentro do nosso XML:

Configuration.value('(/Root/Device)[1]','int') AS Device

Vamos dividir este comando da esquerda para a direita, para entendermos melhor o que ele está fazendo:

Configuration -> é o campo XML da tabela ProgramConfigurations onde desejamos buscar o valor de Device.
.value() -> é um método que tem a função de extrair um valor especifico dentro de um campo XML
'(/Root/Device)' -> Localização da tag dentro da raiz do arquivo XML, abaixo um exemplo de como seria este arquivo:

<?xml version="1.0" encoding="utf-8"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <AllowEdition>false</AllowEdition>
  <MaxAttemptsNumber>3</MaxAttemptsNumber>
  <Device>12</Device>
</Root>

O caminho para chegarmos a tag Device é Root > Device, logo, no nosso comando ficaria /Root/Device, mas isto vai de acordo com a estrutura do XML em questão, estes argumentos podem se alterar, mas a lógica aplicada será a mesma.

[1] -> Indica a ocorrência da tag que será levada em consideração, como só temos uma ocorrência no nosso arquivo, o correto seria 1, caso tivéssemos 3 tags Device, e quiséssemos coletar a 3 ocorrência, ficaria (/Root/Device)[3].
,'Int' -> é o tipo no qual o valor de Device será convertido, neste caso, um inteiro (int).
As Device -> Será o nome dado a coluna retornada por esta query.

Assim, ao executarmos esta query, teremos um retorno parecido com isto:

E assim, resolvemos nosso problema e podemos seguir com nosso relatório, sem ter aberto nenhum XML para coletar as informações.

Extra

Deixo neste artigo também, uma query para auxiliar na troca rápida de valores em XML armazenados no banco de dados, isto pode ser útil quando temos uma configuração que pode ser alterada dado um determinado contexto, por exemplo o endereço de uma API.

Neste caso, podemos utilizar a seguinte query:

UPDATE
  ProgramConfigurations
SET
  Configuration =
REPLACE
(
  CAST(CONFIG as varchar(MAX))
  ,'<UrlApi type="string">http://localhost:5001/api/</UrlApi>'
  ,'<UrlApi type="string">http://localhost:5107/api/</UrlApi >'
)

O que esta query faz?

Ela transforma o campo XLM em varchar, realizar a troca de valores, neste caso o valor:

<UrlApi type="string">http://localhost:5001/api/</UrlApi>

vira:

<UrlApi type="string">http://localhost:5107/api/</UrlApi>

Em todos os XMLs desta tabela, pode ser usado a cláusula WHERE para filtrar onde se deseja alterar os valores, assim evitando a alteração de todos os valores da tabela.

Esta query de Update foi desenvolvida pelo meu colega e Tech Lead Victor Alfani, o Linkedin dele se encontra abaixo.

Links e Referências

Agradeço a todos que leram este artigo, é o primeiro que escrevo e espero que não seja o único, fico aberto para questionamentos, feedbacks, Sugestões e Afins.