XML con PostgreSQL

En el artículo Alternativas EAV con XML expliqué cómo se podía implementar una mejora del modelo Entity Attribute Value (Entidad-Atributo-Valor) usando XML. Este artículo es, en alguna medida, una continuación de aquél, cubriendo ciertos aspectos importantes sobre la consulta a éstos campos o cualquier otro campo que contenga xml. El uso de las características de este artículo requiere que la instalación de postgresql se haya realizado con el soporte xml (configure --with-libxml).

Serialización/deserialización XML de campos varchar
Hay ocasiones en que necesitamos que los campos que contienen XML sean de tipo caracter y no de tipo xml nativo. Una razón para hacer eso, por ejemplo, es que estemos usando un ORM. En algunos casos, pongamos por ejemplo PostgreSQL con Toplink Essentials (incluído de serie en Glassfish 2.1), no hay forma (al menos yo no la he encontrado) de que el ORM haga mapping de campos de tipo xml. Para estos casos, en primer lugar, debemos producir un valor de tipo xml a partir de datos carácter, para lo que podemos usar la función xmlparse o realizar un type cast a xml usando la sintaxis tradicional de PostgreSQL expression::type o bien usando la sintaxis SQL-92 estándar CAST ( expression AS type ), así:

SELECT XMLPARSE( DOCUMENT campo) FROM tabla WHERE campo is not null

<=>

SELECT cast(campo as xml) FROM tabla WHERE campo is not null

<=>

SELECT campo::xml FROM tabla WHERE campo is not null

Consultas: la función xpath

Para procesar valores de tipo xml, PostgreSQL ofrece la función xpath, que evalúa expresiones XPath 1.0.

xpath(xpath_expr, xml_value[, nsarray])

La función xpath evalúa la expresión XPath xpath_expr contra el valor XML xml_value (debe ser un documento XML bien formado), devolviendo un array de valores XML correspondiente al conjunto de nodos producidos por la expresión XPath. El tercer argumento, opcional, es el array bidimensional de espacios de nombress (nombre espacio de nombres,URI espacio de nombres) que use el documento XML xml_value.


Por ejemplo, dado un campo campo de la tabla tabla, de tipo varchar(2048), podríamos consultar el contenido así (a partir de este momento usaremos la sintaxis del último ejemplo, la tradicional de PostgreSQL, por ser la más sencilla):

Consulta

Resultado

SELECT campo::xml FROM tabla where campo is not null

campo ---------------------------------------------------------- <xmlData><data name="incidencia"> <data value="tiempo" name="motivo"/></data></xmlData> <xmlData><data name="incidencia"> <data value="hardware" name="motivo"/></data></xmlData> <xmlData><data name="incidencia"> <data value="hardware" name="motivo"/></data></xmlData> <xmlData><data name="cantidad" value="2"></xmlData> <xmlData><data name="cantidad" value="3"></xmlData>

Es importante recordar que la función xpath devuelve un array de valores XML. Por ejemplo, si deseamos consultar sólo el contenido de aquellos valores de elementos data cuyo name es "motivo", por eso la siguiente consulta nos devuelve 4 filas. Es decir, de las cuatro filas en las que campo tiene valores, sólo dos de ellas tiene un elemento data con name igual a 'motivo', pero como xpath devuelve un array de valores, en las los dos últimas filas se devuelve un array vacio.

Consulta	Resultado
SELECT xpath('//data[@name=''motivo'']/@value',campo::xml) as motivos FROM tabla where campo is not null	motivos ---------- {tiempo} {hardware} {} {}

Para evitar lo anterior, podemos hacer:

Consulta	Resultado
SELECT xpath('//data[@name=''motivo'']/@value',campo::xml) as motivos FROM tabla where campo is not null and array_upper(xpath('//data[@name=''motivo'']',campo::xml),1) is not null	motivos ------------ {tiempo} {hardware}

La consulta anterior elimina aquellas filas con array vacío. No obstante, xpath() nos sigue devolviendo un array que tendremos que procesar posteriormente. Para que nos devuelva valores de tipo xml (u no un array) tendremos que tratar la respuesta de xpath como array y pedir el primer elemento del mismo. Así:

Consulta	Resultado
SELECT (xpath('//data[@name=''motivo'']/@value',campo::xml))[1] as motivos FROM tabla where campo is not null and array_upper(xpath('//data[@name=''motivo'']',campo::xml),1) is not null	motivos ----------- tiempo hardware

Obviamente, podemos usar la función xpath no sólo para seleccionar valores (uso en la select list de la sentencia SELECT) sino también en la cláusula WHERE, para filtrar filas. En este último caso, tendremos que realizar una conversión de tipo para poder realizar ciertas comparaciones (comparación de valores enteros, de fechas, etc...). La conversión a realizar debe ser un poco especial, ya que deberemos convertir de xml a varchar y de éste al tipo deseado. Por ejemplo:

SELECT (xpath('//data[@name=''cantidad'']/@value',campo::xml))[1]::varchar::int4
FROM step
where campo is not null
and array_upper(xpath('//data[@name=''cantidad'']',campo::xml),1) is not null
and (xpath('//data[@name=''cantidad'']/@value',campo::xml))[1]::varchar::int4 > 2

Con lo que conseguiríamos todas aquellas filas con xml que tengan un elemento data con nombre "cantidad" cuyo valor sea superior a 2.


Con esto, podemos usar un modelo más flexible (especialmente para valores de poca densidad) al modelo EAV y con consultas más asequibles.

Referencias:

• http://www.postgresql.org/docs/current/static/datatype-xml.html
• http://www.postgresql.org/docs/8.4/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
• XPath 1.0