{"id":197,"date":"2015-05-25T13:37:25","date_gmt":"2015-05-25T12:37:25","guid":{"rendered":"http:\/\/eppesuigoccas.homedns.org\/wordpress\/?p=197"},"modified":"2015-05-25T14:16:05","modified_gmt":"2015-05-25T13:16:05","slug":"xmlsql-e-postgresql-come-recuperare-due-dati-in-parallelo-in-una-sola-select","status":"publish","type":"post","link":"https:\/\/blog.sguazz.it\/index.php\/archives\/197","title":{"rendered":"XML\/SQL e PostgreSQL: come recuperare due tag in parallelo da un dato XML in una sola SELECT"},"content":{"rendered":"

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 \u00abdi frontiera\u00bb 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\u00a0 reperivano un singolo campo, ma non ci riusciva quando vi erano pi\u00f9 campi da prendere allo stesso tempo (per inserirli nello stesso record).<\/p>\n

Anche PostgreSQL \u00e8 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<\/a>. Vediamo come si pu\u00f2 affrontare questo problema con PostgreSQL.<\/p>\n

<\/p>\n

Quello che ci interessa \u00e8 estrarre i tag iocod<\/em> e iodesc<\/em> e inserirli in una tabella che ha gli stessi campi.<\/p>\n

Per semplicit\u00e0, anzich\u00e9 partire da un campo XML in una tabella, lo costruiamo al volo subito prima della SELECT con questa query:<\/p>\n

SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID=\"9.2\">\r\n<DataArea>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/IODESC><\/ITEM>\r\n<\/DataArea>\r\n<\/SyncITEMS>') AS colonna;<\/pre>\n
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.<\/p>\n
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 \u00e8 il numero di tag da estrarre. In questo modo posso prendere i due campi del tag i-esimo e metterli nello stesso record finale.<\/p>\n
Per farlo \u00e8 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:<\/p>\n
SELECT XPATH('count(\/\/ITEM)', (select colonna from tabella));<\/pre>\n
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:<\/p>\n
SELECT XPATH('count(\/\/ITEM[1])', (select colonna from tabella)) [1];<\/pre>\n
Fallisce con \u00aberrore di sintassi a o presso “[“\u00bb perch\u00e9 ci vogliono delle parentesi tonde aggiuntive, e poi faccio un succesivo cast per passare da varchar a intero:<\/p>\n
SELECT\u00a0 cast( c.c as int) AS N\r\nFROM (select (XPATH('count(\/\/ITEM[1])', (select colonna from tabella)))[1]::text AS c) AS c;<\/pre>\n
Come ultimo passaggio, una volta che si sa quanti record abbiamo, vediamo la SELECT che mette tutto assieme:<\/p>\n
WITH dati AS (SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID=\"23.562\">\r\n<DataArea>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART002<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 2<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART003<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 3<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART004<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 4<\/IODESC><\/ITEM>\r\n<\/DataArea>\r\n<\/SyncITEMS>') AS colonna),\r\n\u00a0contatore AS (SELECT cast( c.c as int) AS N\r\n   FROM (select (XPATH('count(\/\/ITEM)', (select colonna from dati)))[1]::text AS c) AS c),\r\n\u00a0serie AS ( SELECT generate_series POS FROM generate_series(1,(select n from contatore)))\r\nSELECT\r\n\u00a0XPATH('*\/ITEM[IOCOD and IODESC]['||pos||']\/IOCOD\/text()', dati.colonna)\u00a0 IOCOD,\r\n\u00a0XPATH('*\/ITEM[IOCOD and IODESC]['||pos||']\/IODESC\/text()', dati.colonna) IODESC\r\nFROM dati NATURAL JOIN serie;<\/pre>\n
Il cui risultato \u00e8:<\/p>\n
\u00a0 iocod\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 iodesc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n----------+-------------------------\r\n\u00a0{ART001} | {\"Articolo generico 1\"}\r\n\u00a0{ART002} | {\"Articolo generico 2\"}\r\n\u00a0{ART003} | {\"Articolo generico 3\"}\r\n\u00a0{ART004} | {\"Articolo generico 4\"}\r\n(4 righe)<\/pre>\n
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:<\/p>\n
WITH dati AS (SELECT XMLPARSE ( CONTENT '<SyncITEMS releaseID=\"23.562\">\r\n<DataArea>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART001<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 1<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART002<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 2<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART003<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 3<\/IODESC><\/ITEM>\r\n\u00a0 <ITEM>\r\n\u00a0\u00a0\u00a0 <IOCOD>ART004<\/IOCOD>\r\n\u00a0\u00a0\u00a0 <IODESC>Articolo generico 4<\/IODESC><\/ITEM>\r\n<\/DataArea>\r\n<\/SyncITEMS>') AS colonna),\r\n\u00a0contatore AS (SELECT cast( c.c as int) AS N\r\n   FROM (select (XPATH('count(\/\/ITEM)', (select colonna from dati)))[1]::text AS c) AS c),\r\n\u00a0serie AS ( SELECT generate_series POS FROM generate_series(1,(select n from contatore)))\r\nSELECT\r\n\u00a0(XPATH('*\/ITEM[IOCOD and IODESC]['||pos||']\/IOCOD\/text()', dati.colonna))[1]::text IOCOD,\r\n\u00a0(XPATH('*\/ITEM[IOCOD and IODESC]['||pos||']\/IODESC\/text()', dati.colonna))[1]::text IODESC\r\nFROM dati NATURAL JOIN serie;<\/pre>\n
Che restituisce<\/p>\n
\u00a0iocod\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 iodesc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n--------+---------------------\r\n\u00a0ART001 | Articolo generico 1\r\n\u00a0ART002 | Articolo generico 2\r\n\u00a0ART003 | Articolo generico 3\r\n\u00a0ART004 | Articolo generico 4\r\n(4 righe)<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 \u00abdi frontiera\u00bb e invocava questa procedura che doveva prendere […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,3],"tags":[13],"class_list":["post-197","post","type-post","status-publish","format-standard","hentry","category-sysadmin","category-computer","tag-itpug"],"_links":{"self":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/comments?post=197"}],"version-history":[{"count":7,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/197\/revisions"}],"predecessor-version":[{"id":204,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/posts\/197\/revisions\/204"}],"wp:attachment":[{"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/media?parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/categories?post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sguazz.it\/index.php\/wp-json\/wp\/v2\/tags?post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}