Problema
- Hoje vou explicar uma das possibilidades para se trabalhar com uma coluna do tipo BLOB em SQL.
- Esta solução surgiu devido ao pedido de um cliente que queria monitorizar os processos de trabalho realizados numa aplicação customizada. Após algum debate com a equipa de desenvolvimento da aplicação ficou decidido que o melhor seria registar todas as alterações que os processos sofriam. Esta solução acabou por ser implementada através de “fotografias” do processo a serem guardadas após cada alteração, em forma de documento XML.
Os Dados
- Vamos então olhar para um exemplo do tipo de document XML com o qual tinha de trabalhar:
“<CONTACTO>
<Id>52588</Id>
<NumeroContacto>48800</NumeroContacto>
<AnoContacto>2016</AnoContacto>
<Nome>Danoninho</Nome>
<Morada>-</Morada>
<Cidade>Madrid</Cidade>
<CodigoPostal>-</CodigoPostal>
<PaisId>PT</PaisId>
<IsInVictoria>False</IsInVictoria>
<EstadoContactoId>2</EstadoContactoId>
<DesejaResposta>False</DesejaResposta>
<TipoComentarioId>0</TipoComentarioId>
<MotivoComentarioId>0</MotivoComentarioId>
…
</CONTACTO>”
A Solução
- Temos então o element “root” CONTACTO que possui muitos elementos filho, Usei o médtodo value() para extrair a a informação do documento XML acima apresentado. O método value() precisa de dois argumentos, um caminho XQuery e um tipo de data de SQL Server. Tendo o que foi escrito anteriormente em conta obtive as seguintes linhas de código:
“SELECT
CONTACTOXML.value(‘(/CONTACTO/NumeroContacto)[1]’, ‘varchar(max)’) AS NumContacto,
CONTACTOXML.value(‘(/CONTACTO/AnoContacto)[1]’, ‘varchar(max)’) AS AnoContacto,
CONTACTOXML.value(‘(/CONTACTO/AnoContacto)[1]’, ‘varchar(max)’)+’/’+CONTACTOXML.value(‘(/CONTACTO/NumeroContacto)[1]’, ‘varchar(max)’)AS CodContacto,
…
FROM DW_ODS_QLP.[MILES_TEMP].[CONTACTO_VERSAO]
ORDER BY CONTACTOID,ID”
Discriminando o código CONTACTOXML.value('(/CONTACTO/NumeroContacto)[1]', 'varchar(max)') AS NumContacto para melhor compreensão:
- CONTACTOXML.value() nome da coluna na tabela fonte de onde está a ser extraido o valor;
- ‘(/CONTACTO/NumeroContacto)[1]’ é o caminho Xquery que aponta para um element específico que eu quero obter. O [1] é porque o método value() só pode retornar um único element e por isso precisa que se específique qual a posição do elemento que queremos obter em caso de ser repetido. Tem o nome de “singleton rule”;
- ‘varchar(max)’ é o Segundo argumento e representa o tipo de data de SQL Server;
- AS NumContacto representa o nome da coluna que eu quero apresentar no resultado final
Esta pesquisa vai retornar, para cada registo, todas as colunas que eu escolhi do document XML dentro da coluna CONTACTO.