OPENXML (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure
OpenXML fornisce una vista di un documento XML basata su un set di righe. In quanto provider di set di righe, è possibile utilizzare OPENXML nelle istruzioni di Transact-SQL che supportano provider di set di righe quali una tabella, una vista o la funzione OPENROWSET.
Convenzioni di sintassi Transact-SQL
Sintassi
OPENXML ( idoc int [ in ]
, rowpattern nvarchar [ in ]
, [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Nota
Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.
Argomenti
idoc
Handle del documento della rappresentazione interna di un documento XML. La rappresentazione interna di un documento XML viene creata chiamando sp_xml_preparedocument.
rowpattern
Modello XPath usato per identificare i nodi da elaborare come righe. I nodi provengono dal documento XML di cui viene passato l'handle nel parametro idoc.
flags
Indica il mapping da usare tra i dati XML e il set di righe relazionale e la modalità di riempimento della colonna di overflow. flags è un parametro di input facoltativo. I possibili valori sono i seguenti.
| Valore byte | Descrizione |
|---|---|
| 0 | Per impostazione predefinita viene impostato il mapping incentrato sugli attributi. |
| 1 | Viene usato il mapping incentrato sugli attributi. Può essere utilizzato in combinazione con XML_ELEMENTS. In questo caso, il mapping incentrato sugli attributi viene applicato per primo. Successivamente il mapping incentrato sugli elementi viene applicato a tutte le colonne non ancora elaborate. |
| 2 | Viene usato il mapping incentrato sugli elementi. Può essere utilizzato in combinazione con XML_ATTRIBUTES. In questo caso, il mapping incentrato sugli attributi viene applicato per primo. Successivamente il mapping incentrato sugli elementi viene applicato a tutte le colonne non ancora elaborate. |
| 8 | Può essere utilizzato in combinazione (OR logico) con XML_ATTRIBUTES o XML_ELEMENTS. Nel contesto del recupero, questo flag indica che i dati utilizzati non devono essere copiati nella proprietà @mp:xmltextoverflow . |
SchemaDeclaration
Definizione dello schema del formato: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]
ColName
Nome della colonna nel set di righe.
ColType
Tipo di dati di SQL Server della colonna nel set di righe. Se i tipi di colonna sono diversi dal tipo di dati xml sottostante dell'attributo, viene eseguita un'assegnazione forzata del tipo.
ColPattern
Modello XPath generale facoltativo che descrive come eseguire il mapping dei nodi XML alle colonne. Se ColPattern viene omesso, viene applicato il mapping predefinito, ovvero il mapping incentrato sugli attributi o sugli elementi, a seconda del valore dei flag.
Per il mapping incentrato sugli attributi o sugli elementi, il modello XPath specificato come ColPattern consente di specificare la natura speciale del mapping che sovrascrive o migliora il mapping predefinito indicato in flag.
Il modello XPath generale specificato come ColPattern supporta inoltre le metaproprietà.
Metaproprietà
Una delle metaproprietà fornite da OPENXML. Se si specifica MetaProperty, la colonna contiene le informazioni definite dalla metaproprietà. Le metaproprietà consentono di estrarre informazioni, ad esempio sulla posizione relativa e sullo spazio dei nomi, relative ai nodi XML. Queste metaproprietà restituiscono una quantità di informazioni maggiore rispetto al quelle visibili nella rappresentazione testuale.
TableName
Nome della tabella che può essere assegnato (anziché SchemaDeclaration), se esiste già una tabella con lo schema desiderato e non sono necessari criteri di colonna.
Osservazioni:
La clausola WITH restituisce un formato di set di righe (e informazioni aggiuntive relative al mapping, se necessario) tramite SchemaDeclaration o specificando un valore esistente per TableName. Se la clausola facoltativa WITH viene omessa, i risultati vengono restituiti nel formato di tabella edge. Le tabelle edge rappresentano la struttura di documenti XML con granularità fine(ad esempio nomi di elementi/attributi, gerarchia di documenti, spazi dei nomi, PI e così via) in una singola tabella.
Nella tabella seguente viene descritta la struttura della tabella edge.
| Nome colonna | Tipo di dati | Descrizione |
|---|---|---|
| id | bigint | ID univoco del nodo del documento. Il valore dell'ID dell'elemento radice è 0. I valori di ID negativi sono riservati. |
| parentid | bigint | Identifica il padre del nodo. Il padre identificato da questo ID non corrisponde necessariamente all'elemento padre. Dipende infatti dal valore NodeType del nodo il cui padre è identificato da questo ID. Se ad esempio il nodo è di tipo testo, il relativo elemento padre potrebbe essere un nodo attributo. Se il nodo si trova al livello principale nel documento XML, il relativo valore ParentID è NULL. |
| nodetype | int | Identifica il tipo di nodo. Valore intero corrispondente alla numerazione del tipo di nodo XML DOM. I possibili tipi di nodo sono i seguenti: 1 = Nodo elemento 2 = Nodo attributo 3 = Nodo testo |
| localname | nvarchar | Nome locale dell'elemento o attributo. È NULL se l'oggetto DOM non ha un nome. |
| prefix | nvarchar | Prefisso dello spazio dei nomi del nome del nodo. |
| namespaceuri | nvarchar | URI dello spazio dei nomi del nodo. Se il valore è NULL, non sono presenti spazi dei nomi. |
| datatype | nvarchar | Il tipo di dati effettivo dell'elemento o della riga dell'attributo, in caso contrario è NULL. Il tipo di dati viene ricavato dalla DTD o dallo schema inline. |
| prev | bigint | ID XML dell'elemento di pari livello precedente. È NULL se non è presente alcun elemento diretto di pari livello precedente. |
| Testo | ntext | Include il valore dell'attributo o il contenuto dell'elemento in formato testuale (oppure è NULL se la voce della tabella edge non richiede un valore). |
Esempi
R. Usare un'istruzione edizione Standard LECT di base con OPENXML
Nell'esempio seguente viene creata una rappresentazione interna dell'immagine XML tramite sp_xml_preparedocument. Su tale rappresentazione viene quindi eseguita un'istruzione SELECT che utilizza un provider di set di righe OPENXML.
Il valore di flag viene impostato su 1, a indicare il mapping incentrato sugli attributi. Sugli attributi XML viene pertanto eseguito il mapping alle colonne del set di righe. Il valore di rowpattern specificato come /ROOT/Customer identifica i nodi <Customers> da elaborare.
Il parametro facoltativo ColPattern (modello di colonna) viene omesso in quanto il nome di colonna corrisponde ai nomi di attributo XML.
Il provider di set di righe OPENXML crea un set di righe composto dalle due colonne CustomerID e ContactName, da cui vengono ricavate le colonne necessarie, in questo caso tutte le colonne, tramite l'istruzione SELECT.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
CustomerID VARCHAR(10),
ContactName VARCHAR(20)
);
Questo è il set di risultati.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Se si esegue la stessa istruzione SELECT con i flag impostati su 2, a indicare il mapping incentrato sugli elementi, per i valori di CustomerID e ContactName di entrambi i clienti nel documento XML viene restituito NULL, perché non è disponibile alcun elemento denominato CustomerID o ContactName nel documento XML.
Questo è il set di risultati.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. Specificare ColPattern per il mapping tra colonne e attributi XML
La query seguente restituisce gli attributi ID cliente, data dell'ordine, ID prodotto e quantità dal documento XML. Il rowpattern identifica gli elementi <OrderDetails>. ProductID e Quantity sono attributi dell'elemento <OrderDetails>. Tuttavia, OrderID, CustomerID e OrderDate sono gli attributi dell'elemento padre (<Orders>).
Il parametro facoltativo ColPattern viene specificato per i mapping seguenti:
Sulle colonne
OrderID,CustomerIDeOrderDatedel set di righe viene eseguito il mapping agli attributi del padre dei nodi identificati da rowpattern nel documento XML.Sulla colonna
ProdIDdel set di righe viene eseguito il mapping all'attributoProductID, mentre sulla colonnaQtyviene eseguito il mapping all'attributoQuantitydei nodi identificati in rowpattern.
Il mapping incentrato sugli elementi specificato nel parametro flag viene sovrascritto dal mapping specificato in ColPattern.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
OrderID INT '../@OrderID',
CustomerID VARCHAR(10) '../@CustomerID',
OrderDate DATETIME '../@OrderDate',
ProdID INT '@ProductID',
Qty INT '@Quantity'
);
Questo è il set di risultati.
OrderID CustomerID OrderDate ProdID Qty
------------------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
C. Ottenere risultati in un formato di tabella perimetrale
Il documento XML utilizzato nell'esempio seguente è composto dagli elementi <Customers>, <Orders> e <Order_0020_Details>. In primo luogo, sp_xml_preparedocument viene chiamato per ottenere un handle di documento. quindi l'handle di documento viene passato a OPENXML.
Nell'istruzione OPENXMLrowpattern (/ROOT/Customers) identifica i nodi <Customers> da elaborare. Poiché la clausola WITH non è specificata, OPENXML restituisce il set di righe nel formato di tabella edge.
L'istruzione SELECT recupera infine tutte le colonne della tabella edge.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;
Vedi anche
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per