Ultimo aggiornamento: 29-Ago-2019

29 Luglio 2018MarkOne

XML fa rima con SQL

Dalla versione IBM i 7.1 l’integrazione tra XML e DB2 è completa. Il prodotto DB2 XML extender (5761DE1 a pagamento) presente nelle precedenti release è sostituito dalle funzionalità presenti nel sistema operativo release 7.1.

In breve le principali caratteristiche:

  • registrazione nel DB2 di XML Schemas
  • immagazzinamento di documenti XML in colonne di tabelle relazionali con validazione a fronte degli XML Schemas registrati
  • generazione di documenti XML da tabelle relazionali (SQL XML Publishing Functions)
  • inserimento righe di tabelle relazionali a partire da dati ricavati da documenti XML (Annotated XML Document Decomposition)
  • Possibilità di query sui dati di documenti XML immagazzinati in colonne di tabelle relazionali via OmniFind Text Search (prodotto OMF)

In questo articolo parleremo delle SQL XML Publishing Functions ovvero come ottenere un file XML well-formed solo tramite le funzioni SQL messe a disposizione nel DB2.

Per giungere al risultato finale, ossia un documento XML well-formed, è necessario combinare l’utilizzo di diverse funzioni SQL. Operazione non del tutto intuitiva se ci si limita a leggere il manuale del reference SQL dove per ogni funzione viene descritta l’operatività in dettaglio.

In questo articolo perciò preferisco adottare un approccio “per esempi”, partendo da un semplicissimo file XML e via via aggiungendo complessità alla struttura.

In allegato trovate i file XML ottenuti, gli script SQL e il programma RPG illustrati negli esempi.

Indice

Funzioni
Esempi: 1 – XML semplice 2 – XML impiegato 3 – XML elenco impiegati 4 – XML elenco impiegati e attività 5 – XML con allegato
Scrivere con RPG XML su IFS

Riepilogo SQL XML Publishing Functions

Prima di cominciare con gli esempi elenchiamo le funzioni SQL più importanti.

Funzioni di aggregazione

XMLAGG: restituisce un XML che è un aggregazione di ogni valore non nullo in un set di valori XML
XMLGROUP: restituisce un elemento XML complesso: ogni record della tabella è mappato con un sottoelemento dell’elemento principale ed ogni colonna è a sua volta un sottoelemento dell’elemento che rappresenta il record

Funzioni scalari

XMLATTRIBUTES: restituisce una porzione XML di tipo attributo
XMLCOMMENT: restituisce un valore XML di tipo commento (p.es. <!– … –>)
XMLCONCAT: restituisce la concatenazione degli elementi XML in input
XMLDOCUMENT: restituisce un valore XML che è un documento XML well-formed. N.B. ogni valore XML che si desidera memorizzare in una tabella del DB2 deve essere un documento XML.
XMLELEMENT: restituisce un valore XML che rappresenta un elemento
XMLFOREST: restituisce un valore XML che è una sequenza di elementi XML
XMLPI: restituisce un valore XML di “processing instructions” (p.es. <? … ?>)
XMLROW: restituisce una sequenza di elementi XML
XMLSERIALIZE: restituisce un documento XML well-formed comprensivo di intestazione

Dichiarazioni

XMLNAMESPACES: restituisce la dichiarazione del namespace. Può essere usata nella funzione XMLELEMENT o XMLFOREST

Esempi


Gli esempi seguenti sono basati sulle tabelle del database di esempio fornito da IBM. Per creare il database di esempio eseguire la procedura:

call CREATE_SQL_SAMPLE('DB2SAMPLE');

Quindi l’istruzione seguente per impostarlo come schema predefinito:

set current schema = 'DB2SAMPLE';

Esempio n. 1


Nel primo esempio creiamo un semplicissimo file XML composto da un solo tag. Ovvero il risultato di una select su una tabella estraendo 1 solo record e 1 sola colonna.

L’XML che desideriamo creare ha un solo tag lastname. Lo otteniamo utilizzando la funzione XMLELEMENT (porzione evidenziata in verde). La funzione XMLELEMENT definisce nel primo parametro il nome del tag (N.B. è bene ricordarsi che i tag sono case-sensitive) e nel secondo il suo valore.

Il contenuto dell’XML è completo. Rimane solo da utilizzare la funzione XMLDOCUMENT per trasformare la stringa di caratteri in un “documento” XML (porzione evidenziata in marrone). Ed infine con la funzione XMLSERIALIZE (evidenziata in violetto) aggiungiamo l’intestazione del file XML ottenendo così un documento “well-formed”.

Da notare che la funzione XMLSERIALIZE definisce il tipo di dati restituito (nell’esempio un CLOB di 500 byte). Bisogna porre attenzione alla dimensione definita affinché sia sufficientemente “grande” da contenere tutta la stringa del documento XML.

Come potete vedere dall’esempio anche solo per creare un semplicissimo file XML è necessario utilizzare 3 funzioni XML nidificate.

Download xml esempio 1

Esempio n. 2


Nel secondo esempio creiamo un file XML leggermente più complesso estraendo diverse colonne di 1 solo record per ottenere l’anagrafica di un impiegato.

L’XML di questo esempio contiene dei tag (p.es. firstname e lastname) nidificati nel tag di livello superiore name.

Si può ottenere il tag “complesso” name utilizzando la funzione XMLROW che elenca nei suoi argomenti i vari tag nidificati all’interno di name; il tag name è definito tramite la keyword option row.

Il tag employee in questo esempio ha due attributi id e job. Li possiamo ottenere con la funzione XMLATTRIBUTES (parte evidenziata in rosso).

A questo punto abbiamo ottenuto gli attributi del tag employee con XMLATTRIBUTES, i tag complessi name e contract con XMLROW e il tag semplice department con la già nota XMLELEMENT.

Tutti questi elementi vanno nidificati all’interno del tag principale employee. Quindi la funzione XMLELEMENT più esterna (parte evidenziata in giallo) definisce un elemento employee il cui contenuto è la sequenza degli elementi ottenuti in precedenza.

Come negli esempi precedenti una volta ottenuta la stringa corretta con i dati XML necessari nidifichiamo il tutto nelle funzioni XMLDOCUMENT e XMLSERIALIZE per ottenere un documento XML well-formed.
Download xml esempio 2

Esempio n. 3


Nel terzo esempio desideriamo ottenere un file XML simile al secondo esempio, ma estraendo l’anagrafica di più di un impiegato. Quindi nel file XML al di sotto del tag principale employees ci saranno tante ripetizioni del tag employee tanti quanti sono i record letti dalla tabella.

In questo esempio desideriamo ottenere le stesse informazioni come nell’esempio 2, non più per un singolo impiegato, bensì per un elenco di impiegati. Ovverosia il tag employee sarà ripetuto n volte (tanti quanti sono i record restituiti dalla select) all’interno del tag principale employees (ricordiamoci che un documento XML well-formed ha sempre bisogno di un solo root element).

A questo punto si pone un problema: finora le select esaminate restituivano un solo record dalla tabella letta. In questo esempio invece i record restituiti sarebbero due; ma noi desideriamo creare un solo file XML con il contenuto di entrambi i record.

Per fare ciò – dopo aver ottenuto il tag employee come in precedenza (parte evidenziata in giallo) – è necessario utilizzare una funzione XML di aggregazione XMLAGG (parte evidenziata in fucsia). Una funzione di aggregazione senza nessuna clausola group by implica che la select debba restituire 1 solo record. Questo record nel nostro caso contiene la concatenazione dei vari tag employee.

Aggregare non è sufficiente, perché il risultato aggregato deve essere contenuto nel tag principale employees. Non resta quindi che utilizzare la già nota funziona XMLELEMENT per crearlo.
Download XML esempio 3

Esempio n. 4


Nel quarto esempio desideriamo creare un file XML estraendo i dati da più di una tabella del DB2. Ovvero l’elenco degli impiegati con i loro dati anagrafici e per ogni impiegato le attività svolte nel progetto.

Il quarto esempio aggiunge al file XML già ottenuto nell’esempio n. 3 un tag complesso projects. Questo tag contiene per ogni impiegato un elenco (quindi uno o più record) delle attività svolte nei vari progetti.

In sintesi dobbiamo questa volta estrarre uno o più record dalla tabella impiegati e per ciascuno di questi record abbiamo bisogno di estrarre uno o più record dalla tabella delle attività sui progetti; ovverosia un join tra tabelle che deve generare un solo file XML.

Il metodo che preferisco adottare in questi casi è ottenere le varie porzioni del file XML e poi riunirle insieme.

Tutta la struttura dei dati degli impiegati employees l’abbiamo ottenuta nell’esempio 3.

Creiamo con un’altra istruzione select la struttura del nuovo tag projects. Siccome questo tag sarà nidificato all’interno di un altro tag XML non è necessario che questa sub-select crei un documento XML well-formed: è sufficiente che ci restituisca una stringa con il contenuto del tag projects correttamente formattato.

Procediamo creando i 3 tag ProjectID, Activity e hours con la funzione XMLCONCAT (avremmo potuto usare la funzione XMLROW come in precedenza, ma colgo l’occasione per illustrare anche un metodo alternativo). La funzione XMLCONCAT semplicemente concatena i suoi argomenti, che in questo caso sono 3 funzioni XMLELEMENT generanti i 3 tag che ci servono. Il risultato della funzione XMLCONCAT è a sua volta argomento della funzione XMLELEMENT che crea il tag project. Il tutto viene aggregato con la funzione XMLAGG. Questo risultato parziale ottenuto dalla sub-select è l’argomento della funzione XMLELEMENT che genera il tag projects. A questo punto inserisco nell’istruzione dell’esempio 3 questa porzione di codice come ulteriore argomento della funzione XMLELEMENT (evidenziata in giallo) che si occupa di creare il tag employee.
Download XML esempio 4

Esempio n. 5


Nel quinto esempio creiamo un documento XML del tutto simile all’esempio n. 2. Però aggiungiamo il tag PDF_allegato che è destinato a contenere un file pdf con il curriculum vitae dell’impiegato. Il file pdf da allegare risiede in una cartella di IFS.

    -- creazione di un file XML di un impiegato con allegato un file pdf (esempio 5)
select xmlserialize(
        xmldocument(
         xmlelement(name "employee", xmlattributes(EMPNO as "id", trim(JOB) as "job"),
          xmlrow(FIRSTNME as "firstname",
                 LASTNAME as "lastname"
                 option row "name"
          ),
          xmlelement(name "department", WORKDEPT),
          xmlrow(SALARY as "salary",
                 BONUS as "bonus",
                 HIREDATE as "hiredate"
                 option row "contract"
          ),
          xmlelement(name "PDF_allegato", blob(get_blob_from_file('/home/Curriculum vitae Christine.pdf'), 500000) option xmlbinary using base64)
         )
        ) as clob(500000) including xmldeclaration) as "XML output"
  from EMPLOYEE
  where EMPNO = '000010';

La funzione get_blob_from_file restituisce un puntatore al file binario (in questo esempio il file /home/Curriculum vitae Christine.pdf).

La funzione blob restituisce (in formato binario) il contenuto del puntatore al file.

Nella funzione xmlelement si aggiungono le keyword option xmlbinary using base64 che effettuano la codifica in base64 dei dati binari restituiti dalla funzione blob (ricordiamo che se si desidera inserire all’interno di un documento XML un qualsiasi file binario deve essere codificato in base64).

E alla fine il file dove è?


In tutti questi esempi abbiamo ottenuto tramite le SQL XML Publishing Functions un documento XML come risultato di una istruzione SQL.

Ebbene e se ora volessimo scrivere questo risultato in un file su IFS, proprio per avere nelle nostre mani un file XML concreto?

Un metodo molto semplice che richiede veramente pochissime righe di codice RPG è illustrato nel programma di esempio allegato XMLTOIFS1.

La facilità risiede tutta nello sfruttare una variabile di tipo SQL XML_CLOB_FILE. Dichiarando questo tipo di variabile in un programma RPG IV con embedded SQL si definisce in realtà una DS i cui sottocampi definiscono 3 proprietà del file:

– percorso e nome del file su IFS
– lunghezza di tale percorso + nome
– una costante che indica se creare il file, sovrascriverlo o aggiungere contenuto al file già esistente

       dcl-s xml_file sqltype(XML_CLOB_FILE);
[...]
       xml_file_name = %trim(xml_path) + %trim(xml_name);
       xml_file_nl = %len(%trim(xml_file_name));
       xml_file_fo = SQFOVR;

Valorizzate queste 3 variabili all’interno del programma RPG, tutto il resto che concerne la gestione del file non è una nostra preoccupazione.

Non resta quindi che eseguire nel programma RPG una delle istruzioni SQL di uno degli esempi precedenti (omettendo soltanto la funzione XMLSERIALIZE che non è necessaria in questo caso) aggiungendo la clausola into per porre il risultato nella variabile di tipo XML_CLOB_FILE.

exec sql
  select
    xmldocument(
    [...]
    ) into :xml_file
   from EMPLOYEE
   where EMPNO = '000010';

Seton LR.

Finito! Il file XML è servito su IFS.

Download sorgente RPG
Download script SQL

Bibliografia