Solución:
Tengo una implementación de trabajo donde lo hago todo 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;
Resolver múltiple 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 tercer parámetro puede elegir one (el primero), all o dist valores (distintos). Se agregan varios valores 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;
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'
Llama:
SELECT f_xml_extract_val('//city', x, 'dist');
Parte principal
Nombre de la tabla de destino: tbl; remilgado. llave: 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
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;
Notas importantes
-
Esta implementación verifica una clave primaria si la fila insertada ya existe y actualizaciones en este caso. Solo se insertan filas nuevas.
-
Utilizo una tabla de preparación temporal para acelerar el procedimiento.
-
Probado con Postgres 8.4, 9.0 y 9.1.
-
XML debe estar bien formado.
-
pg_read_file()tiene restricciones. El manual:El uso de estas funciones está restringido a superusuarios.
Y:
Solo los archivos dentro del directorio del clúster de la base de datos y el
log_directorypuede ser accesible.
Por lo tanto, debe colocar su archivo fuente 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 puedes usar lo_import como se demuestra en esta respuesta relacionada en dba.SE.
- SQL para leer XML desde un archivo a la base de datos PostgreSQL
Esta publicación de blog de Scott Bailey me ayudó.
Postgres tiene (gracias a Daniel Lyons por señalarlo) soporte XML nativo que puede usar para almacenar su tabla. Sin embargo, si desea triturar sus datos XML manualmente, 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 que sea específico de su dominio (es decir, solo permite documentos XML de 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 de las principales ventajas de XML). Esta presentación debería darle algunas ideas sobre las diferentes posibilidades.
Además, podría considerar cambiar a alguna base de datos que admita Xquery, como DB2. La capacidad de realizar consultas de forma nativa mediante XQuery, un lenguaje destinado al procesamiento de XML, simplificará mucho las cosas.
ACTUALIZACIÓN: Dado su comentario, sus datos XML (a los que se vinculó) son perfectamente relacionales. Se puede asignar 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
)
Así que cualquiera mynt La etiqueta sería un registro en la tabla y las subetiquetas correspondientes serían los atributos. Los tipos de datos que recopilé de sus datos pueden ser incorrectos. El principal problema es, en mi opinión, que no hay una clave primaria natural, así que agregué una autogenerada.
PostgreSQL tiene un tipo de datos XML. Hay muchas funciones específicas de XML que puede utilizar para consultar y modificar los datos, como con xpath.
Desde el lado de Java, puede fingir que solo está trabajando con cadenas, pero sepa que los datos están bien formados al salir y no le permitirán almacenar datos que no estén bien formados.