Données XML dans la base de données PostgreSQL

J'ai un travail de mise en œuvre où je ne tout à l'intérieur de PostgreSQL sans autres bibliothèques.

Auxiliaire fonction d'analyse syntaxique

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;

Poignée plusieurs valeurs

Ci-dessus mise en œuvre ne gère pas les plusieurs attributs à un xpath. Voici un surchargé version de f_xml_extract_val(). Avec le 3e paramètre, vous pouvez choisir one (le premier), all ou dist (distinctes) de valeurs. Plusieurs valeurs sont regroupées pour une chaîne séparée par des virgules.

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'

Appel:

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

Partie principale

Nom de la table cible: tbl; prim. clé: 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;

Points importants:

• Cette mise en œuvre des contrôles sur une clé primaire si la ligne existe déjà et mises à jour dans ce cas. Seulement de nouvelles lignes sont insérées.

• - Je utiliser un transfert temporaire de la table d'accélérer la procédure.

• pg_read_file() a des restrictions. Je cite le manuel:

  Utilisation de ces fonctions est limité pour les superutilisateurs.

  Et:

  Que seuls les fichiers de la base de données du répertoire de cluster et le log_directory peut être consulté.

Donc vous devez mettre votre fichier source il y a - ou créer un lien symbolique vers votre fichier/répertoire.

Ou vous pouvez fournir le fichier via Java dans votre cas (je l'ai fait tout à l'intérieur de Postgres).

Ou vous pouvez importer les données dans la colonne 1 de 1 rangée d'une table temporaire, et à partir de là.

Ou vous pouvez utiliser lo_import comme démontré dans cette liés à la réponse sur le dba.SE.

• Testé avec Postgres 8.49.0 et 9.1.

• XML doit être bien formé.

Ce blog post par Scott Bailey m'a aidé.