Consultar valores de un XML en SQL Server

Es posible consultar valores de un XML en SQL Server y mostrarlos en forma de tabla como cualquier otra consulta.

Para esta tarea debemos tener un archivo XML guardado en nuestra base de datos en forma de Binary.

En esta ocasión no solo veremos la estructura del XML, más bien manipularemos el XML para recuperar únicamente los datos.

Podría interesarte Realizar un respaldo en SQL Server 2017  

Convertimos el archivo XML

Iniciemos declarando una variable de tipo XML:

DECLARE @ArchivoXML xml;

Cargamos en la variable XML declarado el XML guardado en la base de datos, se debe realizar mediante una consulta SELECT.

SELECT
    @ArchivoXML = CONVERT(XML, ArchivoXML)
FROM
    Productos
WHERE
    ID = 1;

Convertimos el valor de la columna ArchivoXML de binary a XML y la guardamos en @ArchivoXML.

Hasta ahora solo obtuvimos el XML, pero no se puede mostrar en forma de tabla para ellos es necesario realizar algunas acciones.

El archivo XML que se obtiene es la siguiente:

<ArrayOfProductos xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Producto ID="a02a23db-15d4-4efe-959f-50f82dfeadd9" Descripcion="INSUMOS" Estatus="1" />
    <Producto ID="745f2752-d366-4b6a-9fd8-c96615587a40" Descripcion="CMPRAS" Estatus="1" />
</ArrayOfProductos>

Obtener los datos del XML

Realizamos una consulta SELECT a la variable @ArchivoXML:

SELECT
    Tabla.Col.value('@ID','VARCHAR(50)') AS 'ID',
    Tabla.Col.value('@Descripcion','VARCHAR(30)') AS 'Descripcion',
    Tabla.Col.value('@Estatus','TINYINT') AS 'Estatus'
FROM
    @ArchivoXML.nodes('//ArrayOfProductos/Producto') Tabla(Col);

Definimos que la variable @ArchivoXML se comporte como una tabla declarándolo Tabla(Col).

En SELECT definimos:

• Tabla.Col.Value : de la tabla y columna se obtenga el valor.
• @ID : es la descripción del atributo en el xml.
• VARCHAR(50) : tipo de dato del valor del atributo obtenido.
• AS ‘ID’ : especificación del encabezado de la tabla de salida.

Los datos que se obtienen están ubicados en el nivel especificado en el nodo:

@ArchivoXML.nodes('//ArrayOfProductos/Producto')

Podría interesarte ORDER BY y UNION en SQL Server  

El código completo es:

DECLARE @ArchivoXML xml;
SELECT
    @ArchivoXML = CONVERT(XML, ArchivoXML)
FROM
    Productos
WHERE
    ID = 1;

SELECT
    Tabla.Col.value('@ID','VARCHAR(50)') AS 'ID',
    Tabla.Col.value('@Descripcion','VARCHAR(30)') AS 'Descripcion',
    Tabla.Col.value('@Estatus','TINYINT') AS 'Estatus'
FROM
    @ArchivoXML.nodes('//ArrayOfProductos/Producto') Tabla(Col);

Como resultado tenemos: