30 January 2017

How to Query XML from a SQL Server Blob Data Type

Overview

  • Today i am going to explain one of the possible ways to query XML from a SQL Server Blob data type.
  • First of all, this solution came up because a client asked for some facts about ongoing processes from a proprietary application. Then I passed this request onto the application team developers and after some debate it was decided to log every little change made to the processes. We ended with some snapshots of every process change being registered into a Blob data type column in a SQL Server Database.

 

The Data

  • Let us start by looking at this example of the XML Document I have to work with:

 

   “<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>”

The Solution

  • So I have a root element CONTACTO with many child elements that I need for my task. I used the value() method to retrieve information from the XML above. The value() method needs two arguments, a XQuery path and a SQL Server data type.  With this in mind I achieved the following query:

 

“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”

Breaking down the query CONTACTOXML.value(‘(/CONTACTO/NumeroContacto)[1]’, ‘varchar(max)’) AS NumContactofor better understanding:

 

  • CONTACTOXML.value() is the name of the column, in the source table, from which I am getting the value;
  • ‘(/CONTACTO/NumeroContacto)[1]’ is the Xquery path pointing to the specific element I want to retrieve. The [1] is because the value() method can only return one element thus requiring the specification of the element order we are querying in case there are 1 or more elements with the same name. It is called the singleton rule;
  • ‘varchar(max)’ is the second argument and represents the SQL Server data type;
  • AS NumContacto represents the name of the column I want to present in the query result

 

This query will now return, for every record, all the columns I choose from the XML elements inside CONTACTO source column.

.

.

.

.
        João Pinto
        Consultant
Blog