Promuovere i valori XML di uso frequente mediante colonne calcolatePromote Frequently Used XML Values with Computed Columns

Se le query vengono eseguite principalmente su un piccolo numero di valori di attributi e di elementi, sarà possibile promuovere tali quantità al livello di colonne relazionali.If queries are made principally on a small number of element and attribute values, you may want to promote those quantities into relational columns. Ciò risulta utile quando le query vengono eseguite su una piccola parte dei dati XML mentre viene recuperata l'intera istanza XML.This is helpful when queries are issued on a small part of the XML data while the whole XML instance is retrieved. Non è necessario creare un indice XML sulla colonna XML,Creating an XML index on the XML column is not required. ma è possibile indicizzare la colonna promossa.Instead, the promoted column can be indexed. Le query devono essere scritte in modo da utilizzare la colonna promossa,Queries must be written to use the promoted column. poiché il Query Optimizer non reindirizza alla colonna promossa le query eseguite sulla colonna XML.That is, the query optimizer does not target again the queries on the XML column to the promoted column.

La colonna promossa può essere una colonna calcolata nella stessa tabella oppure una colonna separata e gestita dall'utente in un'altra tabella.The promoted column can be a computed column in the same table or it can be a separate, user-maintained column in a table. Ciò è sufficiente quando i valori singleton vengono promossi da ogni istanza XML.This is sufficient when singleton values are promoted from each XML instance. Per le proprietà multivalore, invece, è necessario creare una tabella separata per la proprietà, come illustrato nella sezione seguente.However, for multi-valued properties, you have to create a separate table for the property, as described in the following section.

Colonna calcolata basata sul tipo di dati xmlComputed Column Based on the xml Data Type

Per creare una colonna calcolata è possibile usare una funzione definita dall'utente che richiama metodi per il tipo di dati xml .A computed column can be created by using a user-defined function that invokes xml data type methods. Il tipo della colonna calcolata può essere qualsiasi tipo SQL, incluso il tipo XML,The type of the computed column can be any SQL type, including XML. come illustrato nell'esempio seguente.This is illustrated in the following example.

Esempio: colonna calcolata basata su un metodo per il tipo di dati xmlExample: Computed Column Based on the xml Data Type Method

Creare la funzione definita dall'utente per il codice ISBN di un libro:Create the user-defined function for a book ISBN number:

CREATE FUNCTION udf_get_book_ISBN (@xData xml)  
RETURNS varchar(20)  
BEGIN  
   DECLARE @ISBN   varchar(20)  
   SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')  
   RETURN @ISBN   
END  

Aggiungere alla tabella una colonna calcolata per il codice ISBN:Add a computed column to the table for the ISBN:

ALTER TABLE      T  
ADD   ISBN AS dbo.udf_get_book_ISBN(xCol)  

La colonna calcolata può essere indicizzata come di consueto.The computed column can be indexed in the usual way.

Esempio: query su una colonna calcolata basata sui metodi per il tipo di dati xmlExample: Queries on a Computed Column Based on xml Data Type Methods

Per ottenere l'elemento <book> il cui ISBN è 0-7356-1588-2:To obtain the <book> whose ISBN is 0-7356-1588-2:

SELECT xCol  
FROM   T  
WHERE  xCol.exist('/book/@ISBN[. = "0-7356-1588-2"]') = 1  

La query sulla colonna XML può essere riscritta in modo da utilizzare la colonna calcolata, come illustrato di seguito:The query on the XML column can be rewritten to use the computed column as follows:

SELECT xCol  
FROM   T  
WHERE  ISBN = '0-7356-1588-2'  

È possibile creare una funzione definita dall'utente per restituire il tipo di dati xml e una colonna calcolata,You can create a user-defined function to return the xml data type and a computed column by using the user-defined function. ma non è possibile creare un indice XML sulla colonna XML calcolata.However, you cannot create an XML index on the computed, XML column.

Creazione di tabelle di proprietàCreating Property Tables

È possibile promuovere alcune delle proprietà multivalore dei dati XML fino a ottenere una o più tabelle, creare indici su tali tabelle e modificare la destinazione delle query in modo da utilizzarle.You may want to promote some of the multivalued properties from your XML data into one or more tables, create indexes on those tables, and target again your queries to use them. Un tipico scenario è quello in cui la maggior parte del carico di lavoro delle query è costituita da un piccolo numero di proprietà.A typical scenario is one in which a small number of properties covers most of your query workload. È possibile eseguire le operazioni seguenti:You can do the following:

  • Creare una o più tabelle in cui inserire le proprietà multivalore.Create one or more tables to hold the multivalued properties. Risulta conveniente archiviare una proprietà per tabella e duplicare la chiave primaria della tabella di base nelle tabelle di proprietà, per poter eseguire il join all'indietro alla tabella di base.You may find it convenient to store one property per table and duplicate the primary key of the base table in the property tables for back joining with the base table.

  • Se si desidera mantenere l'ordine relativo delle proprietà, sarà necessario introdurre una colonna separata per l'ordine relativo.If you want to maintain the relative order of the properties, you have to introduce a separate column for the relative order.

  • Creare trigger sulla colonna XML per eseguire operazioni di manutenzione delle tabelle di proprietà.Create triggers on the XML column to maintain the property tables. Nell'ambito dei trigger, eseguire una delle operazioni seguenti:Within the triggers, do one of the following:

    • Usare i metodi per il tipo di dati xml , ad esempio nodes() e value(), per inserire ed eliminare righe nelle tabelle di proprietà.Use xml data type methods, such as nodes() and value(), to insert and delete rows of the property tables.

    • Creare funzioni di flusso con valori di tabella in Common Language Runtime (CLR) per inserire ed eliminare righe nelle tabelle di proprietà.Create streaming table-valued functions in the common language runtime (CLR) to insert and delete rows of the property tables.

    • Scrivere query per l'accesso SQL alle tabelle di proprietà e per l'accesso XML alla colonna XML nella tabella di base, utilizzandone la chiave primaria per creare join tra le tabelle.Write queries for SQL access to the property tables and for XML access to the XML column in the base table, with joins between the tables by using their primary key.

Esempio: creazione di una tabella di proprietàExample: Create a Property Table

Si supponga ad esempio di voler promuovere i nomi degli autori.For illustration, assume that you want to promote the first name of the authors. Poiché un libro può avere più autori, quel nome è una proprietà multivalore.Books have one or more authors, so that first name is a multivalued property. Ogni nome è archiviato in una riga separata di una tabella di proprietà.Each first name is stored in a separate row of a property table. La chiave primaria della tabella di base viene duplicata nella tabella di proprietà per consentire il join all'indietro.The primary key of the base table is duplicated in the property table for back join.

create table tblPropAuthor (propPK int, propAuthor varchar(max))  

Esempio: creazione di una funzione definita dall'utente per la generazione di un set di righe da un'istanza XMLExample: Create a User-defined Function to Generate a Rowset from an XML Instance

La seguente funzione con valori di tabella, udf_XML2Table, accetta un valore di chiave primaria e un'istanza XML.The following table-valued function, udf_XML2Table, accepts a primary key value and an XML instance. Recupera il nome di tutti gli autori degli elementi <book> e restituisce un set di righe composto da coppie di chiave primaria e nome.It retrieves the first name of all authors of the <book> elements and returns a rowset of primary key, first name pairs.

create function udf_XML2Table (@pk int, @xCol xml)  
returns @ret_Table table (propPK int, propAuthor varchar(max))  
with schemabinding  
as  
begin  
      insert into @ret_Table   
      select @pk, nref.value('.', 'varchar(max)')  
      from   @xCol.nodes('/book/author/first-name') R(nref)  
      return  
end  

Esempio: creazione di trigger per il popolamento di una tabella di proprietàExample: Create Triggers to Populate a Property Table

Il trigger di inserimento consente di inserire righe nella tabella di proprietà:The insert trigger inserts rows into the property table:

create trigger trg_docs_INS on T for insert  
as  
      declare @wantedXML xml  
      declare @FK int  
      select @wantedXML = xCol from inserted  
      select @FK = PK from inserted  

   insert into tblPropAuthor  
   select * from dbo.udf_XML2Table(@FK, @wantedXML)  

Il trigger di eliminazione consente di eliminare righe dalla tabella di proprietà, in base al valore della chiave primaria delle righe eliminate:The delete trigger deletes the rows from the property table based on the primary key value of the deleted rows:

create trigger trg_docs_DEL on T for delete  
as  
   declare @FK int  
   select @FK = PK from deleted  
   delete tblPropAuthor where propPK = @FK  

Il trigger di aggiornamento consente di eliminare dalla tabella di proprietà le righe esistenti corrispondenti all'istanza XML aggiornata e di inserire nuove righe nella tabella stessa:The update trigger deletes the existing rows in the property table corresponding to the updated XML instance and inserts new rows into the property table:

create trigger trg_docs_UPD  
on T  
for update  
as  
if update(xCol) or update(pk)  
begin  
      declare @FK int  
      declare @wantedXML xml  
      select @FK = PK from deleted  
      delete tblPropAuthor where propPK = @FK  

   select @wantedXML = xCol from inserted  
   select @FK = pk from inserted  

   insert into tblPropAuthor   
      select * from dbo.udf_XML2Table(@FK, @wantedXML)  
end  

Esempio: ricerca di istanze XML i cui autori hanno lo stesso nomeExample: Find XML Instances Whose Authors Have the Same First Name

È possibile creare la query nella colonna XMLThe query can be formed on the XML column. oppure è possibile ricercare il nome "David" nella tabella di proprietà ed eseguire un join all'indietro alla tabella di base, per restituire l'istanza XML.Alternatively, it can search the property table for first name "David" and perform a back join with the base table to return the XML instance. Esempio:For example:

SELECT xCol   
FROM     T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK  
WHERE    tblPropAuthor.propAuthor = 'David'  

Esempio: soluzione che utilizza una funzione di flusso CLR con valori di tabellaExample: Solution Using the CLR Streaming Table-valued Function

Per creare questa soluzione è necessario eseguire i passaggi seguenti:This solution is made up of the following steps:

  1. Definire una classe CLR, SqlReaderBase, che implementa ISqlReader e genera un output di flusso valutato a livello di tabella, applicando un'espressione di percorso a un'istanza XML.Define a CLR class, SqlReaderBase, that implements ISqlReader and generates a streaming, table-valued output by applying a path expression on an XML instance.

  2. Creare un assembly e una funzione Transact-SQL definita dall'utente per avviare la classe CLR.Create an assembly and a Transact-SQL user-defined function to start the CLR class.

  3. Definire i trigger di inserimento, aggiornamento ed eliminazione utilizzando la funzione definita dall'utente per la manutenzione delle tabelle di proprietà.Define the insert, update, and delete triggers by using the user-defined function to maintain a property tables.

    A tale scopo è innanzitutto necessario creare la funzione CLR di flusso.To do this, you first create the streaming CLR function. Il tipo di dati xml viene esposto come classe SqlXml gestita in ADO.NET e supporta il metodo CreateReader() , che restituisce un oggetto XmlReader.The xml data type is exposed as a managed class SqlXml in ADO.NET and supports the CreateReader() method that returns an XmlReader.

Nota

Il codice di esempio in questa sezione utilizza XPathDocument e XPathNavigator,The example code in this section uses XPathDocument and XPathNavigator. che impongono il caricamento in memoria di tutti i documenti XML.These force you to load all the XML documents into memory. Se nella propria applicazione si utilizza codice analogo per elaborare numerosi documenti XML di grandi dimensioni, sarà necessario ricordare che tale codice non è scalabile.If you are using similar code in your application to process several large XML documents, this code is not scalable. Se possibile, è preferibile utilizzare allocazioni di memoria di piccole dimensioni e utilizzare interfacce di flusso.Instead, keep memory allocations small and use streaming interfaces whenever possible. Per altre informazioni sulle prestazioni, vedere Architettura dell'integrazione con CLR.For more information about performance, see Architecture of CLR Integration.

public class c_streaming_xml_tvf {  
   public static ISqlReader streaming_xml_tvf   
(SqlXml xmlDoc, string pathExpression) {  
      return (new TestSqlReaderBase (xmlDoc, pathExpression));  
   }  
}  

// Class that implements ISqlReader  
public class TestSqlReaderBase : ISqlReader {  
XPathNodeIterator m_iterator;           
   public SqlChars FirstName;  
// Metadata for current resultset  
private SqlMetaData[] m_rgSqlMetaData;        

   public TestSqlReaderBase (SqlXml xmlDoc, string pathExpression) {     
      // Variables for XPath navigation  
      XPathDocument xDoc;  
      XPathNavigator xNav;  
      XPathExpression xPath;  

      // Set sql metadata  
      m_rgSqlMetaData = new SqlMetaData[1];  
      m_rgSqlMetaData[0] = new SqlMetaData ("FirstName",    
SqlDbType.NVarChar,50);     

      //Set up the Navigator  
      if (!xmlDoc.IsNull)  
          xDoc = new XPathDocument (xmlDoc.CreateReader());  
      else  
          xDoc = new XPathDocument ();  
      xNav = xDoc.CreateNavigator();  
      xPath = xNav.Compile (pathExpression);  
      m_iterator = xNav.Select(xPath);  
   }  
   public bool Read() {  
      bool moreRows = true;  
      if (moreRows = m_iterator.MoveNext())  
         FirstName = new SqlChars (m_iterator.Current.Value);  
      return moreRows;  
   }  
}  

Creare quindi un assembly e una funzione Transact-SQLTransact-SQL definita dall'utente, SQL_streaming_xml_tvf (non illustrata), che corrisponde alla funzione CLR streaming_xml_tvf.Next, create an assembly and a Transact-SQLTransact-SQL user-defined function, SQL_streaming_xml_tvf (not shown), that corresponds to the CLR function, streaming_xml_tvf. La funzione definita dall'utente viene utilizzata per definire la funzione con valori di tabella, CLR_udf_XML2Table, per la generazione del set di righe:The user-defined function is used to define the table-valued function, CLR_udf_XML2Table, for rowset generation:

create function CLR_udf_XML2Table (@pk int, @xCol xml)  
returns @ret_Table table (FK int, FirstName varchar(max))  
with schemabinding  
as  
begin  
      insert into @ret_Table   
   select @pk, FirstName   
   FROM   SQL_streaming_xml_tvf (@xCol, '/book/author/first-name')  
      return  
end  

Definire infine i trigger come illustrato nell'esempio "Creazione di trigger per il popolamento di una tabella di proprietà", sostituendo tuttavia la funzione udf_XML2Table con la funzione CLR_udf_XML2Table.Finally, define triggers as shown in the example, "Create triggers to populate a property table", but replace udf_XML2Table with the CLR_udf_XML2Table function. Il trigger di inserimento è illustrato nell'esempio seguente:The insert trigger is shown in the following example:

create trigger CLR_trg_docs_INS on T for insert  
as  
   declare @wantedXML xml  
   declare @FK int  
   select @wantedXML = xCol from inserted  
   select @FK = PK from inserted  

   insert into tblPropAuthor  
      select *  
   from    dbo.CLR_udf_XML2Table(@FK, @wantedXML)  

Il trigger di eliminazione è identico alla versione non CLR,The delete trigger is identical to the non-CLR version. mentre nel trigger di inserimento viene semplicemente sostituita la funzione udf_XML2Table() con la funzione CLR_udf_XML2Table().However, the update trigger just replaces the function udf_XML2Table() with CLR_udf_XML2Table().

Vedere ancheSee Also

Utilizzo del codice XML nelle colonne calcolateUse XML in Computed Columns