OPENXML (Transact-SQL)

  • Articolo

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: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 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 0ID . 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;

Contenuto correlato