OPENXML (SQL Server)

OPENXML, una parola chiave Transact-SQL, fornisce un set di righe su documenti XML in memoria simili a una tabella o a una vista. OPENXML consente l'accesso ai dati XML come se fosse un set di righe relazionale. A tale scopo, viene fornita una visualizzazione set di righe della rappresentazione interna di un documento XML. I record nel set di righe possono essere archiviati nelle tabelle di database.

OPENXML può essere usato nelle istruzioni SELECT e SELECT INTO ovunque possano apparire come origine i provider di set di righe, una vista o OPENROWSET. Per informazioni sulla sintassi di OPENXML, vedere OPENXML (Transact-SQL).

Per scrivere query su un documento XML tramite OPENXML, è prima necessario chiamare sp_xml_preparedocument. Analizza il documento XML e restituisce un handle al documento analizzato pronto per l'utilizzo. Il documento analizzato è una rappresentazione ad albero DOM (Document Object Model) di vari nodi nel documento XML. L'handle del documento viene passato a OPENXML. OPENXML fornisce quindi una visualizzazione del set di righe del documento, in base ai parametri passati.

Annotazioni

sp_xml_preparedocument usa una versione aggiornata di SQL del parser MSXML, Msxmlsql.dll. Questa versione del parser MSXML è stata progettata per supportare SQL Server e rimanere compatibile con le versioni precedenti di MSXML versione 2.6.

La rappresentazione interna di un documento XML deve essere rimossa dalla memoria chiamando la stored procedure di sistema sp_xml_removedocument per liberare la memoria.

Nella seguente illustrazione viene mostrato il processo.

Si noti che per comprendere OPENXML, è necessaria la familiarità con le query XPath e la comprensione di XML. Per altre informazioni sul supporto XPath in SQL Server, vedere Uso di query XPath in SQLXML 4.0.

Annotazioni

OpenXML consente di parametrizzare i modelli XPath di riga e colonna come variabili. Tale parametrizzazione può comportare inserimenti di espressioni XPath, se il programmatore espone la parametrizzazione agli utenti esterni, ad esempio se i parametri vengono forniti tramite una stored procedure chiamata esternamente. Per evitare potenziali problemi di sicurezza, è consigliabile che i parametri XPath non vengano mai esposti ai chiamanti esterni.

Esempio

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

Prima di tutto, la sp_xml_preparedocument stored procedure analizza il documento XML. Il documento analizzato è una rappresentazione ad albero dei nodi (elementi, attributi, testo e commenti) nel documento XML. OPENXML fa quindi riferimento a questo documento XML analizzato e fornisce una vista a set di righe di tutto o parte di questo documento XML. Un'istruzione INSERT che usa OPENXML può inserire dati da un set di righe di questo tipo in una tabella di database. È possibile utilizzare diverse OPENXML chiamate per fornire una visualizzazione set di righe di varie parti del documento XML ed elaborarle, ad esempio, inserendole in tabelle diverse. Questo processo viene anche definito come scomposizione dell'XML in tabelle.

Nell'esempio seguente, un documento XML viene frammentato in modo che gli elementi <Customers> siano archiviati nella tabella Customers e gli elementi <Orders> siano archiviati nella tabella Orders utilizzando due istruzioni INSERT. L'esempio mostra anche un'istruzione SELECT con OPENXML che recupera CustomerID e OrderDate dal documento XML. L'ultimo passaggio del processo consiste nel chiamare sp_xml_removedocument. Questa operazione viene eseguita per rilasciare la memoria allocata per contenere la rappresentazione interna dell'albero XML 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 utilizzando sp_xml_preparedocument.

Parametri OPENXML

I parametri per OPENXML includono quanto segue:

• Gestore del documento XML (idoc)

• Espressione XPath per identificare i nodi di cui eseguire il mapping alle righe (rowpattern)

• Descrizione del set di righe da generare

• Mapping tra le colonne del rowset e i nodi XML

Gestione documento XML (idoc)

L'handle del documento viene restituito dalla sp_xml_preparedocument procedura memorizzata.

Espressione XPath per identificare i nodi da elaborare (rowpattern)

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

I nodi identificati dall'espressione XPath possono essere qualsiasi nodo XML nel documento XML. Se rowpattern identifica un set di elementi nel documento XML, è presente una riga nel set di righe per ogni nodo di elemento identificato. Ad esempio, se rowpattern termina con un attributo, viene creata una riga per ogni nodo dell'attributo selezionato da rowpattern.

Descrizione del set di righe da generare

Uno schema del set di righe viene usato da OPENXML per generare il set di righe risultante. Quando si specifica uno schema di set di righe, è possibile usare le opzioni seguenti.

Utilizzo del formato tabella Edge

È consigliabile usare il formato della tabella dei bordi per specificare uno schema del set di righe. Non utilizzare la clausola WITH.

Quando si esegue questa operazione, OPENXML restituisce un set di righe nel formato della tabella perimetrale. Questa operazione viene definita tabella degli edge, perché ogni edge dell'albero dei documenti XML analizzato corrisponde a una riga nell'insieme di righe.

Le tabelle edge rappresentano all'interno di una singola tabella la struttura di documenti XML con granularità fine. Questa struttura include i nomi degli elementi e degli attributi, la gerarchia dei documenti, gli spazi dei nomi e le istruzioni di elaborazione. Il formato della tabella dei bordi consente di ottenere informazioni aggiuntive non esposte tramite le metaproprietà. Per altre informazioni sulle metaproprietà, vedere Specificare metaproprietà in OPENXML.

Le informazioni aggiuntive fornite da una tabella perimetrale consentono di archiviare ed eseguire query sul tipo di dati di un elemento e di un attributo e sul tipo di nodo, nonché archiviare ed eseguire query sulle informazioni sulla struttura del documento XML. Con queste informazioni aggiuntive, può anche essere possibile creare un proprio sistema di gestione dei documenti XML.

Usando una tabella perimetrale, è possibile scrivere stored procedure che accettano documenti XML come input BLOB (Binary Large Object), produrre la tabella perimetrale e quindi estrarre e analizzare il documento in un livello più dettagliato. Questo livello dettagliato può includere la ricerca della gerarchia dei documenti, i nomi degli elementi e degli attributi, gli spazi dei nomi e le istruzioni di elaborazione.

La tabella perimetrale può anche fungere da formato di archiviazione per i documenti XML quando il mapping ad altri formati relazionali non è logico e un campo ntext non fornisce informazioni strutturali sufficienti.

In situazioni in cui è possibile usare un parser XML per esaminare un documento XML, è possibile utilizzare invece una tabella perimetrale per ottenere le stesse informazioni.

Nella tabella seguente viene descritta la struttura della tabella dei bordi.

Nome della colonna	Tipo di dati	Descrizione
ID	bigint	Identificatore univoco del nodo del documento. L'elemento radice ha un valore ID pari a 0. I valori ID negativi sono riservati.
parentid	bigint	Identifica l'elemento padre del nodo. L'elemento padre identificato da questo ID non è necessariamente l'elemento padre. Tuttavia, questo dipende dal NodeType del nodo il cui elemento padre è identificato da questo ID. Ad esempio, se il nodo è un nodo di testo, il relativo elemento padre può essere un nodo attributo. Se il nodo si trova al livello superiore del documento XML, parentID è NULL.
tipo di nodo	Int	Identifica il tipo di nodo e è un numero intero che corrisponde alla numerazione del tipo di nodo DOM (XML Object Model). Di seguito sono riportati i valori che possono essere visualizzati in questa colonna per indicare il tipo di nodo: 1 = Nodo elemento 2 = Nodo attributo 3 = Nodo di testo 4 = nodo di sezione CDATA 5 = Nodo riferimento entità 6 = Nodo entità 7 = Nodo istruzione di elaborazione 8 = Nodo di commento 9 = Nodo documento 10 = Nodo Tipo di documento 11 = Nodo di frammento del documento 12 = Nodo notazione Per altre informazioni, vedere l'argomento "Proprietà nodeType" nel Microsoft XML (MSXML) SDK.
localname	nvarchar(max)	Assegna il nome locale dell'elemento o dell'attributo. È NULL se l'oggetto DOM non ha un nome.
prefisso	nvarchar(max)	È il prefisso dello spazio dei nomi per il nome del nodo.
namespaceuri	nvarchar(max)	URI dello spazio dei nomi del nodo. Se il valore è NULL, non è presente alcun namespace.
Datatype	nvarchar(max)	Tipo di dati effettivo dell'elemento o della riga dell'attributo ed è in caso contrario NULL. Il tipo di dati viene dedotto dal DTD inline o dallo schema inline.
Prev	bigint	ID XML dell'elemento fratello precedente. È NULL se non è presente alcun precedente elemento di pari livello diretto.
testo	ntext	Contiene il valore dell'attributo o il contenuto dell'elemento in formato testo. Oppure è NULL, se la voce della tabella dei margini non richiede un valore.

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 semplicemente un nome di tabella esistente il cui schema può essere usato 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. Specificando lo schema del set di righe, specificare i nomi delle colonne, i relativi tipi di dati e il relativo mapping al documento XML.

È possibile specificare il modello di colonna usando il parametro ColPattern in SchemaDeclaration. Il modello di colonna specificato viene utilizzato per eseguire il mapping di una colonna del set di righe al nodo XML identificato da rowpattern e viene usato anche per determinare il tipo di mapping.

Se ColPattern non viene specificato per una colonna, la colonna del set di righe esegue il mapping al nodo XML con lo stesso nome, in base al mapping specificato dal parametro flags . Tuttavia, se ColPattern viene specificato come parte della specifica dello schema nella clausola WITH, sovrascrive il mapping specificato nel parametro flags .

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

Nell'istruzione OPENXML è possibile specificare facoltativamente il tipo di mapping, ad esempio incentrato sugli attributi o incentrato sugli elementi, tra le colonne del set di righe e i nodi XML identificati dal rowpattern. Queste informazioni vengono utilizzate nella trasformazione tra i nodi XML e le colonne del set di righe.

È possibile specificare il mapping in due modi ed è anche possibile specificare entrambi:

• Usando il parametro flags

  Il mapping specificato dal parametro flags presuppone la corrispondenza dei nomi in cui i nodi XML eseguono il mapping alle colonne del set di righe corrispondenti con lo stesso nome.

• Usando il parametro ColPattern

  ColPattern, un'espressione XPath, viene specificata come parte di SchemaDeclaration nella clausola WITH. Il mapping specificato in ColPattern sovrascrive il mapping specificato dal parametro flags .

  È possibile usare ColPattern per specificare il tipo di mapping, ad esempio incentrato sugli attributi o incentrato sugli elementi, che sovrascrive o migliora il mapping predefinito indicato dai flag.

  ColPattern viene specificato nelle circostanze seguenti:

  • Il nome della colonna nel set di righe è diverso dal nome dell'elemento o dell'attributo a cui è mappato. In questo caso, ColPattern viene usato per identificare l'elemento XML e il nome dell'attributo a cui la colonna del set di righe mappa.

  • Si desidera associare un attributo metaproperty alla colonna. In questo caso, ColPattern viene usato per identificare la metaproprietà a cui viene mappata la colonna del set di righe. Per altre informazioni su come usare le metaproprietà, vedere Specificare metaproprietà in OPENXML.

Entrambi i flag e ColPattern sono facoltativi. Se non viene specificato alcun mapping, viene utilizzato il mapping incentrato sugli attributi. Il mapping incentrato sugli attributi è il valore predefinito del parametro flags .

Mappatura incentrata sugli attributi

L'impostazione del parametro flags in OPENXML su 1 (XML_ATTRIBUTES) specifica il mapping incentrato sugli attributi . Se i flag contengono XML_ ATTRIBUTES, il set di righe esposto fornisce o utilizza righe in cui ogni elemento XML è rappresentato come riga. Gli attributi XML vengono mappati agli attributi definiti in SchemaDeclaration o forniti dal tablename della clausola WITH, in base alla corrispondenza dei nomi. La corrispondenza dei nomi indica che gli attributi XML di un nome specifico vengono archiviati in una colonna nel set di righe con lo stesso nome.

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

Se l'attributo XML ha un qualificatore dello spazio dei nomi, anche il nome della colonna nel set di righe deve avere il qualificatore.

Mapping incentrato sugli elementi

L'impostazione del parametro flags in OPENXML su 2 (XML_ELEMENTS) specifica il mapping incentrato sugli elementi . È simile al mapping incentrato sugli attributi , ad eccezione delle differenze seguenti:

• La corrispondenza dei nomi nell'esempio di associazione prevede che un mapping di colonna a un elemento XML con lo stesso nome scelga i sottoelementi semplici, a meno che non venga specificato un pattern a livello di colonna. Nel processo di recupero, se il sottoelemento è complesso perché contiene sottoelementi aggiuntivi, la colonna viene impostata su NULL. I valori degli attributi dei sottoelementi vengono quindi ignorati.

• Per più sottoelementi con lo stesso nome, viene restituito il primo nodo.

Vedere anche

sp_xml_preparedocument (Transact-SQL)sp_xml_removedocument (Transact-SQL)OPENXML (Transact-SQL)DATI XML (SQL Server)