XML a Entidad-Relación

---

En esta entrada veremos cómo operar con documentos XML en un modelo entidad relación.   Para este objetivo utilizaremos el siguiente documento como fuente de datos:

DECLARE @Doc XML = '
<subcategories>
  <subcategory Name="Bib-Shorts">
    <products>
      <product ProductID="855" Name="Men''s Bib-Shorts, S" ListPrice="89.9900" />
      <product ProductID="856" Name="Men''s Bib-Shorts, M" ListPrice="89.9900" />
      <product ProductID="857" Name="Men''s Bib-Shorts, L" ListPrice="89.9900" />
    </products>
  </subcategory>
  <subcategory Name="Bike Racks">
    <products>
      <product ProductID="876" Name="Hitch Rack - 4-Bike" ListPrice="120.0000" />
    </products>
  </subcategory>
</subcategories>'

Podemos ver que hay un nodo raíz llamado subcategories y que contiene elementos subcategory quienes a su vez contienen a los productos que pertenecen a esa subcategoría.

Si queremos obtener los productos para poder cruzar la información del documento con alguna tabla resultaría muy complicado hacer un parser para obtenre la información que necesitamos.    Por este motivo SQL Server tiene funciones que nos permiten convertir el documento a una estructura entidad relación.

Antes de comenzar con los queries me gustaría mostrar los símbolos básicos del XPath, el cual es el lenguaje de búsqueda en XML:

Símbolo Uso . Elemento actual .. Elemento padre / Elemento hijo // Búsqueda secuencial y jerárquica [ ] Filtro @ Atributo

Debemos tener en cuenta que la navegación de un documento XML se hace nodo a nodo por lo que es preferible convertirlo primero a una estructura entidad-relación y no estar navegándolo una y otra vez, si es un documento pequeño no le vamos a encontrar mucho problema, pero si fuera un documento muy grande seguro vamos a tener problemas de rendimiento.

Supongamos que queremos obtener todos los productos que trae el documento, comenzaremos con obtener la ruta de navegación que tenemos que seguir para llegar a los elementos que necesitamos, en este caso la ruta es /subcategories/subcategory/products/product.    Esta ruta en particular devolverá todos los nodos cuya ruta coincida con la especificada en la función.

La función .nodes recibe como parámetro una sentencia XPath, construirá una tabla con una columna donde estará colocado cada nodo descubierto por la ruta; para sacar los valores del nodo ocuparemos la función .value de la siguiente manera:

SELECT c.value('@ProductID', 'INT') AS ProductID,
    c.value('@Name', 'NVARCHAR(50)') AS Name
FROM @Doc.nodes('/subcategories/subcategory/products/product') AS T(c)

En este query podemos ver en uso la función .nodes para obtener los nodos que tengan la ruta XPath especificada y después ejecutar la función .value sobre cada nodo obtenido para sacar su información.    La función .value recibe dos parámetros, el primero es el elemento que quiero obtener del nodo actual y el segundo es el tipo de dato al que lo voy a convertir en la estructura entidad-relación.

El resultado de la ejecución de este query es el siguiente:

Podemos ver que tomamos todos los nodos product y los colocamos en una estructura entidad-relación.

Ahora vamos a ocupar un filtro para obtener los productos que pertenecen a la subcategoría Bib-Shorts.

SELECT c.value('@ProductID', 'INT') AS ProductID,
    c.value('@Name', 'NVARCHAR(50)') AS Name
FROM @Doc.nodes('/subcategories/subcategory[@Name="Bib-Shorts"]/products/product') AS T(c)

Podemos ver que lo que cambió fue la ruta XPath que se utilizó como parámetro para la función .nodes, hemos agregado unos corchetes para que filtre los nodos subcategory que cumplan con que su atributo Name sea Bib-Shorts.   A continuación muestro el resultado:

¿Qué pasa si nos piden que mostremos el nombre de la categoría en una tabla que tenga todos los productos del documento XML?, recordemos que tenemos el símbolo ".." para XPath, modifiquemos el query para mostrar lo que nos piden:

SELECT c.value('../../@Name', 'NVARCHAR(50)') AS Subcategory,
    c.value('@ProductID', 'INT') AS ProductID,
    c.value('@Name', 'NVARCHAR(50)') AS Name
FROM @Doc.nodes('/subcategories/subcategory/products/product') AS T(c)

Podemos ver que agregamos una columna más al query donde utilizamos el parámetro ".." para irnos moviendo del nodo actual product hacia el padre dos lugares arriba en la jerarquía del documento.   Aquí está el resultado:

En la entrada siguiente abordaré la función XML .exist y en la entrada posterior veremos la función .query.

Espero te haya sido de utilidad esta entrada.

Programación en SQL Server - parte 4

---

En esta entrada veremos cómo utilizar la sentencia MERGE para poder actualizar el contenido de una tabla en base a la comparación contra otra tabla.

Supongamos que tenemos una tabla de productos y que nos envían en un archivo de XML los nuevos datos de los productos, en este archivo XML pueden venir productos nuevos y productos existentes que se desean actualizar.

Hay muchas formas de solucionar este problema, a mí se me ocurre la siguiente (en la forma tradicional):

1. Cargar el archivo XML a una tabla temporal.
2. Actualizar los productos que están en la tabla tempora y que existen en la tabla objetivo.
3. Insertar los productos que están en la tabla temporal pero que no existen en la tabla objetivo.

Este trabajo se puede resolver de una forma mucho más compacta con la instrucción MERGE, dado que dependiendo de la existencia de los elementos en la tabla objetivo podemos realizar tareas de inserción, actualización o eliminación.

Vamos a crear las estructuras que utilizaremos para explicar cómo funciona esta instrucción (no he colocado constraints de tipo UNIQUE ni CHECK para hacer más corto el código):

CREATE TABLE Products(
    Id TINYINT NOT NULL,
    Name VARCHAR(10) NOT NULL,
    ListPrice DECIMAL(8, 2) NOT NULL,
    CONSTRAINT pkProducts PRIMARY KEY (Id)
)
GO
INSERT INTO Products (Id, Name, ListPrice)
VALUES (1, 'Product 1', 10),
    (2, 'Product 2', 20),
    (3, 'Product 3', 30),
    (4, 'Product 4', 40)

Ahora voy a mostrar el XML que estaría recibiendo nuestro procedimiento almacenado para realizar la actualización o inserción de productos:

<products>
    <product id="2" name="new 2" listPrice="22" />
    <product id="4" name="new 4" listPrice="44" />
    <product id="5" name="Product 5" listPrice="50" />
</products>

Podemos notar que los productos 2 y 4 ya existen en nuestra tabla y que el producto 5 no, por lo que el resultado esperado es que los productos 2 y 4 sean actualizados y el producto 5 sea insertado en la tabla.

La sentencia MERGE tiene la siguiente sintaxis (reducida):

MERGE INTO Target T
USING Source S
ON T.Col1 = S.Col1
WHEN MATCHED THEN
    --Sentencia a ejecutarse cuando halle correspondencia entre las filas
WHEN NOT MATCHED THEN
    --Sentencia a ejecutarse cuando no halle correspondencia en la tabla Target
WHEN NOT MATCHED BY SOURCE THEN
    --Sentencia a ejecutarse cuando no halle correspondencia en la tabla Source

He colocado como nombres de tabla Target y Source dado que la tabla Target será el objetivo principal de las actividades a realizarse en base a la comparación entre las filas contra la tabla Source en su columna Col1.

En nuestro caso la tabla Target será Products mientras que la tabla Source será el documento XML que estamos recibiendo como parámetro.

La columna que en nuestro caso estaremos comparando para verificar si hay o no correspondencia será Id.

Voy a escribir el código completo para la declaración de la variable de tipo XML (simulando la recepción del parámetro) y la sentencia MERGE:

DECLARE @Products XML = '
<products>
    <product id="2" name="new 2" listPrice="22" />
    <product id="4" name="new 4" listPrice="44" />
    <product id="5" name="Product 5" listPrice="50" />
</products>'

MERGE INTO Products P
USING @Products.nodes('/products/product') T(c)
ON P.Id = c.value('@id', 'TINYINT')
WHEN MATCHED THEN
    UPDATE
    SET Name = c.value('@name', 'VARCHAR(10)'),
        ListPrice = c.value('@listPrice', 'DECIMAL(8, 2)')
WHEN NOT MATCHED THEN
    INSERT (Id, Name, ListPrice)
    VALUES (c.value('@id', 'TINYINT'),
        c.value('@name', 'VARCHAR(10)'),
        c.value('@listPrice', 'DECIMAL(8, 2)')
    );

Si revisamos el contenido de la tabla Products veremos lo siguiente:

Vemos que los productos 2 y 4 fueron actualizados acorde a los valores que tenía nuestro documento XML y que el producto 5 fue agregado con los valores del documento XML.

Notemos que la sentencias UPDATE e INSERT no indican en qué tabla se van a ejecutar, para entenderlo recordemos que la tabla que aparece después de MERGE INTO es la tabla objetivo, por lo tanto es la tabla a la que apuntarán las sentencias establecidas en WHEN MATCHED y WHEN NOT MATCHED.

En la siguiente entrada exploraremos con más detalle la forma en cómo podemos operar con documentos XML, espero te resulte de ayuda esta entrada.

Programación en SQL Server - parte 3

---

En la entrada anterior vimos cómo funciona el INNER JOIN para cruzar la información de dos o más tablas, en esta entrada abordaremos el LEFT JOIN.

Supongamos que tenemos el siguiente query:

SELECT T1.Col1, T1.Col2
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.Col1 = T2.Col1

A la tabla Table1 se le considera tabla izquierda y a la tabla Table2 se le considera la tabla derecha, es importante tener esto en mente para poder identificar qué datos vamos a obtener del query.

LEFT JOIN devolverá todas las filas de la tabla izquierda y de la tabla derecha devolverá valores para sus columnas cuando se haya encontrado una correspondencia en el predicado ON, para aquellas filas de la tabla izquierda que no encuentren correspondencia en la tabla derecha, las columnas de la tabla derecha tendrán el valor NULL.

Por ejemplo, supongamos que tenemos las siguientes tablas:

CREATE TABLE Categories(
    Id TINYINT IDENTITY(1, 1),
    Name VARCHAR(10) NOT NULL,
    CONSTRAINT pkCategories PRIMARY KEY (Id)
)
GO
CREATE TABLE Products(
    Id TINYINT IDENTITY(1, 1),
    Id_Category TINYINT NOT NULL,
    Name VARCHAR(10) NOT NULL,
    CONSTRAINT pkProducts PRIMARY KEY (Id),
    CONSTRAINT fkProducts_Categories FOREIGN KEY (Id_Category) REFERENCES Categories (Id)
)
GO
INSERT INTO Categories (Name)
VALUES ('Category 1'), ('Category 2'), ('Category 3')
GO
INSERT INTO Products (Id_Category, Name)
VALUES (1, 'Product 1'), (1, 'Product 2'), (3, 'Product 3')

Podemos ver que la categoría "Category 2" no tiene productos, ¿qué pasa si nos pidieran un reporte donde mostremos la cantidad de productos que tiene cada categoría?, intentemos con el INNER JOIN y veamos el resultado:

SELECT C.Name, COUNT(*) AS ProductsCount
FROM Categories C
INNER JOIN Products P
ON C.Id = P.Id_Category
GROUP BY C.Name

El resultado es el siguiente:

Podemos ver que la categoría "Category 2" no aparece en el resultado, esto es debido a que INNER JOIN sólo mostrará aquellas filas que encontraron correspondencia directa y como no se encontró el Id de la categoría "Category 2" en la tabla Products, entonces no se toma en cuenta.

Cambiemos el query por un LEFT JOIN y veamos que ahora sí muestra el resultado tal como lo queremos:

SELECT C.Name, COUNT(*) AS ProductsCount
FROM Categories C
LEFT JOIN Products P
ON C.Id = P.Id_Category
GROUP BY C.Name

Veamos el resultado:

¿Qué pasó? ¡se supone que no hay productos en la categoría "Category 2"!, el problema radica en que COUNT(*) cuenta la fila, y como "Category 2" forma parte del resultado por ser tabla izquierda en un LEFT JOIN, entonces la cuenta, veamos el query sin el COUNT(*) y verifiquemos que en efecto hay una fila que tiene "Category 2":

SELECT C.Name AS Category, P.Name AS Product
FROM Categories C
LEFT JOIN Products P
ON C.Id = P.Id_Category

Veamos el resultado:

Aún cuando no tenemos un valor en la columna de la tabla derecha (Products) la función de agregación COUNT(*) devuelve 1 porque existe una fila que tiene "Category 2".    Vamos a cambiar nuestro query para que cuente los valores de la tabla derecha y así descarte los nulos:

SELECT C.Name, COUNT(P.Name) AS ProductsCount
FROM Categories C
LEFT JOIN Products P
ON C.Id = P.Id_Category
GROUP BY C.Name

El resultado será el siguiente:

¡Ahora sí!, este es justamente el resultado que estábamos buscando, la categoría "Category 2" no tiene productos y por lo tanto debe mostrar el número cero.

Espero te resulte de ayuda esta entrada y haya quedado claro cómo utilizar el LEFT JOIN, en la siguiente entrada veré una instrucción súper interesante llamada MERGE.