OPENXML (Transact-SQL)

  • Articolo

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 Transact-SQL che supportano provider di set di righe quali una tabella, una vista o la funzione OPENROWSET.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

  • idoc
    Handle di documento della rappresentazione interna di un documento XML. Tale rappresentazione viene creata tramite sp_xml_preparedocument.
  • rowpattern
    Modello XPath utilizzato per identificare i nodi da elaborare come righe nel documento XML di cui viene passato l'handle nel parametro idoc.

  • flags
    Indica il mapping da utilizzare tra i dati XML e il set di righe relazionale e la modalità di riempimento della colonna spill-over. 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 utilizzato il mapping incentrato sugli attributi. Può essere utilizzato in combinazione con XML_ELEMENTS. In questo caso, il mapping incentrato sugli attributi verrà applicato per primo; il mapping incentrato sugli elementi verrà applicato successivamente a tutte le colonne non ancora elaborate.

    2

    Viene utilizzato il mapping incentrato sugli elementi. Può essere utilizzato in combinazione con XML_ATTRIBUTES. In questo caso, il mapping incentrato sugli attributi verrà applicato per primo; il mapping incentrato sugli elementi verrà applicato successivamente a tutte le colonne non ancora elaborate.

    8

    Può essere utilizzato in combinazione (OR logico) con XML_ATTRIBUTES o XML_ELEMENTS. Nel contesto di operazioni di recupero, questo flag indica che i dati consumati non devono essere copiati nella proprietà di overflow @mp:xmltext.

  • SchemaDeclaration
    Definizione dello schema nel formato seguente: 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 generale XPath facoltativo che indica il criterio di 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 di flags.

      Nel caso di mapping incentrato sugli attributi o sugli elementi, il modello XPath specificato come ColPattern consente di specificare la natura specifica del mapping che sovrascrive o migliora il mapping predefinito indicato in flags.

      Il modello XPath generale specificato come ColPattern supporta inoltre le metaproprietà.

    • MetaProperty
      Una delle metaproprietà definite 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. In questo modo viene restituita una quantità di informazioni maggiore rispetto alle informazioni visibili nella rappresentazione testuale.
  • TableName
    Nome di tabella che è possibile assegnare in sostituzione di SchemaDeclaration se una tabella avente lo schema desiderato esiste già e non sono necessari modelli di colonna specifici.

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 specifica del documento XML, ad esempio nomi di elementi/attributi, gerarchia del documento, spazi dei nomi, istruzioni di elaborazione e così via, in un'unica 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 principale è 0. I valori di ID negativi ID 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 è un nodo testo, il padre corrispondente 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 integer 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 è associato a 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

Tipo di dati effettivo della riga dell'elemento o attributo. In caso contrario, restituisce 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.

text

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

A. Utilizzo di un'istruzione SELECT semplice 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. Gli attributi XML vengono pertanto mappati 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
DECLARE @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))

Set di risultati:

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

Se si esegue la stessa istruzione SELECT con il parametro flags impostato su 2, a indicare il mapping incentrato sugli elementi, per CustomerID e ContactName di entrambi i clienti del documento XML viene restituito NULL, in quanto agli elementi <Customers> non è associato alcun sottoelemento.

Set di risultati:

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. Utilizzo di ColPattern per l'impostazione del mapping tra colonne e attributi XML

La query seguente restituisce gli attributi ID cliente, data dell'ordine, ID e quantità dal documento XML. rowpattern identifica gli elementi <OrderDetails>. ProductID e Quantity sono gli attributi dell'elemento <OrderDetails>. Tuttavia, OrderID, CustomerID e OrderDate sono gli attributi dell'elemento padre (<Orders>).

Viene specificato il parametro facoltativo ColPattern a indicare quanto segue:

  • Le colonne OrderID, CustomerID e OrderDate del set di righe sono mappate agli attributi del padre dei nodi identificati da rowpattern nel documento XML.
  • La colonna ProdID del set di righe è mappata all'attributo ProductID, mentre la colonna Qty è mappata all'attributo Quantity dei nodi identificati in rowpattern.

Il mapping incentrato sugli elementi specificato nel parametro flags viene sovrascritto dal mapping specificato in ColPattern.

DECLARE @idoc int
DECLARE @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">
   <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')

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. Restituzione di risultati in formato di tabella edge

Il documento XML utilizzato nell'esempio seguente è composto dagli elementi <Customers>, <Orders> e <Order_0020_Details>. Viene innanzitutto richiamata la stored procedure sp_xml_preparedocument 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 viene 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
DECLARE @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

Vedere anche

Altre risorse

Utilizzo di OPENXML

Guida in linea e informazioni

Assistenza su SQL Server 2005