Home Page Home Page Articoli Leggere un file XML con SQL Server 2005

Leggere un file XML con SQL Server 2005

Scopriamo in questo articolo alcune potenzialità offerte dal nuovo database SQL 2005, tra cui il nuovo data type XML, e le Stored Procedures per importare e gestire all'interno del database file testuali in formato XML.
Autore: Lorenzo Benaglia Livello:
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.

Codice XML n°1
<?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.

Codice SQL n°2
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.

Codice SQL n°3
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.

Codice SQL n°4
/* 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.

Codice SQL n°5
/* 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.

Codice SQL n°6
/* 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.
Voto medio articolo: 4.5 Numero Voti: 2
Lorenzo Benaglia

Lorenzo Benaglia

MVP Program Logo Lorenzo Benaglia lavora nel settore informatico come sviluppatore, trainer e DBA dal 1995 anche se la passione è iniziata una decina di anni prima con l'acquisto di un home computer MSX. Dall'anno 2000 svolge le mansioni di DBA e System Engineer presso Il Sole 24 Ore S.p.A. Ha conseguito le certificazioni MCP, MC... Profilo completo

Articoli collegati

Garantire l'univocità di una colonna ammettendo valori NULL multipli
Chi sviluppa in ambito professionale o per puro diletto prima o poi si troverà ad affrontare tematiche nuove ed avvincenti. In questo articolo cercherò di illustrare tre tecniche con i relativi pregi e difetti per permettere con SQL Server di garantire l'univocità di una colonna senza alcuna limitazione sui valori NULL
Autore: Lorenzo Benaglia | Difficoltà:
Integrare Crystal Reports con una Windows Form
Ecco come sfruttare in modo semplice e rapido le funzionalità offerte dal tool di reportistica Crystal Reports di Business Objects all'interno di una Windows Form .NET, dalla crazione del Report al Binding dei dati.
Autore: Matteo Raumer | Difficoltà: | Commenti: 2
Il Logging con Integration Services 2005
Perchè si è bloccata la mia applicazione ? Che errori si sono verificati ? Perchè il flusso d'esecuzione non procede nel modo corretto ? Cerchiamo di capire come possono esserci utili le funzionalità di logging offerte da SQL Server e gli Integration Services per rendere più affidabili e consistenti le nostre applicazioni.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 3
Utilizzare i SQL Server Agent Proxies con i Package SSIS
Chi utilizza frequentemente i SSIS Packages e accede a risorse esterne a SQL Server non può fare a meno di conoscere ed utilizzare i Proxy Account (o SQL Server Agent Proxies). Vediamo che cosa sono e come si utilizzano in modo proficuo.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 4
SQL Server 2005 - La Import/Export utility
Chi lavora sui Database si ritrova quasi quotidianamente a dover importare o esportare dati. SQL Server 2005 agevola notevolmente il compito tramite l'Import/Export Utility (ex DTS Import/Export di SQL 2000). Vediamo come funziona.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 14
Visual Studio 2005 Team System for Database Professionals
Scopriamo questo nuovo e potente Tool della famiglia Visual Studio 2005 Team System per creare, sviluppare, testare e gestire con più facilità e flessibilità i database di SQL Server.
Autore: Marco Caruso | Difficoltà:
Parallelismo tra SSIS e DTS, due prodotti a confronto
In questo articolo un'analisi sulle novità dei SQL Server 2005 Integration Services rispetto ai DTS di SQL Server 2000.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 2
Overview dei SQL Server Integration Services (SSIS)
Scopriamo i nuovi Sql Server Integration Services presenti in SQL Server 2005 che sostituisco i DTS aggiungendo delle nuove funzionalità davvero interessanti
Autore: Alessandro Alpi | Difficoltà: | Commenti: 6 | Voto:
ExcelDataSetLib, una libreria .NET per esportare dati in formato Excel
In questo articolo verrà dimostrato come derivare la classe DataSet e integrare funzionalità per esportare in formato Excel XLS, XML o CSV i dati provenienti da un base dati qualsiasi.
Autore: David De Giacomi | Difficoltà: | Commenti: 3 | Voto:
Microsoft Application Blocks Data Access
Quante volte vi è capitato di riscrivere decine di volte lo stesso pezzo di codice all'interno di un progetto ? Scopriamo in questo articolo l'utilità dei Microsoft Application Blocks che vi permettono di ottimizzare il vostro codice evitando ripetizioni e vi facilitano la lettura.
Autore: Marco Caruso | Difficoltà:
Comprimere e decomprimere files ZIP con .NET e #ZipLib
Visualizzare il contenuto di un file Zip da una applicazione vi sembra una Mission Impossibile? Oppure la vostra azienda ha fatto un mutuo per pagare il componente che vi aiuta nel lavoro? Ecco un articolo che parla della libreria sharpZipLib, libreria Open Source per la gestione dei formati compressi.
Autore: Giovanni Ferron | Difficoltà: | Commenti: 7
Rivoluzione nel campo dei Reports con i SQL Reporting Services
Finalmente Microsoft ci mette a disposizione uno straordinario tool per la produzione di Report perfettamente integrato con l'architettura .NET. Fino ad ora siamo sempre stati abituati ad utilizzare il buono seppur limitato Crystal Report. Vediamo cosa cambia da ora in poi.
Autore: David De Giacomi | Difficoltà: | Commenti: 6 | Voto:
Scopriamo cosa sono gli Office XP PIA
Vediamo in quest'articolo una dettagliata introduzione dei PIA gli assemblies che permettono ad un'applicazione .NET di interagire e di dialogare con i prodotti della suite Microsoft Office XP.
Autore: David De Giacomi | Difficoltà: | Commenti: 6
Migliorare la qualità dei grafici nei Crystal Reports
In questo breve articolo vedremo come aumentare la qualità dei grafici prodotti da Crystal Reports durante il rendering tramite Web con ASP.NET e all'interno di applicazioni WinForms.
Autore: David De Giacomi | Difficoltà:
Costruire Report con ASP.NET e Crystal Reports
Vedremo in questo semplice Tutorial le istruzioni base che ci permetteranno di costruire un report avanzato usando ASP.NET e l'oggetto CrystalReportViewer integrato in questa versione di Visual Studio .NET.
Autore: David De Giacomi | Difficoltà: | Commenti: 14
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5