OPENXML (Transact-SQL)

Articolo
10/31/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

OPENXML fornisce una visualizzazione set di righe su un documento XML. Poiché OPENXML è un provider di set di righe, OPENXML può essere usato nelle istruzioni Transact-SQL in cui possono essere visualizzati provider di set di righe, ad esempio una tabella, una vista o la OPENROWSET funzione.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

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 eseguito `attribute-centric` il mapping.
`1`	Usare il `attribute-centric` mapping. Può essere utilizzata in combinazione con `XML_ELEMENTS`. In questo caso, `attribute-centric` il mapping viene applicato per primo. `element-centric` Viene quindi applicato il mapping per le colonne rimanenti.
`2`	Usare il `element-centric` mapping. Può essere utilizzata in combinazione con `XML_ATTRIBUTES`. In questo caso, `element-centric` il mapping viene applicato per primo. `attribute-centric` Viene quindi applicato il mapping per le colonne rimanenti.
`8`	Può essere combinato (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:xmltext`overflow .

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 non è specificato, viene eseguito il mapping predefinito (attribute-centric o element-centric il mapping come specificato dai flag).

Il modello XPath specificato come ColPattern viene usato per specificare la natura speciale del mapping (per attribute-centric e element-centric mapping) che sovrascrive o migliora il mapping predefinito indicato dai 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 WITH clausola fornisce un formato di set di righe (e informazioni di mapping aggiuntive necessarie) usando SchemaDeclaration o specificando un tableName esistente. Se la clausola facoltativa WITH non è specificata, i risultati vengono restituiti in un formato di tabella perimetrale. 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. L'elemento radice ha un valore `0`ID . I valori di ID negativi sono riservati.
`parentid`	bigint	Identifica il padre del nodo. L'elemento padre identificato da questo ID non è necessariamente l'elemento padre, ma dipende dal `nodetype` nodo il cui elemento padre è identificato da questo ID. Ad esempio, se il nodo è un nodo di testo, l'elemento padre potrebbe essere un nodo attributo. Se il nodo si trova al livello superiore del documento XML, è `ParentID` `NULL`.
`nodetype`	int	Identifica il tipo di nodo. Questo valore è un numero intero che corrisponde alla numerazione del tipo di nodo DOM XML. I possibili tipi di nodo sono i seguenti: `1` = Nodo elemento `2` = Nodo attributo `3` = Nodo di 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 è presente alcuno spazio dei nomi.
`data type`	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 c'è nessun elemento di pari livello precedente diretto.
`text`	ntext	Contiene il valore dell'attributo o il contenuto dell'elemento in formato testo oppure se `NULL` la voce della tabella dei bordi non richiede un valore.

Esempi

R. Usare un'istruzione SELECT 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, Questo valore indica attribute-centric il mapping. 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)
);

Il set di risultati è il seguente.

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

Se la stessa SELECT istruzione viene eseguita con flag impostati su 2, che indica element-centric il mapping, i valori di CustomerID e ContactName per entrambi i clienti nel documento XML vengono restituiti come NULL, perché non sono presenti elementi denominati CustomerID o ContactName nel documento XML.

Il set di risultati è il seguente.

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, CustomerID e OrderDate del set di righe viene eseguito il mapping agli attributi del padre dei nodi identificati da rowpattern nel documento XML.
Sulla colonna ProdID del set di righe viene eseguito il mapping all'attributo ProductID, mentre sulla colonna Qty viene eseguito il mapping all'attributo Quantity dei nodi identificati in rowpattern.

Anche se il element-centric mapping viene specificato dal parametro flags , il mapping specificato in ColPattern sovrascrive questo mapping.

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'
);

Il set di risultati è il seguente.

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 WITH clausola non viene specificata, OPENXML restituisce il set di righe in un formato di tabella perimetrale.

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;

Esempi d'uso di OPENXML

Condividi tramite

OPENXML (Transact-SQL)

Sintassi

Argomenti

idoc

rowpattern

flags

SchemaDeclaration

TableName

Osservazioni:

Esempi

R. Usare un'istruzione SELECT di base con OPENXML

B. Specificare ColPattern per il mapping tra colonne e attributi XML

C. Ottenere risultati in un formato di tabella perimetrale

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

OPENXML (Transact-SQL)

Sintassi

Argomenti

idoc

rowpattern

flags

SchemaDeclaration

TableName

Osservazioni:

Esempi

R. Usare un'istruzione SELECT di base con OPENXML

B. Specificare ColPattern per il mapping tra colonne e attributi XML

C. Ottenere risultati in un formato di tabella perimetrale

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive