OPENXML (SQL Server)

La parola chiave Transact-SQL OPENXML restituisce un set di righe simile a una tabella o una vista da documenti XML in memoria. OPENXML consente di accedere ai dati XML come se si trattasse di un set di righe relazionali, visualizzando la rappresentazione interna di un documento XML come un set di righe. I record del set di righe possono essere archiviati in tabelle di database.

È possibile utilizzare OPENXML nelle istruzioni SELECT e SELECT INTO ogni volta che i provider di set di righe, una vista o OPENROWSET possono comparire come origine. Per informazioni sulla sintassi di OPENXML, vedere OPENXML (Transact-SQL).

Per creare query su un documento XML tramite OPENXML, è innanzitutto necessario chiamare sp_xml_preparedocument, che analizza il documento XML e restituisce un handle al documento analizzato e pronto per l'utilizzo. Il documento analizzato è una rappresentazione dell'albero del modello a oggetti documento (DOM, Document Object Model) dei vari nodi inclusi nel documento XML. L'handle del documento viene quindi passato a OPENXML, che a sua volta visualizza il documento come un set di righe in base ai parametri passati.

[!NOTA]

A partire da SQL Server 2005, sp_xml_preparedocument utilizza una versione aggiornata da SQL del parser di MSXML, Msxmlsql.dll. Questa versione è stata progettata per supportare SQL Server e garantire la compatibilità con MSXML versione 2.6.

La rappresentazione interna di un documento XML deve essere rimossa dalla memoria tramite una chiamata alla stored procedure di sistema sp_xml_removedocument , in modo tale da liberare memoria.

Nella figura seguente è illustrato questo processo.

Si noti che per comprendere l'utilizzo di OPENXML, è necessario avere familiarità con le query XPath e XML. Per ulteriori informazioni sul supporto di XPath in SQL Server, vedere Utilizzo di query XPath in SQLXML 4.0.

[!NOTA]

OpenXML consente di parametrizzare i modelli XPath di riga e colonna come le variabili. La parametrizzazione può comportare attacchi intrusivi nelle espressioni XPath se viene esposta dal programmatore a utenti esterni, ad esempio se i parametri sono forniti tramite una stored procedure chiamata dall'esterno. Per evitare questi problemi potenziali di sicurezza, è consigliabile non esporre mai i parametri XPath a chiamanti esterni.

Esempio

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 corrisponde a una rappresentazione ad albero dei vari nodi (elementi, attributi, testo, commenti e così via) del documento XML. OPENXML si riferisce quindi al documento XML analizzato e fornisce una visualizzazione del set di righe di tutto o parte del documento. 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.

Parametri di OPENXML

I parametri passati a OPENXML includono:

• L'handle di un documento XML (idoc).

• Un'espressione XPath che identifica i nodi di cui eseguire il mapping alle righe (rowpattern).

• Una descrizione del set di righe da generare.

• Il mapping tra le colonne del set di righe e i nodi XML.

Handle del documento XML (idoc)

L'handle del documento viene restituito dalla stored procedure sp_xml_preparedocument.

Espressione XPath che identifica i nodi da elaborare (rowpattern)

L'espressione XPath specificata come rowpattern identifica un set di nodi nel documento XML. Ogni nodo definito da rowpattern corrisponde a una riga specifica del set di righe generato da OPENXML.

I nodi definiti dall'espressione XPath possono essere qualsiasi nodo XML del documento XML. Se rowpattern identifica un set di elementi nel documento XML, a ogni nodo identificato corrisponderà una riga nel set di righe. Ad esempio, se rowpattern restituisce un attributo, verrà creata una riga per ogni nodo attributo selezionato da rowpattern.

Descrizione del set di righe da generare

OPENXML utilizza uno schema di set di righe per generare il set di righe risultante. Per specificare uno schema di set di righe, è possibile utilizzare le opzioni illustrate di seguito.

Utilizzo del formato di tabella edge

Per specificare uno schema di set di righe è consigliabile utilizzare il formato di tabella edge, evitando invece di utilizzare la clausola WITH.

In questo caso, OPENXML restituisce un set di righe nel formato di tabella edge. Il nome di questo formato deriva dal fatto che su ogni margine dell'albero del documento XML analizzato viene eseguito il mapping a una riga del set di righe.

In una tabella edge viene rappresentata la struttura dettagliata di un documento XML, che include i nomi degli elementi e degli attributi, la gerarchia del documento, gli spazi dei nomi e le istruzioni per l'elaborazione. Il formato di tabella edge consente di ottenere informazioni aggiuntive non esposte tramite le metaproprietà. Per ulteriori informazioni sulle metaproprietà, vedere Impostazione di metaproprietà in OPENXML.

Le informazioni aggiuntive disponibili in una tabella edge consentono di archiviare e di eseguire query sul tipo di dati di un elemento o di un attributo e sul tipo di nodo, di archiviare ed eseguire query sulle informazioni relative alla struttura del documento XML, nonché di compilare un sistema di gestione dei documenti XML personalizzato.

Tramite una tabella edge è possibile scrivere stored procedure che accettano i documenti XML come input BLOB (Binary Large Object), generano la tabella edge e quindi estraggono e analizzano il documento al massimo livello di dettaglio (ricerca della gerarchia del documento, dei nomi di elementi e attributi, degli spazi dei nomi e delle istruzioni per l'elaborazione).

La tabella edge può inoltre fungere da formato di archiviazione per i documenti XML quando nessun altro tipo di mapping ad altri formati relazionali risulta appropriato e un campo ntext non restituisce informazioni strutturali sufficienti.

L'utilizzo di una tabella edge per analizzare un documento XML equivale a utilizzare un parser XML per ottenere le stesse informazioni.

Nella tabella seguente viene descritta la struttura della tabella edge.

Utilizzo della clausola WITH per specificare una tabella esistente

È possibile utilizzare la clausola WITH per specificare il nome di una tabella esistente. A tale scopo, specificare il nome della tabella il cui schema può essere utilizzato da OPENXML per generare il set di righe.

Utilizzo della clausola WITH per specificare uno schema

È possibile utilizzare la clausola WITH per specificare uno schema completo. Quando si specifica lo schema del set di righe, si definiscono i nomi delle colonne e i rispettivi tipi di dati, nonché il mapping di tali colonne al documento XML.

È possibile specificare il modello di colonna utilizzando il parametro ColPattern nell'argomento SchemaDeclaration. Il modello di colonna specificato consente di eseguire il mapping di una colonna del set di righe al nodo XML identificato da rowpattern, nonché di determinare il tipo di mapping.

Se per una colonna non viene specificato il parametro ColPattern, viene eseguito il mapping della colonna del set di righe al nodo XML con lo stesso nome, in base al mapping specificato dal parametro flags. Se viene impostato nell'ambito della specifica dello schema nella clausola WITH, il parametro ColPattern sarà tuttavia prioritario rispetto al mapping specificato dal parametro flags.

Mapping tra le colonne del set di righe e i nodi XML

Nell'istruzione OPENXML è inoltre possibile specificare facoltativamente il tipo di mapping (incentrato sugli attributi o sugli elementi) tra le colonne del set di righe e i nodi XML identificati da rowpattern. Questa informazione viene utilizzata per la trasformazione tra i nodi XML e le colonne del set di righe.

Per specificare il tipo di mapping è possibile procedere in due modi e inoltre utilizzare entrambi i sistemi:

• Tramite il parametro flags.

  Il mapping specificato dal parametro flags si basa sullo corrispondenza dei nomi, ovvero sui nodi XML viene eseguito il mapping alle colonne del set di righe corrispondenti con lo stesso nome.

• Tramite il parametro ColPattern.

  Il parametro ColPattern, ovvero un'espressione XPath, viene specificato nell'ambito di SchemaDeclaration nella clausola WITH. Il mapping specificato dal parametro ColPattern sovrascrive il mapping specificato dal parametro flags.

  Il parametro ColPattern consente di specificare il tipo di mapping, incentrato sugli attributi o sugli elementi, che sovrascrive o migliora il mapping predefinito indicato in flags.

  Il parametro ColPattern viene specificato nei casi seguenti:

  • Se il nome di colonna nel set di righe è diverso dal nome dell'elemento o dell'attributo al quale viene eseguito il mapping. In questo caso, il parametro ColPattern consente di identificare il nome dell'elemento o dell'attributo XML al quale viene eseguito il mapping della colonna del set di righe.

  • Se si desidera eseguire il mapping di un attributo di una metaproprietà alla colonna. In questo caso, il parametro ColPattern consente di definire la metaproprietà alla quale viene eseguito il mapping della colonna del set di righe. Per ulteriori informazioni sull'utilizzo delle metaproprietà, vedere Impostazione di metaproprietà in OPENXML.

I parametri flags e ColPattern sono entrambi facoltativi. Se non viene specificato un mapping, viene utilizzato il mapping incentrato sugli attributi, ovvero il valore predefinito del parametro flags.

Mapping incentrato sugli attributi

Se il parametro flags dell'istruzione OPENXML viene impostato su 1 (XML_ATTRIBUTES), viene definito un mapping incentrato sugli attributi. Se il parametro flags include XML_ ATTRIBUTES, il set di righe esposto restituisce o utilizza le righe nelle quali ogni elemento XML è rappresentato come una riga. Sugli attributi XML viene eseguito il mapping agli attributi definiti in SchemaDeclaration o restituiti da Tablename nella clausola WITH, in base alla corrispondenza dei nomi. La corrispondenza dei nomi indica che gli attributi XML con un nome specifico vengono archiviati in una colonna del set di righe con lo stesso nome.

Se il nome della colonna è diverso dal nome dell'attributo a cui viene eseguito il mapping della colonna, è necessario specificare il parametro ColPattern.

Se l'attributo XML è associato a un qualificatore dello spazio dei nomi, anche il nome della colonna del set di righe deve essere associato a un qualificatore.

Mapping incentrato sugli elementi

Se il parametro flags dell'istruzione OPENXML viene impostato su 2 (XML_ATTRIBUTES), viene definito un mapping incentrato sugli elementi. Pur essendo simile al mapping incentrato sugli attributi, questo tipo di mapping è caratterizzato dalle differenze seguenti:

• In base alla corrispondenza dei nomi del mapping, ad esempio una colonna su cui viene eseguito il mapping a un elemento XML avente lo stesso nome, verranno selezionati i sottoelementi non complessi a meno che non venga specificato un modello dei livelli delle colonne. Nella fase di recupero dei dati, se il sottoelemento è complesso perché include altri sottoelementi, la colonna verrà impostata su NULL e i valori degli attributi dei sottoelementi verranno ignorati.

• Nel caso di più sottoelementi con lo stesso nome, verrà restituito il primo nodo.

Vedere anche

Riferimento

sp_xml_preparedocument (Transact-SQL)

sp_xml_removedocument (Transact-SQL)

OPENXML (Transact-SQL)

Concetti

Dati XML (SQL Server)