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 AdventureWorks. La query seguente specifica la modalità AUTO nella clausola FOR XML:
USE AdventureWorks
GO
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
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 della struttura 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 della struttura XML interna 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 illustrata la struttura XML analizzata del documento XML precedente creato tramite sp_xml_pareparedocument.
.gif "Struttura XML analizzata")
Vedere anche
Riferimento
Esecuzione di query su codice XML tramite OPENXML
Costruzione di codice XML tramite la clausola FOR XML
Sintassi di base della clausola FOR XML
Concetti
Tipo di dati XML
Applicazioni XML di esempio