Condividi tramite

Specificare la profondità nelle relazioni ricorsive tramite sql:max-depth

Nei database relazionali, quando una tabella è coinvolta in una relazione con se stessa, viene chiamata relazione ricorsiva. Ad esempio, in una relazione supervisor-supervisione, una tabella che archivia i record dei dipendenti è coinvolta in una relazione con se stessa. In questo caso, la tabella dipendenti svolge un ruolo di supervisore su un lato della relazione e la stessa tabella svolge un ruolo di supervisione dall'altra parte.

Gli schemi di mapping possono includere relazioni ricorsive in cui un elemento e il relativo predecessore sono dello stesso tipo.

Esempio A

Si consideri la tabella seguente:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

In questa tabella la colonna ReportsTo archivia l'ID dipendente del manager.

Si supponga di voler generare una gerarchia XML di dipendenti in cui il dipendente manager si trova all'inizio della gerarchia e in cui i dipendenti che segnalano tale responsabile vengano visualizzati nella gerarchia corrispondente, come illustrato nel frammento XML di esempio seguente. Ciò che mostra questo frammento è l'albero ricorsivo per il dipendente 1.

<?xml version="1.0" encoding="utf-8" ?>   
<root>  
  <Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">  
     <Emp FirstName="Andrew" EmployeeID="2" LastName="Fuller" />   
     <Emp FirstName="Janet" EmployeeID="3" LastName="Leverling">  
        <Emp FirstName="Margaret" EmployeeID="4" LastName="Peacock">  
          <Emp FirstName="Steven" EmployeeID="5" LastName="Devolio">  
...  
...  
</root>

In questo frammento, i dipendenti 5 segnalano ai dipendenti 4, i dipendenti 4 segnalano ai dipendenti 3 e 3 e 2 report al dipendente 1.

Per produrre questo risultato, è possibile usare lo schema XSD seguente e specificare una query XPath su di essa. Lo schema descrive un <elemento Emp di tipo EmployeeType, costituito da un elemento figlio Emp>>dello stesso tipo, EmployeeType.< Si tratta di una relazione ricorsiva (l'elemento e il relativo predecessore sono dello stesso tipo). Inoltre, lo schema usa una <sql:relationship> per descrivere la relazione padre-figlio tra il supervisore e la supervisione. Si noti che in questo <sql:relationship> Emp è sia l'elemento padre che la tabella figlio.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:dt="urn:schemas-microsoft-com:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
    <xsd:appinfo>  
      <sql:relationship name="SupervisorSupervisee"  
                                  parent="Emp"  
                                  parent-key="EmployeeID"  
                                  child="Emp"  
                                  child-key="ReportsTo" />  
    </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="Emp" type="EmployeeType"   
                          sql:relation="Emp"   
                          sql:key-fields="EmployeeID"   
                          sql:limit-field="ReportsTo" />  
  <xsd:complexType name="EmployeeType">  
    <xsd:sequence>  
      <xsd:element name="Emp" type="EmployeeType"   
                              sql:relation="Emp"   
                              sql:key-fields="EmployeeID"  
                              sql:relationship="SupervisorSupervisee"  
                              sql:max-depth="6" />  
    </xsd:sequence>   
    <xsd:attribute name="EmployeeID" type="xsd:ID" />  
    <xsd:attribute name="FirstName" type="xsd:string"/>  
    <xsd:attribute name="LastName" type="xsd:string"/>  
  </xsd:complexType>  
</xsd:schema>

Poiché la relazione è ricorsiva, è necessario specificare la profondità della ricorsione nello schema. In caso contrario, il risultato sarà una ricorsione infinita (dipendenti che segnalano ai dipendenti che segnalano ai dipendenti e così via). L'annotazione sql:max-depth consente di specificare il livello di profondità della ricorsione da eseguire. In questo particolare esempio, per specificare un valore per sql:max-depth, è necessario conoscere la profondità della gerarchia di gestione nell'azienda.

Annotazioni

Lo schema specifica l'annotazione sql:limit-field , ma non specifica l'annotazione sql:limit-value . In questo modo il nodo principale della gerarchia risultante viene limitato solo ai dipendenti che non segnalano a nessuno. (ReportsTo è NULL. Specificare sql:limit-field e non specificare sql:limit-value l'annotazione (che per impostazione predefinita è NULL) esegue questa operazione. Se si desidera che il codice XML risultante includa ogni possibile albero di report (l'albero dei report per ogni dipendente nella tabella), rimuovere l'annotazione sql:limit-field dallo schema.

Annotazioni

Nella procedura seguente viene utilizzato il database tempdb.

Per testare una query Xpath di esempio sullo schema

  1. Creare una tabella di esempio denominata Emp nel database tempdb in cui punta la radice virtuale.

    USE tempdb  
CREATE TABLE Emp (  
       EmployeeID int primary key,   
       FirstName  varchar(20),   
       LastName   varchar(20),   
       ReportsTo int)

  2. Aggiungere questi dati di esempio:

    INSERT INTO Emp values (1, 'Nancy', 'Devolio',NULL)  
INSERT INTO Emp values (2, 'Andrew', 'Fuller',1)  
INSERT INTO Emp values (3, 'Janet', 'Leverling',1)  
INSERT INTO Emp values (4, 'Margaret', 'Peacock',3)  
INSERT INTO Emp values (5, 'Steven', 'Devolio',4)  
INSERT INTO Emp values (6, 'Nancy', 'Buchanan',5)  
INSERT INTO Emp values (7, 'Michael', 'Suyama',6)

  3. Copiare il codice dello schema precedente e incollarlo in un file di testo. Salvare il file come maxDepth.xml.

  4. Copiare il modello seguente e incollarlo in un file di testo. Salvare il file come maxDepthT.xml nella stessa directory in cui è stato salvato maxDepth.xml. La query nel modello restituisce tutti i dipendenti nella tabella Emp.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <sql:xpath-query mapping-schema="maxDepth.xml">  
    /Emp  
  </sql:xpath-query>  
</ROOT>

    Il percorso della directory specificato per lo schema di mapping (maxDepth.xml) è relativo alla directory in cui viene salvato il modello. È possibile specificare anche un percorso assoluto, ad esempio:

    mapping-schema="C:\MyDir\maxDepth.xml"

  5. Creare e utilizzare lo script di test SQLXML 4.0 (Sqlxml4test.vbs) per eseguire il modello. Per altre informazioni, vedere Uso di ADO per eseguire query SQLXML 4.0.

Risultato:

<?xml version="1.0" encoding="utf-8" ?>   
<root>  
  <Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">  
  <Emp FirstName="Andrew" EmployeeID="2" LastName="Fuller" />   
    <Emp FirstName="Janet" EmployeeID="3" LastName="Leverling">  
      <Emp FirstName="Margaret" EmployeeID="4" LastName="Peacock">  
        <Emp FirstName="Steven" EmployeeID="5" LastName="Devolio">  
          <Emp FirstName="Nancy" EmployeeID="6" LastName="Buchanan">  
            <Emp FirstName="Michael" EmployeeID="7" LastName="Suyama" />   
          </Emp>  
        </Emp>  
      </Emp>  
    </Emp>  
  </Emp>  
</root>

Annotazioni

Per produrre profondità diverse delle gerarchie nel risultato, modificare il valore dell'annotazione sql:max-depth nello schema ed eseguire di nuovo il modello dopo ogni modifica.

Nello schema precedente tutti gli <elementi Emp> hanno esattamente lo stesso set di attributi (EmployeeID, FirstName e LastName). Lo schema seguente è stato leggermente modificato per restituire un attributo ReportsTo aggiuntivo per tutti gli <elementi Emp> che segnalano a un gestore.

Ad esempio, questo frammento XML mostra i subordinati del dipendente 1:

<?xml version="1.0" encoding="utf-8" ?>   
<root>  
<Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">  
  <Emp FirstName="Andrew" EmployeeID="2"   
       ReportsTo="1" LastName="Fuller" />   
  <Emp FirstName="Janet" EmployeeID="3"   
       ReportsTo="1" LastName="Leverling">  
...  
...

Questo è lo schema modificato:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:dt="urn:schemas-microsoft-com:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
    <xsd:documentation>  
      Customer-Order-Order Details Schema  
      Copyright 2000 Microsoft. All rights reserved.  
    </xsd:documentation>  
    <xsd:appinfo>  
      <sql:relationship name="SupervisorSupervisee"   
                  parent="Emp"  
                  parent-key="EmployeeID"  
                  child="Emp"  
                  child-key="ReportsTo" />  
    </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="Emp"   
                   type="EmpType"   
                   sql:relation="Emp"   
                   sql:key-fields="EmployeeID"   
                   sql:limit-field="ReportsTo" />  
  <xsd:complexType name="EmpType">  
    <xsd:sequence>  
       <xsd:element name="Emp"   
                    type="EmpType"   
                    sql:relation="Emp"   
                    sql:key-fields="EmployeeID"  
                    sql:relationship="SupervisorSupervisee"  
                    sql:max-depth="6"/>  
    </xsd:sequence>   
    <xsd:attribute name="EmployeeID" type="xsd:int" />  
    <xsd:attribute name="FirstName" type="xsd:string"/>  
    <xsd:attribute name="LastName" type="xsd:string"/>  
    <xsd:attribute name="ReportsTo" type="xsd:int" />  
  </xsd:complexType>  
</xsd:schema>

Annotazione sql:max-depth

In uno schema costituito da relazioni ricorsive, la profondità della ricorsione deve essere specificata in modo esplicito nello schema. Questa operazione è necessaria per produrre correttamente la query FOR XML EXPLICIT corrispondente che restituisce i risultati richiesti.

Usare l'annotazione sql:max-depth nello schema per specificare la profondità della ricorsione in una relazione ricorsiva descritta nello schema. Il valore dell'annotazione sql:max-depth è un numero intero positivo (da 1 a 50) che indica il numero di ricorsioni: un valore pari a 1 arresta la ricorsione all'elemento per cui viene specificata l'annotazione sql:max-depth ; un valore pari a 2 arresta la ricorsione al livello successivo dall'elemento in corrispondenza del quale sql:max-depth viene specificato e così via.

Annotazioni

Nell'implementazione sottostante, una query XPath specificata in base a uno schema di mapping viene convertita in un oggetto SELECT ... Query FOR XML EXPLICIT. Questa query richiede di specificare una profondità limitata di ricorsione. Maggiore è il valore specificato per sql:max-depth, maggiore è la query FOR XML EXPLICIT generata. Questo potrebbe rallentare il tempo di recupero.

Annotazioni

Gli updategram e il caricamento bulk XML ignorano l'annotazione di profondità massima. Ciò significa che gli aggiornamenti ricorsivi o gli inserimenti verranno eseguiti indipendentemente dal valore specificato per la profondità massima.

Specifica di sql:max-depth su elementi complessi

L'annotazione sql:max-depth può essere specificata in qualsiasi elemento di contenuto complesso.

Elementi ricorsivi

Se sql:max-depth viene specificato sia sull'elemento padre che sull'elemento figlio in una relazione ricorsiva, l'annotazione sql:max-depth specificata nell'elemento padre ha la precedenza. Nello schema seguente, ad esempio, l'annotazione sql:max-depth viene specificata sia per gli elementi padre che per i dipendenti figlio. In questo caso, sql:max-depth=4, specificato nell'elemento <padre Emp> (che svolge un ruolo di supervisore), ha la precedenza. L'oggetto sql:max-depth specificato nell'elemento Emp> figlio< (che gioca un ruolo di supervisione) viene ignorato.

Esempio B

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:dt="urn:schemas-microsoft-com:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
    <xsd:appinfo>  
      <sql:relationship name="SupervisorSupervisee"  
                                  parent="Emp"  
                                  parent-key="EmployeeID"  
                                  child="Emp"  
                                  child-key="ReportsTo" />  
    </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="Emp" type="EmployeeType"   
                          sql:relation="Emp"   
                          sql:key-fields="EmployeeID"   
                          sql:limit-field="ReportsTo"   
                          sql:max-depth="3" />  
  <xsd:complexType name="EmployeeType">  
    <xsd:sequence>  
      <xsd:element name="Emp" type="EmployeeType"   
                              sql:relation="Emp"   
                              sql:key-fields="EmployeeID"  
                              sql:relationship="SupervisorSupervisee"  
                              sql:max-depth="2" />  
    </xsd:sequence>   
    <xsd:attribute name="EmployeeID" type="xsd:ID" />  
    <xsd:attribute name="FirstName" type="xsd:string"/>  
    <xsd:attribute name="LastName" type="xsd:string"/>  
  </xsd:complexType>  
</xsd:schema>

Per testare questo schema, seguire i passaggi forniti per l'esempio A, più indietro in questo argomento.

Elementi non ricorsivi

Se l'annotazione sql:max-depth viene specificata su un elemento nello schema che non causa alcuna ricorsione, viene ignorata. Nello schema seguente un <elemento Emp è costituito da un elemento figlio Constant, che a sua volta ha un elemento figlio Emp>.<><>

In questo schema l'annotazione sql:max-depth specificata nell'elemento <Constant> viene ignorata perché non esiste alcuna ricorsione tra l'elemento <padre Emp> e l'elemento <figlio Constant> . Ma c'è ricorsione tra il <predecessore Emp> e il <figlio Emp> . Lo schema specifica l'annotazione sql:max-depth su entrambi. Di conseguenza, l'annotazione sql:max-depth specificata nel predecessore (<Emp> nel ruolo supervisore) ha la precedenza.

Esempio C

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"   
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
    <xsd:appinfo>  
      <sql:relationship name="SupervisorSupervisee"   
                  parent="Emp"   
                  child="Emp"   
                  parent-key="EmployeeID"   
                  child-key="ReportsTo"/>  
    </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="Emp"   
               sql:relation="Emp"   
               type="EmpType"  
               sql:limit-field="ReportsTo"  
               sql:max-depth="1" />  
    <xsd:complexType name="EmpType" >  
      <xsd:sequence>  
       <xsd:element name="Constant"   
                    sql:is-constant="1"   
                    sql:max-depth="20" >  
         <xsd:complexType >  
           <xsd:sequence>  
            <xsd:element name="Emp"   
                         sql:relation="Emp" type="EmpType"  
                         sql:relationship="SupervisorSupervisee"   
                         sql:max-depth="3" />  
         </xsd:sequence>  
         </xsd:complexType>  
         </xsd:element>  
      </xsd:sequence>  
      <xsd:attribute name="EmployeeID" type="xsd:int" />  
    </xsd:complexType>  
</xsd:schema>

Per testare questo schema, seguire i passaggi forniti per l'esempio A, in precedenza in questo argomento.

Tipi complessi derivati da restrizione

Se si dispone di una derivazione di tipi complessi per <restrizione>, gli elementi del tipo complesso di base corrispondente non possono specificare l'annotazione sql:max-depth . In questi casi, l'annotazione sql:max-depth può essere aggiunta all'elemento del tipo derivato.

D'altra parte, se si dispone di una derivazione di tipo complesso per <estensione>, gli elementi del tipo complesso di base corrispondente possono specificare l'annotazione sql:max-depth .

Ad esempio, lo schema XSD seguente genera un errore perché l'annotazione sql:max-depth viene specificata nel tipo di base. Questa annotazione non è supportata in un tipo derivato dalla <restrizione> da un altro tipo. Per risolvere il problema, è necessario modificare lo schema e specificare l'annotazione sull'elemento sql:max-depth nel tipo derivato.

Esempio D

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:dt="urn:schemas-microsoft-com:datatypes"  
            xmlns:msdata="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:complexType name="CustomerBaseType">   
    <xsd:sequence>  
       <xsd:element name="CID" msdata:field="CustomerID" />  
       <xsd:element name="CompanyName"/>  
       <xsd:element name="Customers" msdata:max-depth="3">  
         <xsd:annotation>  
           <xsd:appinfo>  
             <msdata:relationship  
                     parent="Customers"  
                     parent-key="CustomerID"  
                     child-key="CustomerID"  
                     child="Customers" />  
           </xsd:appinfo>  
         </xsd:annotation>  
       </xsd:element>  
    </xsd:sequence>  
  </xsd:complexType>  
  <xsd:element name="Customers" type="CustomerType"/>  
  <xsd:complexType name="CustomerType">  
    <xsd:complexContent>  
       <xsd:restriction base="CustomerBaseType">  
          <xsd:sequence>  
            <xsd:element name="CID"   
                         type="xsd:string"/>  
            <xsd:element name="CompanyName"   
                         type="xsd:string"  
                         msdata:field="CName" />  
            <xsd:element name="Customers"   
                         type="CustomerType" />  
          </xsd:sequence>  
       </xsd:restriction>  
    </xsd:complexContent>  
  </xsd:complexType>  
</xsd:schema>

Nello schema viene sql:max-depth specificato in un CustomerBaseType tipo complesso. Lo schema specifica anche un <elemento Customer> di tipo CustomerType, derivato da CustomerBaseType. Una query XPath specificata in tale schema genererà un errore, perché sql:max-depth non è supportato in un elemento definito in un tipo di base di restrizione.

Schemi con una gerarchia profonda

Potrebbe essere presente uno schema che include una gerarchia profonda in cui un elemento contiene un elemento figlio, che a sua volta contiene un altro elemento figlio e così via. Se l'annotazione specificata in tale schema genera un documento XML che include una gerarchia di più di 500 livelli (con l'elemento sql:max-depth di primo livello al livello 1, il relativo elemento figlio al livello 2 e così via), viene restituito un errore.

  • Last updated on