Datos XML a la base de datos PostgreSQL

¿Cuál sería la mejor manera de insertar datos XML (que obtengo de una página web) en la base de datos PostgreSQL?
Estoy usando Java y necesito un poco de ayuda para encontrar una buena manera de leer estos datos en la base de datos.

Postgres tiene (gracias a Daniel Lyons por señalarlo) compatibilidad con XML nativo que puede usar para almacenar su tabla. Sin embargo, si desea triturar manualmente sus datos XML, existen diferentes posibilidades para representar datos XML en una base de datos. La primera pregunta debería ser, si desea una solución muy genérica, que pueda almacenar cualquier documento XML o uno específico de su dominio (es decir, solo permite documentos XML de una cierta estructura). Dependiendo de eso, tendrá una representación universal muy flexible que, sin embargo, es más difícil de consultar (el SQL necesario será bastante complicado). Si tiene un enfoque más específico, las consultas serán más simples, pero deberá crear nuevas tablas o agregar nuevos atributos a los talbes existentes cada vez que desee almacenar otro tipo de documento o agregar un campo a un documento existente; por lo que cambiar el esquema será más difícil (que es una gran ventaja de XML). Esta presentación debe darte algunas ideas sobre las diferentes posibilidades.

Además, podría considerar cambiar a algún DB que admita Xquery, como DB2 . La capacidad de consultar de forma nativa con XQuery, un lenguaje dirigido al procesamiento de XML, simplificará mucho las cosas.

ACTUALIZACIÓN: dado su comentario, sus datos XML (a los que se ha vinculado ) son perfectamente relacionales. Se puede mapear 1: 1 a la siguiente tabla:

CREATE TABLE mynt ( ID SERIAL , myntnafn CHAR(3) , myntheiti Varchar(255) , kaupgengi Decimal(15,2) , midgengi Decimal(15,2) , solugengi Decimal(15,2) , dagsetning TimeStamp ) 

Entonces, cualquier etiqueta mynt sería un registro en la tabla y las mynt correspondientes a los atributos. Los tipos de datos que reuní de sus datos, podrían estar equivocados. El principal problema es, IMO, que no hay una clave primaria natural, así que agregué uno autogenerado.

Tengo una implementación en funcionamiento donde hago todo lo que está dentro de PostgreSQL sin bibliotecas adicionales.

Función de análisis auxiliar

 CREATE OR REPLACE FUNCTION f_xml_extract_val(text, xml) RETURNS text AS $func$ SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$' THEN (xpath($1, $2))[1] WHEN $1 ~* '/text()$' THEN (xpath($1, $2))[1] WHEN $1 LIKE '%/' THEN (xpath($1 || 'text()', $2))[1] ELSE (xpath($1 || '/text()', $2))[1] END; $func$ LANGUAGE sql IMMUTABLE STRICT; 

Manejar múltiples valores

La implementación anterior no maneja múltiples atributos en un xpath. Aquí hay una versión sobrecargada de f_xml_extract_val() para eso. Con el 3er parámetro, puede elegir one (el primero), all o dist (distintos) valores. Los valores múltiples se agregan a una cadena separada por comas.

 CREATE OR REPLACE FUNCTION f_xml_extract_val(_path text, _node xml, _mode text) RETURNS text AS $func$ DECLARE _xpath text := CASE WHEN $1 ~~ '%/' THEN $1 || 'text()' WHEN lower($1) ~~ '%/text()' THEN $1 WHEN $1 ~ '@\w+$' THEN $1 ELSE $1 || '/text()' END; BEGIN -- fetch one, all or distinct values CASE $3 WHEN 'one' THEN RETURN (xpath(_xpath, $2))[1]::text; WHEN 'all' THEN RETURN array_to_string(xpath(_xpath, $2), ', '); WHEN 'dist' THEN RETURN array_to_string(ARRAY( SELECT DISTINCT unnest(xpath(_xpath, $2))::text ORDER BY 1), ', '); ELSE RAISE EXCEPTION 'Invalid $3: >>%<<', $3; END CASE; END $func$ LANGUAGE plpgsql IMMUTABLE STRICT; COMMENT ON FUNCTION f_xml_extract_val(text, xml, text) IS ' # extract element of an xpath from XML document # Overloaded function to f_xml_extract_val(..) $3 .. mode is one of: one | all | dist' 

Llamada:

 SELECT f_xml_extract_val('//city', x, 'dist'); 

Parte principal

Nombre de la tabla de objectives: tbl ; remilgado. clave: id :

 CREATE OR REPLACE FUNCTION f_sync_from_xml() RETURNS boolean AS $func$ DECLARE datafile text := 'path/to/my_file.xml'; -- only relative path in db dir myxml xml := pg_read_file(datafile, 0, 100000000); -- arbitrary 100 MB max. BEGIN -- demonstrating 4 variants of how to fetch values for educational purposes CREATE TEMP TABLE tmp ON COMMIT DROP AS SELECT (xpath('//some_id/text()', x))[1]::text AS id -- id is unique ,f_xml_extract_val('//col1', x) AS col1 -- one value ,f_xml_extract_val('//col2/', x, 'all') AS col2 -- all values incl. dupes ,f_xml_extract_val('//col3/', x, 'dist') AS col3 -- distinct values FROM unnest(xpath('/xml/path/to/datum', myxml)) x; -- 1.) DELETE? -- 2.) UPDATE UPDATE tbl t SET ( col_1, col2, col3) = (i.col_1, i.col2, i.col3) FROM tmp i WHERE t.id = i.id AND (t.col_1, t.col2, t.col3) IS DISTINCT FROM (i.col_1, i.col2, i.col3); -- 3.) INSERT NEW INSERT INTO tbl SELECT i.* FROM tmp i WHERE NOT EXISTS (SELECT 1 FROM tbl WHERE id = i.id); END $func$ LANGUAGE plpgsql VOLATILE; 

Puntos importantes:

  • Esta implementación verifica en una clave primaria si la fila insertada ya existe y las actualizaciones en este caso. Solo se insertan nuevas filas.

  • Utilizo una tabla de etapas temporal para acelerar el procedimiento.

  • pg_read_file() tiene restricciones. Cito el manual :

    El uso de estas funciones está restringido a superusuarios.

    Y:

    Solo se puede acceder a los archivos dentro del directorio del clúster de la base de datos y al log_directory.

Entonces debe colocar su archivo de origen allí o crear un enlace simbólico a su archivo / directorio real.

O puede proporcionar el archivo a través de Java en su caso (lo hice todo dentro de Postgres).

O puede importar los datos en 1 columna de 1 fila de una tabla temporal y tomarlos desde allí.

O puede usar lo_import como se demuestra en esta respuesta relacionada en dba.SE.

  • Probado con Postgres 8.4 , 9.0 y 9.1 .

  • XML tiene que estar bien formado.

Esta publicación de blog de Scott Bailey me ayudó.

PostgreSQL tiene un tipo de datos XML . Hay muchas funciones XML específicas que puede usar para consultar y modificar los datos, como con xpath.

Desde el lado de Java, puede pretender que solo está trabajando con cadenas, pero sepa que los datos están bien formados a la salida y no le permitirán almacenar datos no bien formados.