Chargement de fichiers XML dans PostgreSQL

[Phil]

Merci à Guillaume pour sa question :

"Est-il possible de charger des fichiers XML dans PostgreSQL ? En pièce jointe un fichier xml que je chargeais avec Oracle.

Dans cette exemple, les balises EtatCivil et Handicap peuvent être répétées.
Ce fichier va être traité 2 fois (en fait, autant de fois qu’il y a de nœud « répétable »).
La 1er consistera à récupérer les états civils et la 2ème les handicaps.
Si un employé a 2 états civils, la table « Etat Civil » aura 2 lignes correspondant aux 2 nationalités et avec en commun les infos concernant l’employé.

Exemple sur l’état civil avec Oracle 11.2 :

CREATE TABLE ETATCIVIL AS
SELECT
IMAT.codeEJ,
IMAT.codeMatricule,
ETATCIVIL.*
FROM XMLTable('ListeMatricules/Matricule'
PASSING xmltype( bfilename('"+dir+"','"+fichier+"'),nls_charset_id('WE8ISO8859P1') )
COLUMNS
codeEJ VARCHAR2(4000) PATH 'codeEJ',
codeMatricule VARCHAR2(4000) PATH 'codeMatricule',
ListeEtatCivil XMLTYPE PATH 'ListeEtatCivil'
) IMAT
INNER JOIN XMLTable('/ListeEtatCivil/EtatCivil'
PASSING IMAT.ListeEtatCivil
COLUMNS
dateDebutEtatCivil VARCHAR2(4000) PATH 'dateDebutEtatCivil',
dateFinEtatCivil VARCHAR2(4000) PATH 'dateFinEtatCivil',
codePaysNationalite VARCHAR2(4000) PATH 'codePaysNationalite',
libellePaysNationalite VARCHAR2(4000) PATH 'libellePaysNationalite'
) ETATCIVIL ON 1 = 1;"



Réponse :
Il est possible d'utiliser une syntaxe similaire avec PostgreSQL 10 et versions supérieures. Exemple :

Code : Tout sélectionner

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

CREATE TABLE ETATCIVIL AS
  SELECT IMAT.codeEJ, IMAT.codeMATRICULE, ETATCIVIL.*
  FROM 
  xmltable('//ListeMatricules/Matricule'
          PASSING (select (pg_read_file('sample.xml'))::xml as data) 
          COLUMNS  codeEJ text PATH 'codeEJ',
          codeMatricule text PATH 'codeMatricule',
          ListeEtatCivil XML PATH 'ListeEtatCivil' ) IMAT
  INNER JOIN        
   xmltable('EtatCivil'
          PASSING IMAT.ListeEtatCivil COLUMNS 
          dateDebutEtatCivil text PATH 'dateDebutEtatCivil',
          dateFinEtatCivil text PATH 'dateFinEtatCivil', 
          codePaysNationalite TEXT PATH 'codePaysNationalite',
          libellePaysNationalite TEXT PATH 'libellePaysNationalite') ETATCIVIL  ON (true);
SELECT 4
          
select * from ETATCIVIL;
 codeej | codematricule | datedebutetatcivil | datefinetatcivil | codepaysnationalite | libellepaysnationalite
--------+---------------+--------------------+------------------+---------------------+------------------------
 SAMPLE | TU-1          | 26/10/1911         | 31/12/1932       | 33                  | FRANCE
 SAMPLE | TU-1          | 01/01/1933         | 31/12/9999       | 32                  | BELGIQUE
 SAMPLE | TU-2          | 26/10/1911         | 31/12/9999       | 33                  | FRANCE
 SAMPLE | TU-3          | 26/10/1911         | 31/12/9999       | 33                  | FRANCE
(4 lignes)

pg_read_file est une fonction réservées aux superuser, s'il faut vraiment lire sans être superuser alors il faut l'encapsuler et la restreindre.
Exemple de fonction qui l'encapsule (à valider !!!) :

Code : Tout sélectionner

create table extensions.directory(dossier_relatif text, dossier_absolu text, utilisateur text);
CREATE TABLE

insert into extensions.directory(dossier_relatif, dossier_absolu, utilisateur) values('w/', '/var/lib/postgresql/10/main/w/', 'lambda');
INSERT 0 1

create or replace function extensions.restrict_read_file(fichier text) returns text 
language plpgsql SECURITY DEFINER
as $$
declare
chemin text;
begin
if fichier !~* '^[a-z0-9.]*$' or fichier like '%..%' then
	raise exception 'Nom du fichier incorrect';
end if;
select dossier_relatif from extensions.directory where session_user = utilisateur into chemin;
if chemin is null then
	raise exception 'Aucune directory pour %', session_user;
end if;
return pg_read_file(chemin||fichier,0,1000000000,false);
END
$$;
CREATE FUNCTION

grant execute on function ql_read_file(text) to lambda;
GRANT

commit;
COMMIT

[Phil]

Le comportement de xmltable a évolué avec PostgreSQL 11 pour correspondre au standard SQL (et accessoirement à ce que fait Oracle Database).
Dixit Markus Winand dans les release notes :

Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling functions (Markus Winand)
Per the SQL standard, relative paths start from the document node of the XML input document, not the root node as these functions previously did.

Avec le même fichier d'exemple cela donne donc :

Code : Tout sélectionner

select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.0 (Debian 11.0-1.pgdg+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-7) 8.2.0, 64-bit
(1 ligne)

CREATE TABLE ETATCIVIL AS
  SELECT IMAT.codeEJ, IMAT.codeMATRICULE, ETATCIVIL.*
  FROM
  xmltable('//ListeMatricules/Matricule'
          PASSING (select (pg_read_file('sample.xml'))::xml as data)
          COLUMNS  codeEJ text PATH 'codeEJ',
          codeMatricule text PATH 'codeMatricule',
          ListeEtatCivil XML PATH 'ListeEtatCivil' ) IMAT
  INNER JOIN
   xmltable('ListeEtatCivil/EtatCivil'
          PASSING IMAT.ListeEtatCivil COLUMNS
          dateDebutEtatCivil text PATH 'dateDebutEtatCivil',
          dateFinEtatCivil text PATH 'dateFinEtatCivil',
          codePaysNationalite TEXT PATH 'codePaysNationalite',
          libellePaysNationalite TEXT PATH 'libellePaysNationalite') ETATCIVIL  ON (true);
SELECT 4

select * from etatcivil;
 codeej | codematricule | datedebutetatcivil | datefinetatcivil | codepaysnationalite | libellepaysnationalite
--------+---------------+--------------------+------------------+---------------------+------------------------
 SAMPLE | TU-1          | 26/10/1911         | 31/12/1932       | 33                  | FRANCE
 SAMPLE | TU-1          | 01/01/1933         | 31/12/9999       | 32                  | BELGIQUE
 SAMPLE | TU-2          | 26/10/1911         | 31/12/9999       | 33                  | FRANCE
 SAMPLE | TU-3          | 26/10/1911         | 31/12/9999       | 33                  | FRANCE
(4 lignes)