In passato la gestione dei dati
XML in
SQL Server è sempre stata piuttosto "artificiosa". Nel
giugno 2005 postai una soluzione basata sull'utility
TextCopy.exe che pur raggiungendo lo scopo, risultava piuttosto scomoda e limitata nell'utilizzo.
Con questo articolo vorrei illustrare brevemente le novità introdotte con
SQL Server 2005 per quanto concerne l'importazione di un file
XML e la trasformazione in forma relazionale dei dati in esso contenuti.
Introduzione
SQL Server 2005 introduce il data type
XML per la memorizzazione di documenti e frammenti (fragments) XML in una tabella, variabile o parametro. Il data type
XML comporta diversi vantaggi rispetto all'utilizzo di data type stringa come text o varchar, inclusa la possibilità di utilizzare l'Extensible Query Language (XQuery) per leggere o modificare elementi o attributi specifici del documento
XML.
Cos'è il Data Type XML?
Il data type
XML è un data type nativo per l'archiviazione di documenti o fragments
XML in colonne, variabili locali o parametri per una dimensione massima di 2 gigabytes (GB).
La possibilità di memorizzare nativamente dati
XML in un database relazionale comporta tutta una serie di vantaggi per gli sviluppatori, tra i quali:
- La memorizzazione di dati strutturati e semi-strutturati in un unico posto rende molto più semplice la loro gestione;
- E' possibile definire contenuti variabili all'interno di un modello relazionale;
- Ora è possibile scegliere il data model più opportuno in base ai requisiti applicativi, sicuri del fatto di appoggiarsi ad un data storage e ad un ambiente di interrogazione altamente ottimizzati.
In
SQL Server 2005 il data type
XML memorizza l'InfoSet di un documento
XML in un formato interno altamente ottimizzato paragonabile al documento
XML originale ad eccezione degli spazi non significativi, all'ordine degli attributi, ai prefissi dei namespace e alla dichiarazione
XML che non sono mantenuti.
SQL Server 2005 fornisce le seguenti funzionalità relative al data type
XML:
Indicizzazione XML. Le cononne definite come xml possono essere indicizzate utilizzando gli indici XML e full-text. Questo può incrementare significamente le prestazioni delle query che recuperano dati XML.
Metodi XQuery di lettura. Il data type xml implementa i metodi
query, value ed
exists. Tali metodi possono essere utilizzati per interrogare i dati XML tramite una espressione
XQuery.
Metodi XQuery di modifica. Il data type xml implementa il metodo modify che utilizza una estensione delle specifiche XQuery per eseguire aggiornamenti ai dati XML.
XML tipizzato. I dati XML associati ad uno schema XML prendono il nome di XML tipizzato (Typed XML). Lo schema definisce gli elementi e gli attriburi che sono validi in un documento XML di questo tipo e specifica il loro namespace. Quando il data type xml è utilizzato per memorizzare XML tipizzato, SQL Server valida l'XML mediante lo schema ed ottimizza la memorizzazione interna ai dati assegnando i data type SQL Server appropriati ai singoli attributi ed elementi in base ai data type XML definiti nello schema.
Cos'è XQuery?
XQuery è utilizzata per interrogare dati XML. La sintassi XQuery include ed estende le espressioni XPath 2.0 e permette di eseguire query complesse su dati XML. Il data type xml in SQL Server 2005 fornisce metodi attaverso i quali i dati xml possono essere restituiti o modificati attraverso una espressione XQuery.
Il supporto XQuery in SQL Server 2005 è basato sul working draft delle specifiche del linguaggio XQuery 1.0 del W3C (disponibili al seguente
link ), e di conseguenza potranno esserci alcune piccole incompatibilità quando saranno rilasciate le specifiche definitive.
Una query XQuery consiste di due sezioni principali: un prologo opzionale nel quale sono dichiarati i namespace ed importati gli schema, ed il corpo vero e proprio costituito da espressioni XQuery per specificare i dati che si vogliono recuperare. L'espressione XQuery può essere un semplice percorso che descrive i nodi XML da recuperare oppure una espressione complessa che genera un risultato XML.
Un percorso XQuery è basato sul linguaggio XPath e descrive la posizione di un nodo in un documento XML. I percorsi possono essere assoluti (descrivendo la posizione di un nodo attraversando l'intero albero partendo dall'elemento di root) oppure relativi (descrivendo la posizione di un nodo relativamente ad un nodo precedentemente identificato).
La seguente tabella riassume alcuni esempi di semplici percorsi XQuery:
Le specifiche del linguaggio XQuery includono i comandi
for, let, order by, where e return, noti comunemente con l’acronimo FLOWR (pronunciato "flower"). SQL Server 2005 supporta i comandi
for, where e return, descritti nella seguente tabella.
Il seguente esempio mostra una espressione XQuery che include le keywords
for, where e return.
for $i in /InvoiceList/Invoice
where count($i/Items/Item) > 1
return $i
Questo esempio restituisce ogni elemento
Invoice che include più di un elemento
Item nel suo elemento figlio
Items.
Se l’XML che si intende interrogare contiene un namespace, l’
XQuery può includere una dichiarazione del namespace nel prologo della query utilizzando la seguente sintassi.
xml.method('declare default element namespace "http://namespace";method body')
Per maggiori informazioni sull'utilizzo dei namespace con
XQuery consultare il paragrafo
XQuery Basics sui
SQL Server Books Online.
Il problema
Dopo questa breve introduzione sul data type
XML e sulle espressioni
XQuery veniamo al problema esposto da Simone.
Supponiamo di avere il file
C:\Orders.xml con la seguente struttura.
<?xml version="1.0" ?>
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
Lo scopo che ci prefiggiamo è quello di leggere questo file, trasformare la sua struttura gerarchica in una relazionale e popolare la tabella
dbo.Orders con le righe risultanti.
CREATE TABLE dbo.Orders(
OrderID int NOT NULL IDENTITY,
CustomerID char(4) NOT NULL,
ContactName varchar(6) NOT NULL,
CompanyName varchar(10) NOT NULL,
OrderDate datetime NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(OrderID)
);
Lanciamo
SQL Server Management Studio, connettiamoci alla istanza locale, selezioniamo il tempdb e definiamo la tabella
dbo.Orders.
Il primo problema che ci troviamo ad affrontare riguarda la lettura del file
C:\Orders.xml. Ogni volta che abbiamo la necessità di caricare dei dati
XML memorizzati su disco in una variabile o colonna con data type xml possiamo avvalerci del
BULK rowset provider e della funzione
OPENROWSET, specificando l'opzione
SINGLE_CLOB per leggere l’intero file in una colonna con data type
varchar(max) di una singola riga.
A questo punto possiamo dichiarare una variabile con data type
XML e valorizzarla con l'intero file
XML. Durante l’assegnazione della variabile, avverrà un cast implicito da
varchar(max) a
XML.
DECLARE @xmlDoc xml;
/* Carico l'intero file XML in una variabile XML */
SET @xmlDoc = (
SELECT Q.BulkColumn
FROM OPENROWSET(BULK 'C:\Orders.xml', SINGLE_CLOB) AS Q
);
Interrogando il contenuto della variabile
@xmlDoc otterremo il risultato rappresentato in figura.
Ora ci ritroviamo una variabile contenente l’intero documento
XML. Il prossimo passo consiste nel trasformare la struttura gerarchica in relazionale. Come possiamo eseguire questo lavoro?
Possiamo utilizzare due tecniche: la funzione
OPENXML oppure i metodi nodes e value del data type
XML.
Introduzione all’elaborazione dei dati XML
Processare dei dati
XML come un rowset comporta 5 passi:
1. Ricevere un documento XML. Quando una applicazione riceve un documento
XML, può processarlo utilizzando codice
Transact-SQL. Ad esempio quando un fornitore riceve un ordine
XML da un rivenditore, il fornitore inserisce l’ordine in un database SQL Server. Normalmente il codice
Transact-SQL per processare i dati
XML è implementato sottoforma di stored procedure, e la stringa
XML è passata come un parametro.
2. Generare una rappresentazione interna ad albero. La stored procedure
sp_xml_preparedocument permette di analizzare il documento XML e trasformarlo una struttura ad albero residente in memoria prima di processare il documento. L'albero è concettualmente simile alla rappresentazione
Document Object Model (DOM) di un documento XML. Per la generazione interna dell’albero è possibile utilizzare solo documenti
XML validi e well-formed.
3. Recuperare il rowset dall'albero. La funzione
OPENXML genera un rowset in memoria dai dati dell’albero. Utilizzando una query
XPath è possibile specificare i nodi nell'albero da restituire nel rowset.
4. Processare i dati dal rowset. Il rowset generato dalla funzione
OPENXML è esattamente analogo a qualunque altro rowset ottenuto ad esempio con un comando di SELECT. E'possibile selezionare, aggiornare o eliminare i dati ricorrendo ai comandi
Transact-SQL. La funzione
OPENXML viene normalmente utilizzata per memorizzare permanentemente i dati restituiti in una o più tabelle di un database. Per esempio, un ordine
XML ricevuto da un fornitore può contenere i dati che devono essere inseriti nelle tabelle
SalesOrderHeader e
SalesOrderDetail.
Distruggere l'albero interno quando non è più richiesto. Dato che la struttura dell’albero è mantenuta in memoria è opportuno utilizzare la stored procedure
sp_xml_removedocument per liberare la memoria quando l'albero non è più richiesto.
Stored procedure per la gestione delle strutture ad albero residenti in memoria
Prima di poter processare un documento
XML utilizzando i comandi
Transact-SQL, occorre analizzarlo trasformandolo in una struttura ad albero residente in memoria.
La stored procedure
sp_xml_preparedocument analizza un documento XML e genera una rappresentazione interna ad albero. La seguente tabella descrive i parametri della stored procedure di sistema
sp_xml_preparedocument.
SQL Server memorizza i documenti analizzati in una cache interna. Per evitare di andare in out of memory si deve ricorrere alla stored procedure di sistema
sp_xml_removedocument per rilasciare il riferimento al documento e distruggere la struttura ad albero quando non è più richiesta.
La chiamata alla
sp_xml_removedocument deve avvenire nello stesso batch che ha eseguito la
sp_xml_preparedocument, dato che il parametro
hdoc utilizzando per referenziare l’albero non è altro che una variabile locale e se va fuori dalla sua area di validità, non c’è alcun modo per rimuovere l’albero dalla memoria.
La sintassi di OPENXML
Dopo aver analizzato un documento
XML utilizzando la stored procedure
sp_xml_preparedocument ed aver ottenuto un handle all'albero è possibile generare un rowset.
La funzione
OPENXML serve proprio per recuperare un rowset dall’albero. Una volta ottenuto il rowset sarà possibile eseguire comandi
Transact-SQL come
SELECT, UPDATE o INSERT.
La funzione
OPENXML ha la seguente sintassi.
OpenXML(hdoc, rowpattern [, flags])
[WITH (SchemaDeclaration | TableName)]
::= ColumnName ColumnType [colpattern], n
Questa tabella descrive i parametri della funzione
OPENXML.
Ora che abbiamo spiegato dal punto di vista teorico l'elaborazione dei dati
XML vediamo come possiamo applicare i concetti appena visti all'esempio proposto.
/* Eseguo la stessa query utilizzando la funzione OPENXML */
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc;
SELECT *
FROM OPENXML (@hdoc, '/ROOT/Customers/Orders', 1)
WITH (CustomerID char(4) '../@CustomerID',
ContactName varchar(6) '../@ContactName',
CompanyName varchar(10) '../@CompanyName',
OrderDate datetime '@OrderDate'
);
EXEC sp_xml_removedocument @hdoc;
Eseguendola otterremo il risultato visualizzato in figura.
I metodi nodes e value
Come abbiamo visto in precedenza il data type
XML di
SQL Server 2005 fornisce quattro metodi che possono essere utilizzati per interrogare o modificare i dati
XML. Questi metodi possono essere richiamati utilizzando la sintassi
data_type.nome_metodo familiare a molti sviluppatori.
Il metodo
nodes può essere utilizzato per generare una vista relazionale dei dati
XML. Il metodo nodes restituisce un rowset nel quale ogni nodo identificato da una espressione
XQuery è restituito come un nodo di contesto dal quale le successive query possono estrarre i dati.
Il metodo
nodes ha la seguente sintassi.
Xmlvalue.nodes (XQuery) [AS] Table(Column)I parametri della sintassi nodes sono descritti in questa tabella.
Ora che abbiamo una rappresentazione relazionale dei dati, entra in gioco il metodo
value.
Il metodo
value è utilizzato per restituire un singolo valore da un documento
XML. Per utilizzare il metodo value occorre specificare una espressione
XQuery che identifica un singolo nodo del documento
XML ed il data type
Transact-SQL del valore che deve essere restituito.
Applichiamo i metodi appena visti al nostro esempio scrivendo la seguente query.
/* Rappresento in forma relazionale il documento
** utilizzando i metodi nodes e value
*/
SELECT
myColumn.value('../@CustomerID', 'char(4)') AS CustomerID
, myColumn.value('../@ContactName', 'varchar(6)') AS ContactName
, myColumn.value('../@CompanyName', 'varchar(10)') AS CompanyName
, myColumn.value('@OrderDate', 'datetime') AS OrderDate
FROM @xmlDoc.nodes('/ROOT/Customers/Orders') AS myTable(myColumn);
Eseguendola otterremo il risultato visualizzato in figura.
A questo punto per valorizzare la tabella
dbo.Orders potremo utilizzare indistintamente una delle due tecniche appena trattate, associandole al comando
Transact-SQL INSERT.
/* Valorizzo la tabella dbo.Orders utilizzando i metodi nodes e value */
INSERT dbo.Orders
SELECT
myColumn.value('../@CustomerID', 'char(4)') AS CustomerID
, myColumn.value('../@ContactName', 'varchar(6)') AS ContactName
, myColumn.value('../@CompanyName', 'varchar(10)') AS CompanyName
, myColumn.value('@OrderDate', 'datetime') AS OrderDate
FROM @xmlDoc.nodes('/ROOT/Customers/Orders') AS myTable(myColumn);
/* Valorizzo la tabella dbo.Orders utilizzando la funzione OPENXML */
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc;
INSERT dbo.Orders
SELECT *
FROM OPENXML (@hdoc, '/ROOT/Customers/Orders', 1)
WITH (CustomerID char(4) '../@CustomerID',
ContactName varchar(6) '../@ContactName',
CompanyName varchar(10) '../@CompanyName',
OrderDate datetime '@OrderDate'
);
EXEC sp_xml_removedocument @hdoc;
Interrogando la tabella
dbo.Orders otterremo il risultato in figura.
Come vedete con uno sforzo minimo siamo riusciti a raggiungere il risultato che ci eravamo prefissi.
Conclusioni
In questo articolo abbiamo solo sfiorato alcune delle nuove funzionalità che sono state introdotte in
SQL Server 2005 per la gestione dei documenti
XML. Le novità non si fermano certo qui. Ad esempio ci sarebbe da trattare in modo approfondito le estensioni alla clausola
FOR XML e magari questo argomento potrebbe essere lo spunto per un nuovo articolo.