Aquellas pequeñas cosas, transformando datos XML en un rowset (es-ES)

Aquellas pequeñas cosas, transformando datos XML en un rowset

El intercambio de información entre empresas y organizaciones representan una tarea cada vez más común, por esto no es de extrañar el amplio uso y aceptación del formato XML como estándar para el intercambio de dicha información.

SQL Server cuenta entre sus muchas características con una serie de funciones que nos permiten potenciar el uso de XML, pero en esta ocasión nos centraremos el proceso de transformación de datos XML en un conjunto de datos (rowset).

El procesamiento de XML se realiza en cinco pasos fundamentales como se ilustra en la siguiente imagen:

Una vez que se cuenta con el documento XML (bien formado) este se procede a procesar por medio del procedimiento almacenado del sistema sp_xml_preparedocument, el cual nos permite validar un documento XML y transformarlo en una estructura de árbol en memoria, cuyo contenido podemos consultar por medio de la instrucción OPENXML para obtener las columnas deseadas y así manipular los datos en la forma requerida.

Iniciemos con un ejemplo simple, suponiendo que formamos parte de la empresa dedicada a la reventa de artículos por internet y estamos recibiendo información de un proveedor externo, el documento XML es el siguiente:

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Linea ProductoID="12" Cantidad="2" PrecioUnitario="12.99"><Descripcion>Encendedor marca Zippo</Descripcion></Linea>

<Linea ProductoID="2" Cantidad="1" PrecioUnitario="2.99"><Descripcion>Bebida embotellada natural</Descripcion></Linea>

</Lineas>

</Factura>'

DECLARE @IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc OUTPUT, @doc

De forma simple podemos decir que el sp_xml_preparedocument nos permite procesar un documento XML y obtener una representación del mismo, el resultado de este sp es un código identificador que apunta hacia la estructura de árbol del XML creado previamente. Una descripción más amplia se encuentra en el siguiente link:

http://msdn.microsoft.com/es-es/library/ms187367.aspx

Una vez que construida la estructura o árbol en memoria debemos acceder a ella, para esto nos apoyamos en la instrucción OPENXML , este nos permite obtener un conjunto de datos a partir del árbol.

La instrucción OPENXML tiene la siguiente estructura:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

El parámetro idoc representaría en nuestro caso el parámetro de salida resultante de la ejecución del sp_xml_preparedocument, en donde especifica la representación interna del documento XML, el patrón de filas (rowpattern) es el patrón XPATH que permite definir la ruta o nodos que deberá reconocerse en la lectura del XML, a continuación nuestro código:

SELECT * FROM

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

WITH

( FacturaTarget int '../../@FacturaID',

ClienteTarget int '../../@ClienteID',

FechaFacturaTarget datetime '../../@FechaFactura',

FormaPagoTarget int '../../@FormaPago',

DetallesTarget varchar(30) './Descripcion',

CantidadTarget int '@Cantidad',

PrecioUnitarioTarget float '@PrecioUnitario')

En este caso especifico utilizamos una opción conocida como patrones de columna, estos contienen un patron XPath para la recuperación de los nodos y este será relativo al patrón de fila (rowpattern) especificado.

Y esto que quiere decir? Que la recuperación de los nodos estará basada en una expresión o expresiones en donde se navegara a través de la jerarquía del documento XML para obtener los datos deseados.

Aquí caben notar dos cosas:

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

Le estamos indicando a la instrucción que debe tomar la representación interna perteneciente al documento que acabamos de transformar, posteriormente le indicamos hasta que nivel deseo “navegar” en la estructura del documento XML. Posteriormente comenzamos a construir la estructura que deseamos recuperar, especificando el nombre del campo, su tipo de datos y el origen o expresión XPATH a partir de donde obtendrá los datos.

WITH

( FacturaTarget int '../../@FacturaID',

ClienteTarget int '../../@ClienteID',

FechaFacturaTarget datetime '../../@FechaFactura',

FormaPagoTarget int '../../@FormaPago',

DetallesTarget varchar(30) './Descripcion',

CantidadTarget int '@Cantidad',

PrecioUnitarioTarget float '@PrecioUnitario')

El manejo o navegación se realize desde el ultimo nodo que se especifico en el patron de fila, cabe mencionar que los atributos se representan por medio de @nombreatributo, el siguiente caso funciona así:

FacturaTarget int '../../@FacturaID'

FacturaTarget int: Es el campo con su respectivo tipo de datos que almacenara el resultado devuelto por la expresión.

../../@FacturaID: Cabe recordar que según el patrón de fila este tiene una estructura '/Factura/Lineas/Linea' , en este caso se quiere recuperar el atributo FacturaID, este se encuentra en el nodo correspondiente a Factura, por lo que si partimos de la ruta especificada en el patrón de filas y viendo el documento XML tenemos que subir dos niveles , para este caso se usa la expresión << ../>>.

En los casos de los atributos que se encuentran en el ultimo nivel (Linea) solo se debe de anteponer el símbolo @NombreAtributo.

Un caso especial aquí es el del ELEMENTO Descripcion, debido a su naturaleza y ubicación dentro del XML para obtener su valor se debe de especificar de la siguiente manera:

./Descripcion'

Aquí le indicamos que seleccione el nodo actual a partir del nodo raíz (nodo Linea), como punto de ayuda presento una tabla resumen de las principales expresiones de ruta usadas en XPath.

Expresión	Descripción
nodename	Selecciona todos los nodos hijos del nodename
/	Selecciona a partir del nodo raiz.
.	Selecciona el nodo actual
..	Selecciona el padre del nodo actual
@	Selecciona atributos

Con estos pasos descritos anteriormente ya tenemos como resultado los campos extraidos directamente de un documento XML. Nuestra consulta completa quedaría así:

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Linea ProductoID="12" Cantidad="2" PrecioUnitario="12.99"><Descripcion>Encendedor marca Zippo</Descripcion></Linea>

<Linea ProductoID="2" Cantidad="1" PrecioUnitario="2.99"><Descripcion>Bebida embotellada natural</Descripcion></Linea>

</Lineas>

</Factura>'

DECLARE @IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc OUTPUT, @doc

SELECT * FROM

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

WITH

( FacturaTarget int '../../@FacturaID',