GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Datos XML y SQL Server 2005 (Introducción)


SQL Server 2005 incluye el nuevo tipo de datos XML capaz de almacenar documentos XML ó fragmentos XML, que es posible utilizar tanto en variables como en campos de tablas, y que a diferencia de otros tipos de datos, ofrece métodos (si, si, métodos, como en la programación orientada a objetos ;-) con los que poder interactuar con este nuevo tipo de dato XML, de una forma extraordinariamente potente, junto con consultas XQuery (lenguaje estándar del W3C para consultas XML), posibilidad de creación de índices sobre campos XML, posibilidad de almacenar hasta 2GB (es un tipo de dato LOB ó BLOB), etc. Es importante recordar que XML es susceptible de mayúsculas y minúsculas, algo que tendremos que tener en cuenta al interactuar con datos XML.

Introducción al tipo de dato XML, sus métodos y funciones XQuery

SQL Server 2000 ya incluía ciertas posibilidades de trabajar con XML (la cláusula FOR XML AUTO de las sentencias SELECT, OPENXML y sp_xml_preparedocument, etc.). Sin embargo, con SQL Server 2005 se aumentaron las posibilidades de manipular datos XML en SQL Server con Transact-SQL, hasta límites insospechados, gracias al nuevo tipo de datos XML (un tipo de dato grande o LOB), y al resto de elementos que existen a su alrededor. Ahora, además de consultas SQL con la cláusula FOR XML AUTO o FOR XML RAW, existen muchas más funcionalidades para trabajar con XML en la base de datos. El nuevo tipo de datos XML y sus posibilidades de programación en Transact-SQL (TSQL) con XML en SQL Server es una gran aportación para los desarrolladores, que día a día, van encontrándose XML hasta en la Sopa (SOAP en inglés, o algo así ;-). El hecho de convertir nodos XML en filas, o la posibilidad de ejecutar consultas XQuery sobre un tipo de datos XML almacenado en SQL Server, son nuevas funcionalidades (y existe más) de gran utilidad para desarrolladores.

En cualquier caso, y aún siendo este un artículo sobre XML con Transact-SQL y SQL Server a nivel introductorio, confío pueda servir de una primera orientación a desarrolladores que necesiten programar con XML en SQL Server.

Entrando en harina, como comentamos anteriormente, el tipo de dato XML aporta una serie de métodos para poder trabajar con el, de forma similar a como se programa en lenguajes orientados a objetos (bueno, algo más casero). Los métodos del tipo de dato XML son los siguientes:

  • Método XML query(). Devuelve el resultado de ejecutar una consulta XQuery, por lo tanto, el resultado también es código XML. Sintaxis: query(XQuery).
  • Método XML value().Devuelve un valor escalar en algún tipo de dato de SQL Server, como resultado de ejecutar una consulta XQuery sobre un dato XML (el resultado NO es código XML). Resulta de utilidad, por ejemplo, para combinar o comparar datos XML con datos NO XML en una consulta. Sintaxis: value(XQuery, SQLType).
  • Método XML exist(). Devuelve un bit como resultado de ejecutar una consulta XQuery sobre un dato XML, de tal modo, que los resultados posibles son:
    • 1, si el resultado de la consulta XQuery NO es vacío.
    • 0, si el resultado de la consulta XQuery es vacío.
    • NULL, si el dato XML que se está consultando, es NULL.
    El método XML exist() suele utilizarse en la cláusula WHERE de consultas SQL. Sintaxis: exist(XQuery).
  • Método XML nodes(). Permite convertir un conjunto de Nodos, en un conjunto de filas. Suele utilizarse en la cláusula FROM de las consultas SQL, y habitualmente, se utiliza junto con el operador APPLY (ya sea con CROSS APPLY o con OUTER APPLY). Sintaxis: nodes(XQuery) as Table(column).
  • Método XML modify(). Modifica el contenido de un XML, ejecutando sentencias XML DML (insert, delete y replace value of). Sólo puede ser utilizado en la cláusula SET de la sentencia UPDATE, o bien, en la sentencia SET. Sintaxis: modify(XML_DML).

Por razones de rendimiento, en las comparaciones es preferible utilizar el método exist() con sql.column(), en vez de utilizar el método value(). Es decir, en vez de utilizar una consulta como la siguiente:

SELECT ProductName
FROM Products
WHERE XmlProduct.value( '/root[1]/@Stock', 'integer') = Stock

Es preferible utilizar una consulta como se muestra a continuación:

SELECT ProductName
FROM Products
WHERE XmlProduct.exist( '/root[@Stock=sql:column("Stock")]') = 1

Antes de continuar, quiero introducir algún concepto básico de XML que nos va a resultar de utilidad. Para empezar, es importante saber qué es un Documento XML bien formado (well formed). Un Documento XML bien formado es aquel documento XML que cumple las características básicas del formato XML (debe ser susceptible de mayúsculas y minúsculas, utilizar un único elemento raíz, y resto de normas sintácticas básicas). En ocasiones trabajaremos con Fragmentos XML, es decir, con un trozo de un documento XML que quizás no tenga un único elemento raíz.

Aclarada la diferencia entre Documento XML bien formado y Fragmento XML, es hora de hablar del concepto de Documento XML válido. Un Documento XML válido es aquel documento XML que cumple con un esquema determinado, ya se trate de un esquema DTD (de esos antiguos) o de un esquema XSD (más moderno y potente, escrito a su vez en XML). En este caso, un Documento XML válido será aquel que esta asociado a un esquema, y además el contenido del Documento XML cumple el esquema que tiene asociado. En caso contrario, hablaremos de un Documento XML no válido.

Dicho todo esto, ahora podemos comentar que a partir de SQL Server 2005 podemos almacenar esquemas XML en SQL Server, de tal modo, podamos asociar un dato XML con un Esquema XML. De este modo, podemos hablar de datos XML con tipo o sin tipo (es decir, con esquema asociado o sin esquema asociado).

De este modo, podemos crear colecciones de esquema XML con la sentencia CREATE XML SCHEMA COLLECTION. Seguidamente, podemos declarar una variable XML sin tipo (ej: DECLARE @miVar XML) o bien, una variable XML con tipo (ej: DECLARE @miVar XML (MiSchemaCollection), especificando la colección de esquema deseada).

Volviendo al maravilloso mundo de las Consultas XQuery, para el desarrollo de consultas XQuery en SQL Server es muy útil utilizar las funciones de que disponemos en SQL Server para conseguir fácilmente el objetivo que necesitamos. A modo de resumen aprovecho para enumerar las distintas funciones que podemos utilizar en consultas XQuery (una explicación detallada de cada una queda fuera del alcance de este artículo, aunque puede resultar muy útil consultar los Libros en Pantalla o BOL - Books On Line):

  • Funciones sobre valores numéricos: ceiling, floor y round.
  • Funciones sobre valores de cadena: concat, contains, substring, string-length.
  • Funciones sobre valores binarios: not.
  • Funciones sobre nodos: number, local-number, namespace-uri.
  • Funciones contextuales: last, position.
  • Funciones sobre secuencias: empty, distinct-values, id.
  • Funciones de grupo: count, avg, min, max, sum.
  • Funciones constructoras. Permiten realizar casting. datetime, date, etc.
  • Funciones constructoras binarias: true, false.
  • Funciones de acceso a datos: string, data.
  • Funciones sobre Qnames: expanded-QName, local-name-from-QName, namespace-uri-from-QName.
  • Funciones extendidas: sql:column() y sql:variable().

Índices sobre tipos de datos XML

La problemática de las consultas sobre datos XML, es debida a dos motivos. Por un lado, el tipo de dato XML es un tipo de dato grande (LOB ó BLOB), lo que puede implicar que en tablas con muchas filas con datos XML voluminosos, el acceso a disco pueda tirar completamente el rendimiento de la consulta. Por otro lado, no siempre se desea utilizar el dato XML como una única unidad, es decir, suele ser necesario consultar por el contenido del dato XML (ej: por el valor de algún elemento o propiedad en particular), lo cual, implica tener que procesar al vuelo el contenido del dato XML para acceder a la propiedad o elemento deseado (algo muy costoso).

Partiendo de estas premisas, Microsoft ha incorporado en SQL Server 2005 la posibilidad de crear índices XML, consiguiendo así mejorar el rendimiento de las consultas XML (XQuery).

Dada la naturaleza de los tipos de datos XML y de las consultas que puede ser necesario realizar sobre los mismos, es posible definir distintos tipos de índices XML, de tal modo que en función del tipo de consultas que deseemos realizar/optimizar, será interesante utilizar índices de un tipo o de otro.

Los tipos de índice XML que podemos definir son los siguientes:

  • Indices XML Primarios. Resulta de utilidad cuando se ejecutan consultas que utilizan el método XML exist() en la cláusula WHERE. Para crear un índice XML primario, se debe ejecutar una sentencia CREATE PRIMARY XML INDEX.
  • Indices XML Secundarios. Para poder crear un índice XML secundario, es necesario que previamente exista un índice XML primario. Para crear un índice XML secundario, se debe ejecutar una sentencia CREATE XML INDEX.
    • Indices XML Secundarios de tipo PATH. Resulta de utilidad cuando se ejecutan consultas basadas en caminos (PATH) como las que utilizan el método XML exist() en la cláusula WHERE. Ofrece mejor rendimiento que el índice XML primario.
    • Indices XML Secundarios de tipo VALUE. Resulta de utilidad cuando las consultas están basadas en valores (texto de los elementos XML) y el camino (PATH) no está completamente especificado o incluye comodines.
    • Indices XML Secundarios de tipo PROPERTY. Resulta de utilidad cuando se desean recuperar propiedades de elementos XML utilizando el método XML value() en la SELECT y además se conoce el valor de la clave primaria para el registro buscado (y se especifica en la cláusula WHERE, claro ;-).

Ejemplos de manejar XML con Transact-SQL en SQL Server 2005

Por último, antes de finalizar quería incluir un pequeño trozo de código Transact-SQL con varios ejemplos de manipulación de XML en SQL Server, de modo que podamos ver la utilización de los métodos XML en SQL Server, ejemplos de consultas XQuery en SQL Server, etc.

DECLARE @xml as XML
DECLARE @xml_source as varchar(1000)

SET @xml_source = ''
SET @xml_source = @xml_source + '<Coche Matricula="M1234AB" Fecha="1999-07-21Z">'
SET @xml_source = @xml_source + '   <Marca>Seat</Marca>'
SET @xml_source = @xml_source + '   <Modelo>Cordoba TDI Sport</Modelo>'
SET @xml_source = @xml_source + '   <Extras>'
SET @xml_source = @xml_source + '      <Pintura>Azul metalizado</Pintura>'
SET @xml_source = @xml_source + '      <Airbag>Conductor, acompañante, y laterales</Airbag>'
SET @xml_source = @xml_source + '      <Descripcion Title="Utilitario diesel">Utilitario diesel 2 puertas</Descripcion>'
SET @xml_source = @xml_source + '      <Descripcion Title="Utilitario bajo consumo">Utilitario bajo consumo para ciudad</Descripcion>'
SET @xml_source = @xml_source + '   </Extras>'
SET @xml_source = @xml_source + '</Coche>'

SET @xml = @xml_source

-- *** Devuelve 'Seat', 'NULL', 'Utilitario bajo consumo para ciudad', 'Utilitario bajo consumo'
SELECT @xml.value('(/Coche/Marca)[1]', 'varchar(100)'),
   @xml.value('(/Coche/Marca)[2]', 'varchar(100)'),
   @xml.value('(/Coche//Descripcion)[2]', 'varchar(100)'),
   @xml.value('(/Coche//Descripcion/@Title)[2]', 'varchar(100)')

-- *** Consultas varias
SELECT @xml.query('.')
SELECT @xml.query('/Coche')
SELECT @xml.query('/Coche//Descripcion')
SELECT @xml.query('/Coche/Extras/Descripcion[1]')
SELECT @xml.query('/Coche/Extras/Descripcion[1]/text()')

-- Devuelve una fila de resultados para cada elemento Description
SELECT T.c.query('.') AS result
FROM @xml.nodes('/Coche/Extras/Descripcion') T(c)

-- Devuelve una fila de resultados para cada subelemento de Extras
SELECT T.c.query('.') AS result
FROM @xml.nodes('/Coche/Extras/*') T(c)

-- Comprueba si existe al menos un elemento Coche
SELECT @xml.exist('/Coche')

-- Comprueba si existe al menos un elemento Marca dentro de un elemento Coche
SELECT @xml.exist('/Coche/Marca')

-- Comprueba si existe al menos un elemento Marca dentro de algún elemento Coche
SELECT @xml.exist('/Coche/Marca')

-- Comprueba si existe al menos un elemento Coche de Marca Seat
SELECT @xml.exist('/Coche/Marca[text() = "Seat"]')

-- Comprueba si el primer elemento Coche es de Marca Seat
SELECT @xml.exist('/Coche[1]/Marca[text() = "Seat"]')

-- Comprueba si existe al menos un elemento Descripcion (subelemento de Coche y de Extras) con el parámetro Title igual a "Utilitario diesel"
SELECT @xml.exist('/Coche/Extras/Descripcion[@Title="Utilitario diesel"]')

-- Comprueba si existe al menos un elemento Descripcion (subelemento de Coche y de Extras) con el parámetro Title igual a "Utilitario diesel"
SELECT @xml.exist('/Coche/Extras/Descripcion[@Title eq "Utilitario diesel"]')

SELECT @xml.exist('/Coche[(@Fecha cast as xs:date?) eq xs:date("1999-07-21Z")]')

Y poco más que contar en este artículo introductorio sobre XML en SQL Server 2005. Espero al menos, haber conseguido mi objetivo, de servir como primera orientación para empezar el manejo y programación de datos XML en SQL Server, ya que en lo relacionado con XML en SQL Server se podría hablar mucho, pero que mucho más.


[Fecha del Artículo (UTC): 11/05/2009]
[Autor: GuilleSQL]


Comentarios

veca - 26/07/2011 (UTC)
EStimado, si tuvieras que leer el xml desde un archivo, pero en SQL SERVER 2000 como se puede hacer para leer el archivo y dejarlo en la variable @xml_source?


Gracias



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas