Datos XML en SQL Server

Datos XML en SQL Server (Parte 1) Los documentos XML son un medio flexible y estándar para la publicación, e intercambio de información entre sistemas heterogéneos. Es muy común encontrar en diversos sistemas alguna forma de importar/exportar información a través de este formato. En éste post se destacarán las principales características que ofrece Microsoft SQL Server para trabajar con XML.

Almacenamiento Microsoft SQL Server, desde la versión 2005, nos permite almacenar datos XML como registros, admitiendo el tipo de datos XML conjuntamente con los tipos de datos tradicionales. Se pueden crear columnas y variables de tipo XML. Además este tipo de datos permite almacenar documentos y fragmentos XML (un fragmento XML es una instancia XML en la que falta un solo elemento de nivel superior). También se puede asociar una colección de esquemas XML a una columna, un parámetro o una variable del tipo de datos XML de forma tal de validar y asignar un tipo a las instancias. Hay que tener en cuenta que SQL Server no es un sistema XML nativo, por lo que el almacenamiento se realizará en forma de representación binaria con cierta estructura, especialmente optimizada para realizar consultas (mediante el lenguaje XQuery). Existen algunas limitaciones que se deberán tener en cuenta al momento de utilizar este tipo de datos: La representación almacenada de las instancias del tipo de datos xml no puede superar los 2 GB. No admite la conversión a text ni a ntext. Use varchar(max) o nvarchar(max) en su lugar. No puede compararse ni ordenarse. Esto significa que un tipo de datos xml no puede utilizarse en una instrucción GROUP BY.

Consultas sobre datos XML SQL Server ofrece un conjunto de funciones que pueden aplicarse sobre una columna o variable de tipo XML. Vamos a definir la siguiente tabla para mostrar algunos ejemplos: DECLARE @MiTabla TABLE ( Identificador INTEGER, MiXML XML ) INSERT INTO @MiTabla (Identificador, MiXML) VALUES (1, ‘ José Carlos Romano Soltero Susana Colman Casado ’) SELECT * FROM @MiTabla En este caso tendremos una sola fila, con el siguiente XML en la columna MiXML: José Carlos Romano Soltero Susana Colman Casado

Mediante la función query() se pueden obtener los subárboles de un XML que cumplan con el path XQuery pasado por parámetro. Por ejemplo, para obtener el subárbol Empleado para el Id = 1: SELECT MiXML.query(‘/Datos/Empleado[@id=1]’) FROM @MiTabla WHERE Identificador = 1 De modo similar, se puede consultar si existe algún nodo que cumpla con un path específico, mediante la función exist(): SELECT MiXML.exist(‘/Datos/Empleado/Surname’) FROM @MiTabla WHERE Identificador = 1 Lo cual devolverá false (0), ya que no existe ningún elemento de nombre “Surname” dentro de algún elemento Empleado en el nodo Datos. La función value() permite obtener el valor de un elemento o atributo del documento. Como ejemplo, para obtener el valor del nombre del Empleado con atributo “id=2” será necesario consultar de la siguiente forma: SELECT MiXML.value(‘(/Datos/Empleado/Nombre)[1]’,’nvarchar(20)’) FROM @MiTabla WHERE Identificador = 1 El resultado es: José Carlos Y para obtener el valor del elemento Apellido del Empleado cuyo atributo id sea 2: SELECT MiXML.value(‘(/Datos/Empleado[@id = 2]/Apellido)[1]’,’varchar(20)’) FROM @MiTabla El resultado en este caso es: Colman Como se habrá notado, para obtener el valor de un nodo es necesario especificar una ruta que lo identifique unívocamente, ya que si se utilizara la

misma ruta de los ejemplos anteriores (/Datos/Empleado), se podría estar haciendo referencia a múltiples elementos. También es necesario indicar el tipo de datos del valor que se quiere seleccionar.

Datos XML en SQL Server (Parte 2)

Ver primera parte del post aquí: Datos XML en SQL Server (Parte 1). Como se ha comentado se debe tener en cuenta que SQL Server no es un sistema nativo XML y que en el caso de XML hace una almacenamiento de los datos en forma binaria. En esta segunda parte se comentarán la forma de actualización de los datos XML y algunas recomendaciones al momento de trabajar con datos XML en SQL Server. Uno de los primeros temas a tener en cuenta es que deben evitarse los paths relativos y el uso de “//” para hacer una búsqueda en todo el documento, esto conspira fuertemente contra la eficiencia, especialmente cuando el documento XML es grande. SQL Server también permite formas más avanzadas para procesar los datos XML. Una de éstas es mediante la función nodes(), la cual es imprescindible si necesitamos transformar un conjunto de nodos de un documento a una forma tabular (tabla). Esta función se utiliza generalmente operadores CROSSAPPLY y OUTER APPLY.

en

conjunto

con

los

Por ejemplo si es necesario devolver un resultset, con columnas ID y Valor, que corresponden al id de cada nodo y a su contenido respectivamente. Para esto, se puede utilizar una consulta similar a la siguiente: SELECT T1.C1.value(‘(@id)[1]‘,‘int’) AS ID, T1.C1.value(‘.’,‘varchar(16)’) AS Valor FROM @MiTabla CROSS APPLY MiXML.nodes(‘/Datos/Empleado’) T1(C1)

WHEREIdentificador = 1 En este caso, el método nodes() permite crear instancias especiales XML (un registro por cada nodo que cumpla con el path especificado por el parámetro, conteniendo su súbarbol, insertado sobre una columna XML “C1” en una tabla ficticia “T1”). Esta tabla es combinada con el resto mediante un producto cartesiano (en este caso similar a un CROSS JOIN, o a un OUTER JOIN, cuando se utiliza OUTER APPLY). La columna C1 podrá luego ser utilizada como un campo cualquiera (XML) dentro del SELECT, al igual que en el ejemplo. Actualizaciones Al igual que para las consultas, las actualizaciones se realizan mediante una función especial modify(), que permite realizar cambios sobre un documento almacenado. Cómo ejemplo básico, si se quisiera agregar un nuevo nodo Empleado al XML del ejemplo, se debería utilizar la siguiente consulta: UPDATE @MiTabla SET MiXML.modify(‘insert Alejandra Moreno Casado

as last into (/Datos[1])’) WHERE Identificador = 1 Como resultado se insertará un nuevo elemento Empleado como último nodo dentro de la raíz Datos del XML de ejemplo. José CarlosNombre>

RomanoApellido> SolteroEstadoCivil> Empleado> SusanaNombre> ColmanApellido> CasadoEstadoCivil> Empleado> AlejandraNombre> MorenoApellido> CasadoEstadoCivil> Empleado> Datos> Se debe tener en cuenta que todas las funciones presentadas requieren que el path que reciben sea un “string literal” (una cadena de texto plano), esto implica que debe ser un único parámetro y no puede ser el resultado de concatenaciones. Esto puede resultar poco flexible, ya que ante una actualización o consulta, podríamos querer insertar o comprar datos no estáticos. Por esta razón se brindan las funciones sql:variable() y sql:column(). Volviendo al ejemplo si se quisiera insertar el valor almacenado en una variable se podría rescribir el UPDATE de la siguiente manera: DECLARE @DatoAInsertar AS XML = ‘Alejandra

Moreno Casado

UPDATE @MiTabla SET MiXML.modify(‘insert sql:variable(“@DatoAInsertar”) as last into (/Datos[1])’) WHERE Identificador = 1 Consideraciones Se debe tener en cuenta que un documento XML, una vez almacenado en una tabla con tipo de datos XML, será tratado como cualquier registro, por lo que si la instancia de un documento es muy grande (mas de 1GB por ejemplo), y además sufre gran cantidad de actualizaciones o bloqueos (se bloquea siempre el documento entero), la performance del sistema puede degradarse. Cada vez que se realizada una consulta sobre un documento XML, éste debe ser parseado, por lo que a medida que el documento crece, la degradación de rendimiento también se hace evidente. Para esto se permite la indexación de los datos XML para colaborar en una mayor eficiencia, siempre hay que tener en cuenta el costo de mantener los índices ante las actualizaciones en los casos en que los documentos XML se actualizan en forma muy frecuente. Conclusión Como se a expuesto SQL Server brinda un conjunto amplio de funciones y operadores para manipular documentos XML, sin embargo siempre hay que tener en cuenta las características de las cargas transaccionales que se ejecutarán sobre los datos antes de optar por almacenarlos como XML dentro del sistema. En general si el documento resultante es grande y necesita ser actualizado frecuentemente, deberíamos optar por una forma tradicional para almacenar la información. Por otro lado, cuando el juego de datos resulta ser bastante estático y/o pequeño, podemos aprovechar las características del formato XML, como la estructura jerárquica o la auto-descriptividad.