Tempo fa mi fu chiesto di lavorare ad una procedura interna a DB2 che si occupava di importare alcuni dati: un sistema esterno generava un testo XML con parecchi record da inserire, metteva tutto il testo tramite connessione ODBC in un campo XML di una tabella «di frontiera» e invocava questa procedura che doveva prendere quei dati e inserirli in varie tabelle. Il punto sul quale il DBA locale si era bloccato era che riusciva a fare query XML che  reperivano un singolo campo, ma non ci riusciva quando vi erano più campi da prendere allo stesso tempo (per inserirli nello stesso record).

Anche PostgreSQL è in grado di gestire un campo XML e di estrarre delle parti di XML da quei campi. Questa estensione del linguaggio segue uno standard chiamato SQL/XML. Vediamo come si può affrontare questo problema con PostgreSQL.

Quello che ci interessa è estrarre i tag iocod e iodesc e inserirli in una tabella che ha gli stessi campi.

Per semplicità, anziché partire da un campo XML in una tabella, lo costruiamo al volo subito prima della SELECT con questa query:

SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID="9.2">
<DataArea>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1                 </IODESC></ITEM>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1                 </IODESC></ITEM>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1                 </IODESC></ITEM>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1                 </IODESC></ITEM>
</DataArea>
</SyncITEMS>') AS colonna;

La funzione XMLPARSE prende un varchar e lo trasforma in un oggetto di tipo XML. Ha delle varianti: quella qui usata quale prende in input direttamente il contentuto dell’XML.

Per avere tutti i record uno alla volta ho scelto di fare una join con una tabella che contiene tutti i numeri da 1 ad N, dove N è il numero di tag da estrarre. In questo modo posso prendere i due campi del tag i-esimo e metterli nello stesso record finale.

Per farlo è necessario avere il numero di oggetti di tipo ITEM contenuti in DataArea. Per questo utilizzo XPATH, che opera su un oggetto XML e ne estrae le informazioni richieste secondo una sintassi molto sofisticata:

SELECT XPATH('count(//ITEM)', (select colonna from tabella));

Il che restituisce un array di XML, del quale va preso l’unico elemento, il primo, va e convertito in intero passando dalla conversione intermedia in varchar. Per prendere il primo avrei usato la sintassi con le parentesi quadre, ma non funziona:

SELECT XPATH('count(//ITEM[1])', (select colonna from tabella)) [1];

Fallisce con «errore di sintassi a o presso “[“» perché ci vogliono delle parentesi tonde aggiuntive, e poi faccio un succesivo cast per passare da varchar a intero:

SELECT  cast( c.c as int) AS N
FROM (select (XPATH('count(//ITEM[1])', (select colonna from tabella)))[1]::text AS c) AS c;

Come ultimo passaggio, una volta che si sa quanti record abbiamo, vediamo la SELECT che mette tutto assieme:

WITH dati AS (SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID="23.562">
<DataArea>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART002</IOCOD>
    <IODESC>Articolo generico 2</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART003</IOCOD>
    <IODESC>Articolo generico 3</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART004</IOCOD>
    <IODESC>Articolo generico 4</IODESC></ITEM>
</DataArea>
</SyncITEMS>') AS colonna),
 contatore AS (SELECT cast( c.c as int) AS N
   FROM (select (XPATH('count(//ITEM)', (select colonna from dati)))[1]::text AS c) AS c),
 serie AS ( SELECT generate_series POS FROM generate_series(1,(select n from contatore)))
SELECT
 XPATH('*/ITEM[IOCOD and IODESC]['||pos||']/IOCOD/text()', dati.colonna)  IOCOD,
 XPATH('*/ITEM[IOCOD and IODESC]['||pos||']/IODESC/text()', dati.colonna) IODESC
FROM dati NATURAL JOIN serie;

Il cui risultato è:

  iocod   |         iodesc          
----------+-------------------------
 {ART001} | {"Articolo generico 1"}
 {ART002} | {"Articolo generico 2"}
 {ART003} | {"Articolo generico 3"}
 {ART004} | {"Articolo generico 4"}
(4 righe)

Come si vede anche in questo caso XPATH ha restituto un array di XML, quindi anche qui si deve estrarre il primo elemento e farne poi il cast a varchar:

WITH dati AS (SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID="23.562">
<DataArea>
  <ITEM>
    <IOCOD>ART001</IOCOD>
    <IODESC>Articolo generico 1</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART002</IOCOD>
    <IODESC>Articolo generico 2</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART003</IOCOD>
    <IODESC>Articolo generico 3</IODESC></ITEM>
  <ITEM>
    <IOCOD>ART004</IOCOD>
    <IODESC>Articolo generico 4</IODESC></ITEM>
</DataArea>
</SyncITEMS>') AS colonna),
 contatore AS (SELECT cast( c.c as int) AS N
   FROM (select (XPATH('count(//ITEM)', (select colonna from dati)))[1]::text AS c) AS c),
 serie AS ( SELECT generate_series POS FROM generate_series(1,(select n from contatore)))
SELECT
 (XPATH('*/ITEM[IOCOD and IODESC]['||pos||']/IOCOD/text()', dati.colonna))[1]::text IOCOD,
 (XPATH('*/ITEM[IOCOD and IODESC]['||pos||']/IODESC/text()', dati.colonna))[1]::text IODESC
FROM dati NATURAL JOIN serie;

Che restituisce

 iocod  |       iodesc        
--------+---------------------
 ART001 | Articolo generico 1
 ART002 | Articolo generico 2
 ART003 | Articolo generico 3
 ART004 | Articolo generico 4
(4 righe)