Dalla query al file XML con SQL Server 2005

SQL Server 2005 fornisce un supporto completo per l'XML: il DBMS, infatti, è in grado di gestire in modo nativo i dati in questo formato, che possono essere memorizzati direttamente in una tabella ed estratti utilizzando XQuery e XPath; inoltre, mette a disposizione una serie di operatori per produrre risultati in XML a partire da informazioni tabellari. In questo articolo ci occuperemo proprio di tale funzionalità. In particolare, analizzeremo i comandi

• FOR XML RAW
• FOR XML AUTO
• FOR XML PATH

Creiamo il database di prova

Per illustrare il funzionamento dei comandi della famiglia FOR XML, ci serviremo di un database di nome Segreteria, contenente una lista di studenti universitari con i relativi esami sostenuti. Lo schema della base di dati è il seguente:

Eseguiamo lo script Create.sql per creare il database e popolarlo con una serie di record. Supponiamo poi di interrogare l'archivio per recuperare la lista degli esami sostenuti da ciascuno studente:

SELECT s.Nome, s.Cognome, s.Matricola, e.NomeEsame, e.Data, e.Voto, e.Lode
FROM Studenti s
LEFT JOIN Esami e ON s.IDStudente = e.IDStudente;

Il risultato prodotto da questa query è il seguente:

Il nostro obiettivo è utilizzare i comandi FOR XML RAW, FOR XML AUTO e FOR XML PATH per trasformare questa visualizzazione in una rappresentazione dei dati in formato XML.

FOR XML RAW

L'operatore FOR XML RAW è il più semplice da utilizzare. Esso crea un elemento XML per ogni riga del set di risultati della query. Aggiungiamo FOR XML RAW all'interrogazione precedente:

SELECT s.Nome, s.Cognome, s.Matricola, e.NomeEsame, e.Data, e.Voto, e.Lode
FROM Studenti s
LEFT JOIN Esami e ON s.IDStudente = e.IDStudente
FOR XML RAW;

Dopo averla eseguita, nel riquadro dei risultati in SQL Server Management Studio apparirà un solo record, il cui contenuto è un link.

Se clicchiamo su di esso, si aprirà un'altra finestra con i dati rappresentati in XML.

<row Nome="Mauro" Cognome="Rossi" Matricola="543436" NomeEsame="Basi di dati" Data="2007-11-04T00:00:00" Voto="27" Lode="0" />
<row Nome="Mauro" Cognome="Rossi" Matricola="543436" NomeEsame="Ricerca operativa" Data="2008-01-07T00:00:00" Voto="30" Lode="1" />
<row Nome="Luca" Cognome="Verdi" Matricola="759050" NomeEsame="Calcolo numerico" Data="2008-02-06T00:00:00" Voto="28" Lode="0" />
<row Nome="Carlo" Cognome="Bianchi" Matricola="123986" NomeEsame="Sistemi intelligenti" Data="2007-11-29T00:00:00" Voto="24" Lode="0" />
<row Nome="Carlo" Cognome="Bianchi" Matricola="123986" NomeEsame="Reti" Data="2008-03-12T00:00:00" Voto="27" Lode="0" />
<row Nome="Carlo" Cognome="Bianchi" Matricola="123986" NomeEsame="Sistemi operativi" Data="2008-04-29T00:00:00" Voto="30" Lode="0" />
<row Nome="Francesco" Cognome="Sempronio" Matricola="765905" />

Analizziamo meglio quanto ottenuto. Anzitutto, il risultato non contempla un nodo root, quindi non è un documento XML ben formato, ma ad un cosiddetto "frammento". Per aggiungere un nodo root, si deve specificare il parametro ROOT dopo l'operatore FOR XML RAW, indicando facoltativamente il nome del nodo stesso (altrimenti sarà usato il valore root):

FOR XML RAW, ROOT('Esami')

Gli elementi restituiti, inoltre, sono chiamati row. Anche questa impostazione può essere modificata, specificando il nome da attribuire agli elementi subito dopo la parola chiave RAW:

FOR XML RAW('Studente')

Come è evidente, ogni colonna del set di risultati originale diventa un attributo dell'elemento XML corrispondente. Se, invece, si vuole creare un sottoelemento per ogni colonna, si deve usare la direttiva ELEMENTS; insieme a quest'ultima è possibile specificare anche l'opzione XSINIL, per mappare i valori di colonna NULL del set di risultati in un elemento con l'attributo xsi:nil="true".

FOR XML RAW('Studente'), ROOT('Esami'), ELEMENTS XSINIL;

Infine, è possibile rinominare gli attributi o i sottoelementi attraverso gli alias delle colonne nella SELECT. Mettendo insieme quanto detto finora, otteniamo complessivamente:

SELECT s.Nome, s.Cognome, s.Matricola, e.NomeEsame AS 'Esame', e.Data, e.Voto, e.Lode
FROM Studenti s
LEFT JOIN Esami e ON s.IDStudente = e.IDStudente
FOR XML RAW('Studente'), ROOT('Esami'), ELEMENTS XSINIL;

Che produce:

<Esami xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Studente>
    <Nome>Mauro</Nome>
    <Cognome>Rossi</Cognome>
    <Matricola>543436</Matricola>
    <Esame>Basi di dati</Esame>
    <Data>2007-11-04T00:00:00</Data>
    <Voto>27</Voto>
    <Lode>0</Lode>
  </Studente>
  <!-- Altri studenti -->
  <Studente>
    <Nome>Francesco</Nome>
    <Cognome>Sempronio</Cognome>
    <Matricola>765905</Matricola>
    <Esame xsi:nil="true" />
    <Data xsi:nil="true" />
    <Voto xsi:nil="true" />
    <Lode xsi:nil="true" />
  </Studente>
</Esami>

Utilizzando FOR XML RAW, tutte le colonne devono essere formattate nello stesso modo (ad esempio, non è consentito visualizzarne alcune come attributi e altre come sottoelementi). Inoltre, l'XML prodotto è tutto "allo stesso livello": non è possibile creare documenti XML con elementi annidati; se si ha necessità di creare strutture complesse, è necessario utilizzare query XML composte, come vedremo meglio più avanti.

FOR XML AUTO

Utilizzando la direttiva AUTO è possibile ottenere file XML con elementi nidificati: per ogni tabella specificata nella clausola FROM della query, viene creato un nuovo livello nella struttura XML; l'ordine di nidificazione dei dati è determinato dall'ordine delle colonne dichiarate nella SELECT.

Le considerazioni fatte in precedenza relativamente a ROOT, ELEMENTS e XSINIL sono valide anche in questo caso. Gli elementi XML possono essere rinominati utilizzando gli alias delle colonne e delle tabelle.

SELECT Nome, Cognome, Matricola, NomeEsame AS 'Materia', Data, Voto, Lode
FROM Studenti AS Studente
LEFT JOIN Esami AS Esame ON Studente.IDStudente = Esame.IDStudente
FOR XML AUTO, ROOT('Studenti');

L'output XML è il seguente:

<Studenti>
  <Studente Nome="Mauro" Cognome="Rossi" Matricola="543436">
    <Esame Materia="Basi di dati" Data="2007-11-04T00:00:00" Voto="27" Lode="0" />
    <Esame Materia="Ricerca operativa" Data="2008-01-07T00:00:00" Voto="30" Lode="1" />
  </Studente>
  <!-- Altri studenti -->
  <Studente Nome="Francesco" Cognome="Sempronio" Matricola="765905">
    <Esame />
  </Studente>
</Studenti>

Per ogni record restituito, il primo livello della struttura XML corrisponde alla tabella contenente la prima colonna dichiarata nella SELECT, il secondo livello è relativo alla tabella di cui fa parte la prima colonna della SELECT che non appartiene alla tabella precedente, e così via.

Nel nostro esempio, la prima colonna è Nome della tabella Studenti, quindi nell'XML viene creato l'elemento relativo; i campi successivi, Cognome e Matricola, fanno parte della stessa tabella, quindi sono aggiunti come attributi al tag corrente. Quando si arriva alla colonna NomeEsame, poiché essa appartiene ad un'altra tabella, SQL Server genera automaticamente un nuovo elemento XML, ponendolo al secondo livello della gerarchia; i campi Data, Voto e Lode, infine, diventano attributi del tag Esame.

Questo algoritmo viene ripetuto per ogni record restituito dalla query. Se nel documento XML così generato compaiono due o più elementi uguali (come nel caso di uno studente con più esami), essi vengono automaticamente fusi in uno solo, che diventa il padre di tutti i sottoelementi posseduti dagli elementi originali.

Come si può vedere dall'esempio, FOR XML AUTO consente una flessibilità maggiore rispetto a RAW, tuttavia, al pari di quest'ultimo, soffre di una limitazione, ovvero l'impossibilità di formattare separatamente le colonne.

FOR XML PATH

La direttiva FOR XML PATH permette di specificare la formattazione dei singoli elementi del file XML, consentendo quindi un grande di livello di personalizzazione dell'output. Con tale operatore, i nomi e gli alias di colonna sono gestiti come espressioni XPath che esprimono il modo in cui viene eseguito il mapping tra i valori e il codice XML.

Ogni espressione XPath specifica il tipo dell'elemento, ad esempio attributo, elemento o valore scalare, nonché il nome e la gerarchia del nodo che verrà generato in relazione all'elemento riga.

SELECT Matricola '@Matricola', Nome, Cognome, NomeEsame 'Esame/Materia', 
Data 'Esame/Data', Lode 'Esame/Voto/@Lode', Voto 'Esame/Voto'
FROM Studenti s
LEFT JOIN Esami e ON s.IDStudente = e.IDStudente
FOR XML PATH('Studente'), ROOT('Esami');

Il documento generato ha la seguente forma:

<Esami>
  <Studente Matricola="543436">
    <Nome>Mauro</Nome>
    <Cognome>Rossi</Cognome>
    <Esame>
      <Materia>Sistemi intelligenti</Materia>
      <Data>2007-11-29T00:00:00</Data>
      <Voto Lode="0">24</Voto>
    </Esame>
  </Studente>
  <Studente Matricola="543436">
    <Nome>Mauro</Nome>
    <Cognome>Rossi</Cognome>
    <Esame>
      <Materia>Ricerca operativa</Materia>
      <Data>2008-01-07T00:00:00</Data>
      <Voto Lode="1">30</Voto>
    </Esame>
  </Studente>
  <!-- Altri studenti -->
  <Studente Matricola="765905">
    <Nome>Francesco</Nome>
    <Cognome>Sempronio</Cognome>
  </Studente>
</Esami>

Al pari di XML RAW, è possibile indicare un valore dopo la parola chiave PATH per specificare il nome dell'elemento principale del file XML. È supportata anche la direttiva ROOT. Non è consentito, invece, utilizzare ELEMENTS, poiché con FOR XML PATH siamo noi ad indicare esplicitamente se mostrare una colonna come elemento oppure attributo.

Ad esempio, i campi Matricola e Lode diventano attributi, rispettivamente, del tag Studente e del sottoelemento Esame/Voto (nella query sono indicati con una @); gli altri campi, invece, sono mostrati come elementi XML. A questo proposito, è bene ricordare che gli attributi XML devono essere dichiarati nella SELECT prima degli elementi a cui appartengono, quindi l'ordine delle colonne è importante.

Le possibilità di formattazione offerte dalla direttiva FOR XML PATH sono molto numerose. Per approfondire l'argomento, si consiglia di consultare il sito di Microsoft TechNet.

Query XML annidate

Gli operatori che abbiamo mostrato finora consentono di configurare in maniera abbastanza flessibile l'output XML a partire dai dati contenuti in una tabella. Tuttavia, ci sono ancora dei casi in cui risultano insufficienti. Supponiamo, ad esempio, di voler ottenere un documento XML con le seguenti caratteristiche:

• per ogni studente compare un nodo, al cui interno si trova un sottoelemento che, a sua volta, contiene un sottoelemento per ogni esame sostenuto
• solo alcune colonne sono formattate come attributi

La direttiva FOR XML PATH, la più flessibile fra quelle analizzate, non consente di ottenere un risultato del genere, perché la formattazione specificata viene ripetuta per ogni riga del risultato, dunque è impossibile avere un solo elemento per ogni studente. Come possiamo creare un documento XML che rispetti entrambi i requisiti? Dobbiamo ricorrere a query XML annidate, utilizzando una speciale parola chiave, TYPE:

SELECT Matricola '@Matricola',
       Nome, Cognome,
       (SELECT e.NomeEsame AS 'Materia', 
               e.Data 'Data', e.Lode 'Voto/@Lode', e.Voto 'Voto'
        FROM Esami e
        WHERE s.IDStudente = e.IDStudente
        FOR XML PATH('Esame'), TYPE) AS Esami 
FROM Studenti s
FOR XML PATH('Studente'), ROOT('Studenti');

La SELECT più esterna è fatta sulla tabella degli studenti; per ognuno di essi, con la SELECT interna si recupera la lista degli esami sostenuti. Utilizzando in quest'ultima la parola chiave TYPE (nell'esempio, dopo la direttiva FOR XML PATH('Esame')), si dice a SQL Server di interpretare il risultato della sottoquery come un tipo di dato XML; senza di essa, infatti, esso sarebbe stato ricopiato nel nodo relativo come semplice testo. Il documento risultante, dunque, si presenta così:

<Studenti>
  <Studente Matricola="543436">
    <Nome>Mauro</Nome>
    <Cognome>Rossi</Cognome>
    <Esami>
      <Esame>
        <Materia>Sistemi intelligenti</Materia>
        <Data>2007-11-29T00:00:00</Data>
        <Voto Lode="0">24</Voto>
      </Esame>
      <Esame>
        <Materia>Ricerca operativa</Materia>
        <Data>2008-01-07T00:00:00</Data>
        <Voto Lode="1">30</Voto>
      </Esame>
    </Esami>
  </Studente>
  <!-- Altri studenti -->
  <Studente Matricola="765905">
    <Nome>Francesco</Nome>
    <Cognome>Sempronio</Cognome>
  </Studente>
</Studenti>

In questo esempio abbiamo utilizzato FOR XML PATH sia per la query principale sia per la sottoquery, ma si possono usare indistintamente tutte le direttive per l'XML analizzate nel corso dell'articolo (l'importante è non dimenticare la parola chiave TYPE).

Tutti i comandi T-SQL e le interrogazioni presentate sono disponibili nel file allegato.

Conclusioni

Abbiamo analizzato i principali operatori che SQL Server 2005 mette a disposizione per produrre documenti XML a come risultato di una SELECT e abbiamo visto come utilizzare query XML annidate per produrre documenti con formattazione complessa.

Quanto mostrato, però, è solo una piccola parte delle funzionalità di gestione dell'XML fornite da SQL Server 2005.