Utilizzo di FOR XML e di OPENXML per pubblicare ed elaborare dati XML

È possibile eseguire query SQL per restituire i risultati in formato XML anziché come set di righe standard. Tali query possono essere eseguite direttamente oppure da stored procedure e funzioni definite dall'utente. Per recuperare direttamente i risultati, è necessario utilizzare innanzitutto la clausola FOR XML dell'istruzione SELECT e quindi, all'interno di tale clausola, specificare una modalità XML: RAW, AUTO, EXPLICIT o PATH.

L'istruzione SELECT seguente, ad esempio, recupera informazioni dalle tabelle Sales.Customer e Sales.SalesOrderHeader del database AdventureWorks2008R2. La query seguente specifica la modalità AUTO nella clausola FOR XML:

USE AdventureWorks2008R2
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

Nei casi in cui è possibile utilizzare la clausola FOR XML per recuperare i dati come documento XML, è possibile utilizzare la funzione Transact-SQL OPENXML per inserire i dati rappresentati come documento XML. La funzione OPENXML è un provider di set di righe simile a una tabella o a una vista e restituisce un set di righe dai documenti XML in memoria. La funzione OPENXML consente di accedere ai dati XML come se si trattasse di un set di righe relazionali tramite la restituzione di una vista di set di righe della rappresentazione interna di un documento XML. I record del set di righe possono essere archiviati in tabelle di database. È possibile utilizzare OPENXML nelle istruzioni SELECT e SELECT INTO in cui è consentito specificare una tabella o una vista di origine.

Nell'esempio seguente viene illustrato l'utilizzo di OPENXML in un'istruzione INSERT e in un'istruzione SELECT. Il documento XML di esempio contiene gli elementi <Customers> e <Orders>.

La stored procedure sp_xml_preparedocument analizza innanzitutto il documento XML. Il documento analizzato è una rappresentazione dell'albero dei nodi (elementi, attributi, testo e commenti) inclusi nel documento XML. OPENXML fa quindi riferimento al documento XML analizzato e restituisce una vista del set di righe di tutto il documento XML o di alcune parti. Un'istruzione INSERT che utilizza OPENXML può inserire i dati di tale set di righe in una tabella di database. È possibile utilizzare diverse chiamate a OPENXML per ottenere una vista del set di righe di diverse parti del documento XML ed elaborarle, ad esempio inserendole in tabelle diverse. Tale processo è noto anche come "suddivisione del documento XML in tabelle".

Nell'esempio seguente un documento XML viene suddiviso in modo che gli elementi <Customers> vengano archiviati nella tabella Customers e gli elementi <Orders> vengano archiviati nella tabella Orders utilizzando due istruzioni INSERT. Nell'esempio viene inoltre illustrata un'istruzione SELECT con la funzione OPENXML che recupera CustomerID e OrderDate dal documento XML. L'ultimo passaggio del processo consiste nel chiamare sp_xml_removedocument in modo da rilasciare la memoria allocata per includere la rappresentazione dell'albero XML interno creata durante la fase di analisi.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'<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">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle 

Nella figura seguente viene illustrato l'albero XML analizzato del documento XML precedente creato tramite sp_xml_preparedocument.