Spécification de la profondeur dans les relations récursives à l'aide de sql:max-depth
S’applique à :
SQL ServerAzure SQL Database
Dans les bases de données relationnelles, lorsqu'une table est impliquée dans une relation avec elle-même, on utilise le terme de relation récursive. Par exemple, dans une relation responsable-subalterne, une table qui stocke des enregistrements d'employés est impliquée dans une relation avec elle-même. Dans ce cas, la table d'employés joue un rôle de responsable d'un côté de la relation, et la même table joue un rôle de subalterne de l'autre côté.
Les schémas de mappage peuvent inclure des relations récursives dans lesquelles un élément et son ancêtre sont du même type.
Exemple A
Considérez la table ci-dessous :
Emp (EmployeeID, FirstName, LastName, ReportsTo)
Dans cette table, la colonne ReportsTo stocke l'ID employé du responsable.
Supposez que vous souhaitez générer une hiérarchie XML des employés, dans laquelle l'employé responsable est en haut de la hiérarchie et les employés subalternes apparaissent dans la hiérarchie correspondante comme illustré dans le fragment d'exemple 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 est le subalterne de l'employé 4, l'employé 4 est le subalterne de l'employé 3 et les employés 3 et 2 sont les subalternes de l'employé 1.
Pour produire ce résultat, vous pouvez utiliser le schéma XSD suivant et spécifier une requête XPath contre lui. 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>
La relation étant récursive, vous devez trouver un moyen de spécifier la profondeur de récursivité dans le schéma. Sinon, le résultat sera une récursivité sans fin (employé subalterne à employé subalterne à employé, et ainsi de suite). 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 dans la hiérarchie résultante uniquement aux employés qui ne sont subalternes de personne. (ReportsTo est NULL.) La spécification de sql :limit-field et non la spécification de l’annotation sql :limit-value (qui est définie par défaut sur 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
Créez un exemple de table nommé Emp dans la base de données tempdb vers laquelle la racine virtuelle pointe.
USE tempdb CREATE TABLE Emp ( EmployeeID int primary key, FirstName varchar(20), LastName varchar(20), ReportsTo int)Ajoutez les exemples de données suivants :
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)Copiez le code de schéma ci-dessus et collez-le dans un fichier texte. Enregistrez le fichier en tant que maxDepth.xml.
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 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 où le modèle est enregistré. Vous pouvez également spécifier un chemin d'accès absolu, par exemple :
mapping-schema="C:\MyDir\maxDepth.xml"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 affiche les subalternes 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">
...
...
Voici le schéma modifié :
<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 afin de produire avec succès 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 ; une valeur de 2 arrête la récursivité au niveau suivant de l’élément auquel sql :max-depth est spécifié ; et ainsi de suite.
Remarque
Dans l'implémentation sous-jacente, une requête XPath spécifiée contre un schéma de mappage est convertie en requête SELECT ... FOR XML EXPLICIT. Cette requête nécessite que vous spécifiiez une profondeur de récursivité finie. Plus la valeur que vous spécifiez pour sql :max-depth est élevée, plus la requête FOR XML EXPLICIT est élevée. Cela risque d'allonger la durée de récupération.
Remarque
Les codes de mise à jour (updategrams) et chargements en masse XML ignorent l'annotation max-depth. Cela signifie que les insertions ou mises à jour récursives ont lieu indépendamment de la valeur que vous spécifiez pour max-depth.
Spécification de sql:max-depth sur des é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 est spécifié à la fois sur l’élément parent et 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 à la fois 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. La profondeur sql :max spécifiée sur l’élément Emp> enfant <(jouant un rôle de supervision) est ignorée.
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 non récursifs
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 sql :max-depth sur 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 sql :max-depth sur l’élément 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 type complexe CustomerBaseType . Le schéma spécifie également un <élément Customer> de type CustomerType, 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 n’est pas pris 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.