Partager via

Spécification de la profondeur dans les relations récursives à l'aide de sql:max-depth

Dans les bases de données relationnelles, lorsqu’une table est impliquée dans une relation avec elle-même, elle est appelée relation récursive. Par exemple, dans une relation superviseur-supervisée, une table stockant les enregistrements des employés est impliquée dans une relation avec elle-même. Dans ce cas, la table des employés joue un rôle de superviseur d’un côté de la relation, et la même table joue un rôle de supervision de l’autre côté.

Les schémas de mappage peuvent inclure des relations récursives où un élément et son ancêtre sont du même type.

Exemple A

Tenez compte du tableau suivant :

Emp (EmployeeID, FirstName, LastName, ReportsTo)

Dans ce tableau, la colonne ReportsTo stocke l’ID d’employé du responsable.

Supposons que vous souhaitez générer une hiérarchie XML d’employés dans laquelle l’employé du responsable se trouve en haut de la hiérarchie et dans laquelle les employés qui signalent à ce responsable apparaissent dans la hiérarchie correspondante, comme indiqué dans l’exemple de fragment XML suivant. Ce que montre ce fragment est l’arborescence récursive pour l’employé 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>

Dans ce fragment, l’employé 5 signale à l’employé 4, l’employé 4 signale à l’employé 3 et les employés 3 et 2 rapports à l’employé 1.

Pour produire ce résultat, vous pouvez utiliser le schéma XSD suivant et spécifier une requête XPath sur celle-ci. Le schéma décrit un <élément Emp> de type EmployeeType, constitué d’un <élément enfant Emp> du même type, EmployeeType. Il s’agit d’une relation récursive (l’élément et son ancêtre sont du même type). En outre, le schéma utilise une <relation sql :relationship> pour décrire la relation parent-enfant entre le superviseur et le superviseur. Notez que dans cette <relation sql :relationship>, Emp est à la fois le parent et la table enfant.

<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>

Étant donné que la relation est récursive, vous avez besoin d’un moyen de spécifier la profondeur de récursivité dans le schéma. Dans le cas contraire, le résultat sera une récursivité sans fin (les employés signalant aux employés qui signalent à l’employé, etc.). L’annotation sql:max-depth vous permet de spécifier la profondeur dans la récursivité à atteindre. Dans cet exemple particulier, pour spécifier une valeur pour sql:max-depth, vous devez connaître la profondeur de la hiérarchie de gestion dans l’entreprise.

Remarque

Le schéma spécifie l’annotation sql:limit-field , mais ne spécifie pas l’annotation sql:limit-value . Cela limite le nœud supérieur de la hiérarchie résultante aux seuls employés qui ne signalent pas à quiconque. (ReportsTo est NULL.) La spécification sql:limit-field et la non spécification (par défaut) de sql:limit-value l’annotation NULL effectue cette opération. Si vous souhaitez que le code XML résultant inclue chaque arborescence de rapports possible (l’arborescence de rapports pour chaque employé de la table), supprimez l’annotation sql:limit-field du schéma.

Remarque

La procédure suivante utilise la base de données tempdb.

Pour tester un exemple de requête XPath sur le schéma

  1. Créez un exemple de table appelé Emp dans la base de données tempdb vers laquelle les points racine virtuels.

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

  2. Ajoutez cet exemple de données :

    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. Copiez le code de schéma ci-dessus et collez-le dans un fichier texte. Enregistrez le fichier en tant que maxDepth.xml.

  4. Copiez le modèle suivant et collez-le dans un fichier texte. Enregistrez le fichier en tant que maxDepthT.xml dans le même répertoire que celui où vous avez enregistré maxDepth.xml. La requête dans le modèle retourne tous les employés de la table Emp.

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

    Le chemin d’accès au répertoire spécifié pour le schéma de mappage (maxDepth.xml) est relatif au répertoire dans lequel le modèle est enregistré. Vous pouvez également spécifier un chemin d'accès absolu, par exemple :

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

  5. Créez et utilisez le script de test SQLXML 4.0 (Sqlxml4test.vbs) pour exécuter le modèle. Pour plus d'informations, voir Utilisation d'ADO pour exécuter des requêtes SQLXML 4.0.

Voici le résultat obtenu :

<?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>

Remarque

Pour produire différentes profondeurs de hiérarchies dans le résultat, modifiez la valeur de l’annotation sql:max-depth dans le schéma et réexécutez le modèle après chaque modification.

Dans le schéma précédent, tous les <éléments Emp> avaient exactement le même ensemble d’attributs (EmployeeID, FirstName et LastName). Le schéma suivant a été légèrement modifié pour renvoyer un attribut ReportsTo supplémentaire pour tous les <éléments Emp> qui signalent à un gestionnaire.

Par exemple, ce fragment XML montre les subordonnés de l’employé 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">  
...  
...

Il s’agit du schéma révisé :

<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>

annotation sql :max-depth

Dans un schéma constitué de relations récursives, la profondeur de récursivité doit être spécifiée explicitement dans le schéma. Cela est nécessaire pour produire correctement la requête FOR XML EXPLICIT correspondante qui retourne les résultats demandés.

Utilisez l’annotation sql:max-depth dans le schéma pour spécifier la profondeur de récursivité dans une relation récursive décrite dans le schéma. La valeur de l’annotation sql:max-depth est un entier positif (1 à 50) qui indique le nombre de récursivités : une valeur de 1 arrête la récursivité à l’élément pour lequel l’annotation sql:max-depth est spécifiée ; la valeur 2 arrête la récursivité au niveau suivant de l’élément spécifié sql:max-depth  ; et ainsi de suite.

Remarque

Dans l’implémentation sous-jacente, une requête XPath spécifiée par rapport à un schéma de mappage est convertie en UN SELECT ... Requête FOR XML EXPLICIT. Cette requête vous oblige à spécifier une profondeur finie de récursivité. Plus la valeur que vous spécifiez est sql:max-depthélevée, plus la requête FOR XML EXPLICIT est élevée. Cela peut ralentir le temps de récupération.

Remarque

Les codes de mise à jour et le chargement en masse XML ignorent l’annotation de profondeur maximale. Cela signifie que les mises à jour récursives ou les insertions se produisent indépendamment de la valeur que vous spécifiez pour la profondeur maximale.

Spécification de sql :max-depth sur les éléments complexes

L’annotation sql:max-depth peut être spécifiée sur n’importe quel élément de contenu complexe.

Éléments récursifs

Si sql:max-depth elle est spécifiée à la fois sur l’élément parent et sur l’élément enfant dans une relation récursive, l’annotation sql:max-depth spécifiée sur le parent est prioritaire. Par exemple, dans le schéma suivant, l’annotation sql:max-depth est spécifiée sur les éléments parent et enfant de l’employé. Dans ce cas, sql:max-depth=4, spécifié sur l’élément <parent Emp> (jouant un rôle de superviseur), est prioritaire. L’élément sql:max-depthEmp> enfant< spécifié (jouant un rôle de supervision) est ignoré.

Exemple 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>

Pour tester ce schéma, suivez les étapes fournies pour l’exemple A, plus haut dans cette rubrique.

Éléments nonrecursifs

Si l’annotation sql:max-depth est spécifiée sur un élément du schéma qui n’entraîne aucune récursivité, elle est ignorée. Dans le schéma suivant, un <élément Emp> se compose d’un <élément Enfant Constant> , qui, à son tour, a un <élément Enfant Emp> .

Dans ce schéma, l’annotation sql:max-depth spécifiée sur l’élément <Constant> est ignorée, car il n’existe aucune récursivité entre le <parent Emp> et l’élément <Enfant Constant> . Mais il y a récursivité entre l’ancêtre <Emp> et l’enfant <Emp> . Le schéma spécifie l’annotation sur sql:max-depth les deux. Par conséquent, l’annotation sql:max-depth spécifiée sur l’ancêtre (<Emp> dans le rôle de superviseur) est prioritaire.

Exemple 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>

Pour tester ce schéma, suivez les étapes fournies pour l’exemple A, plus haut dans cette rubrique.

Types complexes dérivés par restriction

Si vous avez une dérivation de type complexe par <restriction>, les éléments du type complexe de base correspondant ne peuvent pas spécifier l’annotation sql:max-depth . Dans ces cas, l’annotation sql:max-depth peut être ajoutée à l’élément du type dérivé.

En revanche, si vous avez une dérivation de type complexe par <extension>, les éléments du type complexe de base correspondant peuvent spécifier l’annotation sql:max-depth .

Par exemple, le schéma XSD suivant génère une erreur, car l’annotation sql:max-depth est spécifiée sur le type de base. Cette annotation n’est pas prise en charge sur un type dérivé d’une <restriction> d’un autre type. Pour résoudre ce problème, vous devez modifier le schéma et spécifier l’annotation sur l’élément sql:max-depth du type dérivé.

Exemple 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>

Dans le schéma, sql:max-depth est spécifié sur un CustomerBaseType type complexe. Le schéma spécifie également un <élément Customer> de type CustomerType, qui est dérivé de CustomerBaseType. Une requête XPath spécifiée sur un tel schéma génère une erreur, car sql:max-depth elle n’est pas prise en charge sur un élément défini dans un type de base de restriction.

Schémas avec une hiérarchie profonde

Vous pouvez avoir un schéma qui inclut une hiérarchie profonde dans laquelle un élément contient un élément enfant, qui à son tour contient un autre élément enfant, et ainsi de suite. Si l’annotation sql:max-depth spécifiée dans un tel schéma génère un document XML qui inclut une hiérarchie de plus de 500 niveaux (avec un élément de niveau supérieur au niveau 1, son enfant au niveau 2, etc.), une erreur est retournée.

  • Last updated on