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.