30 Janeiro 2017

Como Extrair Dados de uma Coluna do Tipo Blob em SQL Server

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.

.
.
.
.
.
.
         João Pinto
         Consultant