Esempi: utilizzo di OPENXMLExamples: Using OPENXML

Negli esempi presentati in questo argomento viene illustrato come utilizzare l'istruzione OPENXML per visualizzare un documento XML come set di righe.The examples in this topic show how OPENXML is used to create a rowset view of an XML document. Per informazioni sulla sintassi di OPENXML, vedere OPENXML (Transact-SQL).For information about the syntax of OPENXML, see OPENXML (Transact-SQL). Negli esempi vengono illustrati tutti gli aspetti dell'istruzione OPENXML, ma non ne vengono specificate le metaproprietà.The examples show all aspects of OPENXML, but do not specify metaproperties in OPENXML. Per altre informazioni su come specificare le metaproprietà in OPENXML, vedere Specificare metaproprietà in OPENXML.For more information about how to specify metaproperties in OPENXML, see Specify Metaproperties in OPENXML.

EsempiExamples

Durante il recupero dei dati è possibile usare il parametro rowpattern per identificare i nodi del documento XML che definiscono le righe.In retrieving the data, rowpattern is used to identify the nodes in the XML document that determine the rows. rowpattern viene espresso anche nel linguaggio del modello XPath usato nell'implementazione di XPath di MSXML.Additionally, rowpattern is expressed in the XPath pattern language that is used in the MSXML XPath implementation. Se ad esempio il modello termina con un elemento o con un attributo, viene creata una riga per ogni nodo di elemento o di attributo selezionato da rowpattern.For example, if the pattern ends in an element or an attribute, a row is created for each element or attribute node that is selected by rowpattern.

Il mapping predefinito è determinato dal valore di flags .The flags value provides default mapping. Se in SchemaDeclaration non è specificato ColPattern, viene usato il mapping specificato in flags .If no ColPattern is specified in the SchemaDeclaration, the mapping specified in flags is assumed. Se invece in SchemaDeclaration è specificato ColPattern , il valore flagsviene ignorato.The flags value is ignored if ColPattern is specified in SchemaDeclaration. Il valore specificato per ColPattern determina il tipo di mapping, che può essere incentrato sugli attributi o sugli elementi, nonché la modalità di gestione dei dati di overflow e di quelli non utilizzati.The specified ColPattern determines the mapping, attribute-centric or element-centric, and also the behavior in dealing with overflow and unconsumed data.

A.A. Esecuzione di una semplice istruzione SELECT con OPENXMLExecuting a simple SELECT statement with OPENXML

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer>, <Order> e <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. L'istruzione OPENXML recupera dal documento XML le informazioni sui clienti in un set di righe con due colonne, CustomerID e ContactName.The OPENXML statement retrieves customer information in a two-column rowset, CustomerID and ContactName, from the XML document.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • Il parametro rowpattern (/ROOT/Customer) identifica i nodi <Customer> da elaborare.rowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.

  • Il valore del parametro flags è impostato su 1, per indicare che il mapping è incentrato sugli attributi.The flags parameter value is set to 1 and indicates attribute-centric mapping. Per gli attributi XML viene eseguito il mapping alle colonne del set di righe definite in SchemaDeclaration.As a result, the XML attributes map to the columns in the rowset defined in SchemaDeclaration.

  • In SchemaDeclaration, nella clausola WITH, i valori specificati per ColName corrispondono ai nomi degli attributi XML associati.In SchemaDeclaration, in the WITH clause, the specified ColName values match the corresponding XML attribute names. Quindi, in SchemaDeclaration non è specificato il parametro ColPattern.Therefore, the ColPattern parameter is not specified in SchemaDeclaration.

    L'istruzione SELECT recupera quindi tutte le colonne nel set di righe specificato da OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @DocHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<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 @DocHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)  
      WITH (CustomerID  varchar(10),  
            ContactName varchar(20))  
EXEC sp_xml_removedocument @DocHandle  

Risultato:This is the result:

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

Gli elementi <Customer> non hanno sottoelementi, quindi, se si esegue la stessa istruzione SELECT con il parametro flags impostato su 2 per indicare il mapping incentrato sugli elementi, i valori CustomerID e ContactName verranno restituiti come NULL per entrambi i clienti.Because the <Customer> elements do not have any subelements, if the same SELECT statement is executed with flags set to 2 to indicate element-centric mapping, the values of CustomerID and ContactName for both the customers are returned as NULL.

La variabile @xmlDocument può essere anche di tipo xml o di tipo (n)varchar(max).The @xmlDocument can also be of xml type or of (n)varchar(max) type.

Se nel documento XML <CustomerID> e <ContactName> sono sottoelementi, il mapping incentrato sugli elementi ne recupererà i valori.If <CustomerID> and <ContactName> in the XML document are subelements, the element-centric mapping retrieves the values.

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer>  
   <CustomerID>VINET</CustomerID>  
   <ContactName>Paul Henriot</ContactName>  
   <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</CustomerID>  
   <ContactName>Carlos Gonzlez</ContactName>  
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT    *  
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)  
           WITH (CustomerID  varchar(10),  
                 ContactName varchar(20))  
EXEC sp_xml_removedocument @XmlDocumentHandle  

Risultato:This is the result:

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

L'handle del documento restituito da sp_xml_preparedocument è valido solo per la durata del batch e non per tutta la sessione.Note that the document handle returned by sp_xml_preparedocument is valid for the duration of the batch and not the session.

B.B. Impostazione del parametro ColPattern per il mapping tra le colonne del set di righe e attributi o elementi XMLSpecifying ColPattern for mapping between rowset columns and the XML attributes and elements

Questo esempio mostra l'impostazione del modello XPath nel parametro facoltativo ColPattern per specificare il mapping tra le colonne del set di righe e gli attributi o elementi XML.This example shows how the XPath pattern is specified in the optional ColPattern parameter to provide mapping between rowset columns and the XML attributes and elements.

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer>, <Order> e <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. L'istruzione OPENXML recupera dal documento XML le informazioni sui clienti e sugli ordini in un set di righe che include le colonne CustomerID, OrderDate, ProdID e Qty.The OPENXML statement retrieves customer and order information as a rowset (CustomerID, OrderDate, ProdID, and Qty) from the XML document.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • Il parametro rowpattern (/ROOT/Customer/Order/OrderDetail) identifica i nodi <OrderDetail> da elaborare.rowpattern (/ROOT/Customer/Order/OrderDetail) identifies the <OrderDetail> nodes to process.

    Nell'esempio il valore del parametro flags è impostato su 2, per indicare che il mapping è incentrato sugli elementi,For illustration, the flags parameter value is set to 2 and indicates element-centric mapping. ma tale mapping viene sovrascritto da quello specificato in ColPattern .However, the mapping specified in ColPattern overwrites this mapping. In altre parole, il modello XPath specificato in ColPattern esegue il mapping delle colonne del set di righe agli attributi, ilThat is, the XPath pattern specified in ColPattern maps the columns in the rowset to attributes. che determina un mapping incentrato sugli attributi.This results in attribute-centric mapping.

    Nella clausola WITH di SchemaDeclarationil parametro ColPattern è specificato anche con i parametri ColName e ColType .In SchemaDeclaration, in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. Il parametro ColPattern facoltativo è il modello XPath specificato e indica quanto segue:The optional ColPattern is the XPath pattern specified and indicates the following:

  • Il mapping delle colonne OrderID, CustomerID e OrderDate del set di righe viene eseguito agli attributi dell'elemento padre dei nodi identificati da rowpattern e rowpattern identifica i nodi <OrderDetail>.The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes of the parent of the nodes identified by rowpattern, and rowpattern identifies the <OrderDetail> nodes. Viene quindi eseguito il mapping delle colonne CustomerID e OrderDate agli attributi CustomerID e OrderDate dell'elemento <Order>.Therefore, the CustomerID and OrderDate columns map to CustomerID and OrderDate attributes of the <Order> element.

  • Il mapping delle colonne ProdID e Qty del set di righe viene eseguito agli attributi ProductID e Quantity dei nodi identificati in rowpattern.The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributes of the nodes identified in rowpattern.

    L'istruzione SELECT recupera quindi tutte le colonne nel set di righe specificato da OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @XmlDocumentHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<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 @XmlDocumentHandle OUTPUT, @XmlDocument  
-- Execute a SELECT stmt using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)  
WITH (OrderID     int         '../@OrderID',  
      CustomerID  varchar(10) '../@CustomerID',  
      OrderDate   datetime    '../@OrderDate',  
      ProdID      int         '@ProductID',  
      Qty         int         '@Quantity')  
EXEC sp_xml_removedocument @XmlDocumentHandle  

Risultato:This is the result:

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  

Il modello XPath specificato in ColPattern può anche specificare il mapping degli elementi XML alle colonne del set di righe,The XPath pattern specified as ColPattern can also be specified to map the XML elements to the rowset columns. che determina un mapping incentrato sugli elementi.This results in element-centric mapping. Nell'esempio seguente gli elementi <CustomerID> e <OrderDate> del documento XML sono sottoelementi dell'elemento <Orders>.In the following example, the XML document <CustomerID> and <OrderDate> are subelements of the <Orders> element. ColPattern sovrascrive il mapping specificato nel parametro flags , quindi flags non viene specificato nell'istruzione OPENXML.Because ColPattern overwrites the mapping specified in the flags parameter, the flags parameter is not specified in OPENXML.

DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument = N'<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order EmployeeID="5" >  
      <OrderID>10248</OrderID>  
      <CustomerID>VINET</CustomerID>  
      <OrderDate>1996-07-04T00:00:00</OrderDate>  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order  EmployeeID="3" >  
      <OrderID>10283</OrderID>  
      <CustomerID>LILAS</CustomerID>  
      <OrderDate>1996-08-16T00:00:00</OrderDate>  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')  
WITH (CustomerID  varchar(10)   '../CustomerID',  
      OrderDate   datetime      '../OrderDate',  
      ProdID      int           '@ProductID',  
      Qty         int           '@Quantity')  
EXEC sp_xml_removedocument @docHandle  

C.C. Combinazione di mapping incentrato sugli attributi e mapping incentrato sugli elementiCombining attribute-centric and element-centric mapping

Nell'esempio seguente il parametro flags è impostato su 3 , per indicare che verrà applicato sia il mapping incentrato sugli attributi che quello incentrato sugli elementi.In this example, the flags parameter is set to 3 and indicates that both attribute-centric and element-centric mapping will be applied. In questo caso verrà applicato per primo il mapping incentrato sugli attributi, mentre il mapping incentrato sugli elementi verrà applicato successivamente a tutte le colonne non ancora sottoposte a mapping.In this case, the attribute-centric mapping is applied first, and then element-centric mapping is applied for all the columns not yet dealt with.

DECLARE @docHandle int  
DECLARE @XmlDocument nvarchar(1000)  
SET @XmlDocument =N'<ROOT>  
<Customer CustomerID="VINET"  >  
     <ContactName>Paul Henriot</ContactName>  
   <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</ContactName>  
   <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 @docHandle OUTPUT, @XmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer',3)  
      WITH (CustomerID  varchar(10),  
            ContactName varchar(20))  
EXEC sp_xml_removedocument @docHandle  

Risultato:This is the result

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

A CustomerID viene applicato il mapping incentrato sugli attributi.The attribute-centric mapping is applied for CustomerID. Nell'elemento <Customer> non è presente l'attributo ContactName, quindiThere is no ContactName attribute in the <Customer> element. verrà applicato il mapping incentrato sugli elementi.Therefore, element-centric mapping is applied.

D.D. Impostazione della funzione XPath text() come parametro ColPatternSpecifying the text() XPath function as ColPattern

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer> e <Order>.The XML document in this example is made up of the <Customer> and <Order> elements. L'istruzione OPENXML recupera un set di righe composto dall'attributo oid dell'elemento <Order>, dall'ID dell'elemento padre del nodo identificato da rowpattern e dalla stringa del valore foglia del contenuto dell'elemento.The OPENXML statement retrieves a rowset that is made up of the oid attribute from the <Order> element, the ID of the parent of the node identified by rowpattern, and the leaf-value string of the element content.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • Il parametro rowpattern (/root/Customer/Order) identifica i nodi <Order> da elaborare.rowpattern (/root/Customer/Order) identifies the <Order> nodes to process.

  • Il valore del parametro flags è impostato su 1, per indicare che il mapping è incentrato sugli attributi.The flags parameter value is set to 1 and indicates attribute-centric mapping. Per gli attributi XML viene eseguito il mapping alle colonne del set di righe definite in SchemaDeclaration.As a result, the XML attributes map to the rowset columns defined in SchemaDeclaration.

  • In SchemaDeclaration , nella clausola WITH, i nomi delle colonne del set di righe oid e amount corrispondono ai nomi degli attributi XML associati.In SchemaDeclaration in the WITH clause, the oid and amount rowset column names match the corresponding XML attribute names. Di conseguenza, il parametro ColPattern non viene specificato.Therefore, the ColPattern parameter is not specified. Per la colonna comment del set di righe, la funzione XPath text()viene specificata come parametro ColPattern.For the comment column in the rowset, the XPath function, text(), is specified as ColPattern. Questo parametro sovrascrive il mapping incentrato sugli attributi specificato nel parametro flagse la colonna contiene la stringa del valore foglia del contenuto dell'elemento.This overwrites the attribute-centric mapping specified in flags, and the column contains the leaf-value string of the element content.

    L'istruzione SELECT recupera quindi tutte le colonne nel set di righe specificato da OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
--sample XML document  
SET @xmlDocument =N'<root>  
  <Customer cid= "C1" name="Janine" city="Issaquah">  
      <Order oid="O1" date="1/20/1996" amount="3.5" />  
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied  
      </Order>  
   </Customer>  
   <Customer cid="C2" name="Ursula" city="Oelde" >  
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue   
             white red">  
            <Urgency>Important</Urgency>  
            Happy Customer.  
      </Order>  
      <Order oid="O4" date="1/20/1996" amount="10000"/>  
   </Customer>  
</root>'  
-- Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)  
     WITH (oid     char(5),   
           amount  float,   
           comment ntext 'text()')  
EXEC sp_xml_removedocument @docHandle  

Risultato:This is the result:

oid   amount        comment  
----- -----------   -----------------------------  
O1    3.5           NULL  
O2    13.4          Customer was very satisfied  
O3    100.0         Happy Customer.  
O4    10000.0       NULL  

E.E. Impostazione del parametro TableName nella clausola WITHSpecifying TableName in the WITH clause

Questo esempio specifica TableName con la clausola WITH invece di SchemaDeclaration.This example specifies TableName in the WITH clause instead of SchemaDeclaration. Questo risulta utile se è disponibile una tabella con la struttura desiderata e se non sono necessari i modelli di colonna definiti dal parametro ColPattern.This is useful if you have a table that has the structure you want and no column patterns, ColPattern parameter, are required.

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer> e <Order>.The XML document in this example is made up of the <Customer> and <Order> elements. L'istruzione OPENXML recupera dal documento XML le informazioni sugli ordini in un set di righe con tre colonne, oid, date e amount.The OPENXML statement retrieves order information in a three-column rowset (oid, date, and amount) from the XML document.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • Il parametro rowpattern (/root/Customer/Order) identifica i nodi <Order> da elaborare.rowpattern (/root/Customer/Order) identifies the <Order> nodes to process.

  • Nella clausola WITH non è presente SchemaDeclaration,There is no SchemaDeclaration in the WITH clause. ma viene specificato un nome di tabella.Instead, a table name is specified. Come schema del set di righe viene pertanto utilizzato lo schema della tabella.Therefore, the table schema is used as the rowset schema.

  • Il valore del parametro flags è impostato su 1 , per indicare che il mapping è incentrato sugli attributi.The flags parameter value is set to 1 and indicates attribute-centric mapping. Per gli attributi degli elementi identificati da rowpatternviene quindi eseguito il mapping alle colonne del set di righe con lo stesso nome.Therefore, attributes of the elements, identified by rowpattern, map to the rowset columns with the same name.

    L'istruzione SELECT recupera quindi tutte le colonne nel set di righe specificato da OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

-- Create a test table. This table schema is used by OPENXML as the  
-- rowset schema.  
CREATE TABLE T1(oid char(5), date datetime, amount float)  
GO  
DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
-- Sample XML document  
SET @xmlDocument =N'<root>  
  <Customer cid= "C1" name="Janine" city="Issaquah">  
      <Order oid="O1" date="1/20/1996" amount="3.5" />  
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very   
             satisfied</Order>  
   </Customer>  
   <Customer cid="C2" name="Ursula" city="Oelde" >  
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue   
             white red">  
          <Urgency>Important</Urgency>  
      </Order>  
      <Order oid="O4" date="1/20/1996" amount="10000"/>  
   </Customer>  
</root>'  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument  

-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)  
     WITH T1  
EXEC sp_xml_removedocument @docHandle  

Risultato:This is the result:

oid   date                        amount  
----- --------------------------- ----------  
O1    1996-01-20 00:00:00.000     3.5  
O2    1997-04-30 00:00:00.000     13.4  
O3    1999-07-14 00:00:00.000     100.0  
O4    1996-01-20 00:00:00.000     10000.0  

F.F. Recupero di risultati in formato tabella edgeObtaining the result in an edge table format

In questo esempio nell'istruzione OPENXML non viene specificata la clausola WITH.In this example, the WITH clause is not specified in the OPENXML statement. Il set di righe generato dall'istruzione OPENXML ha pertanto un formato tabella edge.As a result, the rowset generated by OPENXML has an edge table format. L'istruzione SELECT restituisce tutte le colonne della tabella edge.The SELECT statement returns all the columns in the edge table.

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer>, <Order> e <OrderDetail>.The sample XML document in the example is made up of the <Customer>, <Order>, and <OrderDetail> elements.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • Il parametro rowpattern (/ROOT/Customer) identifica i nodi <Customer> da elaborare.rowpattern (/ROOT/Customer) identifies the <Customer> nodes to process.

  • La clausola WITH è stata omessaThe WITH clause is not provided. e OPENXML restituisce pertanto un set di righe in formato tabella edge.Therefore, OPENXML returns the rowset in an edge table format.

    L'istruzione SELECT recupera quindi tutte le colonne della tabella edge.The SELECT statement then retrieves all the columns in the edge table.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
SET @xmlDocument = N'<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 @docHandle OUTPUT, @xmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer')  

EXEC sp_xml_removedocument @docHandle  

Il risultato viene restituito sotto forma di tabella edge.The result is returned as an edge table. È possibile creare query da eseguire sulla tabella edge per recuperare informazioni specifiche.You can write queries against the edge table to obtain information. Esempio:For example:

  • La query seguente restituisce il numero di nodi Customer presenti nel documento.The following query returns the number of Customer nodes in the document. Poiché non è stata specificata la clausola WITH, l'istruzione OPENXML restituisce una tabella edge.Because the WITH clause is not specified, OPENXML returns an edge table. L'istruzione SELECT esegue la query sulla tabella edge.The SELECT statement queries the edge table.

    SELECT count(*)  
    FROM OPENXML(@docHandle, '/')  
    WHERE localname = 'Customer'  
    
  • La query seguente restituisce i nomi locali dei nodi XML di tipo elemento.The following query returns the local names of XML nodes of element type.

    SELECT distinct localname   
    FROM OPENXML(@docHandle, '/')   
    WHERE nodetype = 1   
    ORDER BY localname  
    

G.G. Impostazione di un parametro rowpattern che termina con un attributoSpecifying rowpattern ending with an attribute

Il documento XML utilizzato nell'esempio è costituito da elementi <Customer>, <Order> e <OrderDetail>.The XML document in this example is made up of the <Customer>, <Order>, and <OrderDetail> elements. L'istruzione OPENXML recupera dal documento XML le informazioni sui dettagli degli ordini in un set di righe con tre colonne, ProductID, Quantity e OrderID.The OPENXML statement retrieves information about the order details in a three-column rowset (ProductID, Quantity, and OrderID) from the XML document.

Prima di tutto, viene chiamata la stored procedure sp_xml_preparedocument per ottenere un handle di documento.First, the sp_xml_preparedocument is called to obtain a document handle. L'handle del documento viene quindi passato a OPENXML.This document handle is passed to OPENXML.

Nell'istruzione OPENXML si noti quanto segue:The OPENXML statement illustrates the following:

  • rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) termina con un attributo XML, ProductID.rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) ends with an XML attribute, ProductID. Nel set di righe risultante viene creata una riga per ogni nodo di attributo selezionato nel documento XML.In the resulting rowset, a row is created for each attribute node selected in the XML document.

  • In questo esempio il parametro flags non è specificatoIn this example, the flags parameter is not specified. e i mapping vengono definiti dal parametro ColPattern .Instead, the mappings are specified by the ColPattern parameter.

    Nella clausola WITH di SchemaDeclaration il parametro ColPattern è specificato anche con i parametri ColName e ColType .In SchemaDeclaration in the WITH clause, ColPattern is also specified with the ColName and ColType parameters. Il parametro ColPattern facoltativo è il modello XPath specificato e indica quanto segue:The optional ColPattern is the XPath pattern specified to indicate the following:

  • Il modello XPath (.) specificato come ColPattern per la colonna ProdID nel set di righe identifica il nodo di contesto, ovvero il nodo corrente.The XPath pattern (.) specified as ColPattern for the ProdID column in the rowset identifies the context node, current node. Il valore specificato per rowpattern è l'attributo ProductID dell'elemento <OrderDetail>.As per the rowpattern specified, it is the ProductID attribute of the <OrderDetail> element.

  • Il valore di ColPattern, ../@Quantity, specificato per la colonna Qty nel set di righe identifica l'attributo Quantity del nodo padre, <OrderDetail>, del nodo di contesto, <ProductID.The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifies the Quantity attribute of the parent, <OrderDetail>, node of the context node, <ProductID>.

  • Analogamente, il valore di ColPattern, ../../@OrderID, specificato per la colonna OID nel set di righe identifica l'attributo OrderID dell'elemento padre, <Order>, del nodo padre del nodo di contesto.Similarly, the ColPattern, ../../@OrderID, specified for the OID column in the rowset identifies the OrderID attribute of the parent, <Order>, of the parent node of the context node. Il nodo padre è <OrderDetail>, mentre il nodo di contesto è <ProductID>.The parent node is <OrderDetail>, and the context node is <ProductID>.

    L'istruzione SELECT recupera quindi tutte le colonne nel set di righe specificato da OPENXML.The SELECT statement then retrieves all the columns in the rowset provided by OPENXML.

DECLARE @docHandle int  
DECLARE @xmlDocument nvarchar(1000)  
--Sample XML document  
SET @xmlDocument =N'<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 @docHandle OUTPUT, @xmlDocument  
-- Execute a SELECT statement using OPENXML rowset provider.  
SELECT *  
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')  
       WITH ( ProdID  int '.',  
              Qty     int '../@Quantity',  
              OID     int '../../@OrderID')  
EXEC sp_xml_removedocument @docHandle  

Risultato:This is the result:

ProdID      Qty         OID  
----------- ----------- -------   
11          12          10248  
42          10          10248  
72          3           10283  

H.H. Impostazione di un documento XML con più nodi di testoSpecifying an XML document that has multiple text nodes

Se in un documento XML sono presenti più nodi di testo, un'istruzione SELECT con un parametro ColPatterndi tipo text()restituirà solo il primo, invece di tutti i nodi di testo.If you have multiple text nodes in an XML document, a SELECT statement with a ColPattern, text(), returns only the first text node, instead of all of them. Esempio:For example:

DECLARE @h int  
EXEC sp_xml_preparedocument @h OUTPUT,  
         N'<root xmlns:a="urn:1">  
           <a:Elem abar="asdf">  
             T<a>a</a>U  
           </a:Elem>  
         </root>',  
         '<ns xmlns:b="urn:1" />'  

SELECT * FROM openxml(@h, '/root/b:Elem')  
      WITH (Col1 varchar(20) 'text()')  
EXEC sp_xml_removedocument @h  

L'istruzione SELECT restituisce T , invece di TaU.The SELECT statement returns T as the result, and not TaU.

I.I. Impostazione del tipo di dati xml nella clausola WITHSpecifying the xml data type in the WITH clause

Nella clausola WITH un modello di colonna con mapping a una colonna con tipo di dati xml tipizzato o non tipizzato deve restituire una sequenza vuota oppure una sequenza di elementi, istruzioni di elaborazione, nodi di testo e commenti.In the WITH clause, a column pattern that is mapped to the xml data type column, whether typed or untyped, must return either an empty sequence or a sequence of elements, processing instructions, text nodes, and comments. Viene eseguito il cast dei dati a un tipo di dati xml .The data is cast to an xml data type.

Nell'esempio seguente la dichiarazione dello schema di tabella nella clausola WITH include colonne di tipo xml .In the following example, the table schema declaration in the WITH clause includes xml type columns.

DECLARE @h int  
DECLARE @x xml  
set @x = '<Root>  
  <row id="1"><lname>Duffy</lname>  
   <Address>  
            <Street>111 Maple</Street>  
            <City>Seattle</City>  
   </Address>  
  </row>  
  <row id="2"><lname>Wang</lname>  
   <Address>  
            <Street>222 Pine</Street>  
            <City>Bothell</City>  
   </Address>  
  </row>  
</Root>'  

EXEC sp_xml_preparedocument @h output, @x  
SELECT *  
FROM   OPENXML (@h, '/Root/row', 10)  
      WITH (id int '@id',  

            lname    varchar(30),  
            xmlname  xml 'lname',  
            OverFlow xml '@mp:xmltext')  
EXEC sp_xml_removedocument @h  

In particolare, viene passata una variabile di tipo xml, @x, alla funzione sp_xml_preparedocument().Specifically, you are passing an xml type variable (@x) to the sp_xml_preparedocument() function.

Risultato:This is the result:

id  lname   xmlname                   OverFlow  
--- ------- ------------------------------ -------------------------------  
1   Duffy   <lname>Duffy</lname>  <row><Address>  
                                   <Street>111 Maple</Street>  
                                   <City>Seattle</City>  
                                  </Address></row>  
2   Wang    <lname>Wang</lname>   <row><Address>  
                                    <Street>222 Pine</Street>  
                                    <City>Bothell</City>  
                                   </Address></row>  

Dal risultato si noti quanto segue:Note the following from the result:

  • Il valore della colonna lname di tipo varchar(30) viene recuperato dall'elemento <lname> corrispondente.For the lname column of varchar(30) type, its value is retrieved from the corresponding <lname> element.

  • Come valore della colonna xmlname di tipo xml, viene restituito l'elemento con lo stesso nome.For the xmlname column of xml type, the same name element is returned as its value.

  • Il flag è impostato su 10,The flag is set to 10. ovvero 2 + 8, dove 2 indica il mapping incentrato sugli elementi e 8 indica che solo i dati XML non utilizzati devono essere aggiunti alla colonna OverFlow definita nella clausola WITH.The 10 means 2 + 8, where 2 indicates element-centric mapping and 8 indicates that only unconsumed XML data should be added to the OverFlow column defined in the WITH clause. Se si imposta il flag su 2, nella colonna OverFlow specificata nella clausola WITH verrà copiato l'intero documento XML.If you set the flag to 2, the whole XML document is copied to the OverFlow column that is specified in the WITH clause.

  • Se la colonna specificata nella clausola WITH è una colonna XML tipizzata e l'istanza XML non è conforme allo schema, verrà restituito un errore.In case the column in the WITH clause is a typed XML column and the XML instance does not confirm to the schema, an error is returned.

J.J. Recupero di singoli valori da attributi multivaloreRetrieving individual values from multivalued attributes

Un documento XML può includere attributi multivalore.An XML document can have attributes that are multivalued. L'attributo IDREFS , ad esempio, può essere multivalore.For example, the IDREFS attribute can be multivalued. In un documento XML gli attributi multivalore vengono specificati come stringa, con i valori separati da spazi.In an XML document, the multivalued attribute values are specified as a string, with the values separated by a space. Nel documento XML seguente l'attributo attends dell'elemento <Student> e l'attributo attendedBy dell'elemento <Class> sono multivalore.In the following XML document, the attends attribute of the <Student> element and the attendedBy attribute of <Class> are multivalued. Per recuperare i singoli valori da un attributo XML multivalore e archiviare ogni valore in una riga distinta del database, sono necessarie ulteriori operazioni,Retrieving individual values from a multivalued XML attribute and storing each value in a separate row in the database requires additional work. illustrate in questo esempio.This example shows the process.

Questo documento XML di esempio è costituito dagli elementi seguenti:This sample XML document is made up of the following elements:

  • <Student><Student>

    Attributi id (ID dello studente), namee attends .The id (student ID), name, and attends attributes. L'attributo attends è multivalore.The attends attribute is a multivalued attribute.

  • <Class><Class>

    Attributi id (ID della classe), namee attendedBy .The id (class ID), name, and attendedBy attributes. L'attributo attendedBy è multivalore.The attendedBy attribute is a multivalued attribute.

    L'attributo attends dell'elemento <Student> e l'attributo attendedBy dell'elemento <Class> rappresentano una relazione m:n tra le tabelle Student e Class.The attends attribute in <Student> and the attendedBy attribute in <Class> represent a m:n relationship between the Student and Class tables. Uno studente può frequentare più classi e una classe può essere frequentata da più studenti.A student can take many classes and a class can have many students.

    Si supponga che sia necessario suddividere il documento e salvarlo nel database, come illustrato di seguito:Assume that you want to shred this document and save it in the database as shown in the following:

  • Salvare i dati dell'elemento <Student> nella tabella Students.Save the <Student> data in the Students table.

  • Salvare i dati dell'elemento <Class> nella tabella Courses.Save the <Class> data in the Courses table.

  • Salvare nella tabella CourseAttendence i dati della relazione m:n tra le tabelle Student e Class.Save the m:n relationship data, between Student and Class, in the CourseAttendence table. Per estrarre i valori sono necessarie ulteriori operazioni.Additional work is required to extract the values. Per recuperare le informazioni e archiviarle nella tabella, utilizzare le stored procedure seguenti:To retrieve this information and store it in the table, use these stored procedures:

    • Insert_Idrefs_ValuesInsert_Idrefs_Values

      Inserisce gli ID di corsi e studenti nella tabella CourseAttendence.Inserts the values of course ID and student ID in the CourseAttendence table.

    • Extract_idrefs_valuesExtract_idrefs_values

      Estrae gli ID dei singoli studenti da ogni elemento <Course>.Extracts the individual student IDs from each <Course> element. Per recuperare questi valori viene utilizzata una tabella edge.An edge table is used to retrieve these values.

    I passaggi necessari sono i seguenti:Here are the steps:

-- Create these tables:  
DROP TABLE CourseAttendance  
DROP TABLE Students  
DROP TABLE Courses  
GO  
CREATE TABLE Students(  
                id   varchar(5) primary key,  
                name varchar(30)  
                )  
GO  
CREATE TABLE Courses(  
               id       varchar(5) primary key,  
               name     varchar(30),  
               taughtBy varchar(5)  
)  
GO  
CREATE TABLE CourseAttendance(  
             id         varchar(5) references Courses(id),  
             attendedBy varchar(5) references Students(id),  
             constraint CourseAttendance_PK primary key (id, attendedBy)  
)  
go  
-- Create these stored procedures:  
DROP PROCEDURE f_idrefs  
GO  
CREATE PROCEDURE f_idrefs  
    @t      varchar(500),  
    @idtab  varchar(50),  
    @id     varchar(5)  
AS  
DECLARE @sp int  
DECLARE @att varchar(5)  
SET @sp = 0  
WHILE (LEN(@t) > 0)  
BEGIN   
    SET @sp = CHARINDEX(' ', @t+ ' ')  
    SET @att = LEFT(@t, @sp-1)  
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')')  
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp)  
END  
Go  

DROP PROCEDURE fill_idrefs  
GO  
CREATE PROCEDURE fill_idrefs   
    @xmldoc     int,  
    @xpath      varchar(100),  
    @from       varchar(50),  
    @to         varchar(50),  
    @idtable    varchar(100)  
AS  
DECLARE @t varchar(500)  
DECLARE @id varchar(5)  

/* Temporary Edge table */  
SELECT *   
INTO #TempEdge   
FROM OPENXML(@xmldoc, @xpath)  

DECLARE fillidrefs_cursor CURSOR FOR  
    SELECT CAST(iv.text AS nvarchar(200)) AS id,  
           CAST(av.text AS nvarchar(4000)) AS refs  
    FROM   #TempEdge c, #TempEdge i,  
           #TempEdge iv, #TempEdge a, #TempEdge av  
    WHERE  c.id = i.parentid  
    AND    UPPER(i.localname) = UPPER(@from)  
    AND    i.id = iv.parentid  
    AND    c.id = a.parentid  
    AND    UPPER(a.localname) = UPPER(@to)  
    AND    a.id = av.parentid  

OPEN fillidrefs_cursor  
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t  
WHILE (@@FETCH_STATUS <> -1)  
BEGIN  
    IF (@@FETCH_STATUS <> -2)  
    BEGIN  
        execute f_idrefs @t, @idtable, @id  
    END  
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t  
END  
CLOSE fillidrefs_cursor  
DEALLOCATE fillidrefs_cursor  
Go  
-- This is the sample document that is shredded and the data is stored in the preceding tables.  
DECLARE @h int  
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>  
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />  
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />  
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />  
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />  
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />  
  <Student id = "s6" name = "Student6" />  

  <Class id = "c1" name = "Intro to Programming"   
         attendedBy = "s1 s4 s5" />  
  <Class id = "c2" name = "Databases"   
         attendedBy = "s2 s3" />  
  <Class id = "c3" name = "Operating Systems"   
         attendedBy = "s1 s4 s5" />  
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />  
  <Class id = "c5" name = "Algorithms and Graphs"   
         attendedBy =  "s4 s5"/>  
  <Class id = "c6" name = "Power and Pragmatism"   
         attendedBy = "s1 s3 s5" />  
</Data>'  

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students  

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses  
/* Using the edge table */  
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance'  

SELECT * FROM Students  
SELECT * FROM Courses  
SELECT * FROM CourseAttendance  

EXECUTE sp_xml_removedocument @h  

K.K. Recupero di dati binari da dati con codifica Base64 in un valore XMLRetrieving binary from base64 encoded data in XML

Nei valori XML sono spesso inclusi dati binari con codifica Base64.Binary data is frequently included in XML using base64 encoding. Quando si suddivide un valore XML di questo tipo tramite l'istruzione OPENXML, vengono restituiti dati con codifica Base64.When you shred this XML by using OPENXML, you receive the base64 encoded data. In questo esempio viene illustrato come convertire in formato binario i dati con codifica Base64.This example shows how you can convert the base64 encoded data back to binary.

  • Creare una tabella con dati binari di esempio.Create a table with sample binary data.

  • Utilizzare una query FOR XML e l'opzione BINARY BASE64 per costruire il valore XML che include dati binari con codifica Base64.Use a FOR XML query and the BINARY BASE64 option to construct XML that has the binary data encoded as base64.

  • Suddividere il valore XML tramite un'istruzione OPENXML.Shred the XML by using OPENXML. L'istruzione OPENXML restituirà dati con codifica Base64.The data returned by OPENXML will be base64 encoded data. Chiamare quindi la funzione .value per convertire i dati in formato binario.Next, call the .value function to convert it back to binary.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100))  
go  
-- Insert sample binary data  
INSERT T VALUES(1, 0x1234567890)   
go  
 -- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)  
SELECT * FROM T  
FOR XML AUTO, BINARY BASE64  
go  
-- result  
-- <T Col1="1" Col2="EjRWeJA="/>  

-- Now shredd the sample XML using OPENXML.   
-- Call the .value function to convert   
-- the base64 encoded data returned by OPENXML to binary.  
DECLARE @h int ;  
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>' ;  
SELECT Col1,   
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol   
FROM openxml(@h, '/T')   
WITH (Col1 integer, Col2 varchar(max)) ;  
EXEC sp_xml_removedocument @h ;  
GO  

Di seguito è riportato il risultato. I dati binari restituiti sono i dati binari originali della tabella T.The binary data returned is the original binary data in table T.

Col1        BinaryCol  
----------- ---------------------  
1           0x1234567890  

Vedere ancheSee Also

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