Dados XML para o database PostgreSQL

Qual seria a melhor maneira de inserir dados XML (obtidos de uma página da Web) no database PostgreSQL?
Estou usando o Java e preciso de uma pequena ajuda para encontrar uma boa maneira de ler esses dados no database.

O Postgres tem (graças a Daniel Lyons por apontá-lo) suporte XML nativo que você pode usar para armazenar sua tabela. Se, no entanto, você quiser destruir seus dados XML manualmente, existem diferentes possibilidades para representar dados XML em um database. A primeira questão deve ser, se você quiser uma solução muito genérica, que será capaz de armazenar qualquer documento XML ou um que é específico para o seu domínio (ou seja, só permite documentos XML de uma determinada estrutura). Dependendo disso, você terá uma representação universal muito flexível que, no entanto, é mais difícil de consultar (o SQL necessário será bastante complicado). Se você tiver uma abordagem mais específica, as consultas serão mais simples, mas você precisará criar novas tabelas ou adicionar novos atributos a talbes existentes toda vez que quiser armazenar outro tipo de documento ou adicionar um campo a um documento existente; então mudar o esquema será mais difícil (o que é uma grande vantagem do XML). Esta apresentação deve dar algumas idéias sobre as diferentes possibilidades.

Além disso, você pode considerar mudar para algum database que suporte Xquery, como o DB2 . A capacidade de consultar de forma nativa usando XQuery, uma linguagem voltada para o processamento de XML, simplificará muito as coisas.

ATUALIZAÇÃO: Dado seu comentário, seus dados XML (aos quais você se conectou ) são perfeitamente relacionais. Pode ser mapeado de 1: 1 para a seguinte tabela:

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 ) 

Então, qualquer tag mynt seria um registro na tabela e as sub-tags correspondentes aos atributos. Os tipos de dados que recolhi dos seus dados podem estar errados. O principal problema é, IMO, que não há chave primária natural, então adicionei uma chave autogerada.

Eu tenho uma implementação de trabalho onde faço tudo dentro do PostgreSQL sem bibliotecas adicionais.

Função de análise 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; 

Lidar com vários valores

A implementação acima não manipula vários atributos em um xpath. Aqui está uma versão sobrecarregada de f_xml_extract_val() para isso. Com o 3º parâmetro você pode escolher one (o primeiro), all ou dist (distintos) valores. Vários valores são agregados a uma string separada por vírgulas.

 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' 

Ligar:

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

Parte principal

Nome da tabela de destino: tbl ; prim. chave: 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; 

Pontos importantes:

  • Essa implementação verifica uma chave primária se a linha inserida já existir e for atualizada nesse caso. Somente novas linhas são inseridas.

  • Eu uso uma tabela temporária para acelerar o procedimento.

  • pg_read_file() tem restrições para isso. Eu cito o manual :

    O uso dessas funções é restrito a superusuários.

    E:

    Somente arquivos no diretório de cluster de database e no log_directory podem ser acessados.

Então você tem que colocar seu arquivo de origem lá - ou criar um link simbólico para o seu arquivo / diretório atual.

Ou você pode fornecer o arquivo via Java no seu caso (eu fiz tudo dentro do Postgres).

Ou você pode importar os dados em uma coluna de 1 linha de uma tabela temporária e extraí-la de lá.

Ou você pode usar o lo_import como demonstrado nesta resposta relacionada no dba.SE.

  • Testado com Postgres 8.4 , 9.0 e 9.1 .

  • XML tem que ser bem formado.

Este post do blog de Scott Bailey me ajudou.

O PostgreSQL possui um tipo de dados XML . Há várias funções específicas de XML que você pode usar para consultar e modificar os dados, como o xpath.

Do lado do Java, você pode fingir que está apenas trabalhando com strings, mas saiba que os dados são bem formados ao sair e não permitirão que você armazene dados não bem formados.