Dela via

Ange djup i rekursiva relationer med hjälp av sql:max-depth

gäller för:SQL ServerAzure SQL Database

I relationella databaser, när en tabell är involverad i en relation med sig själv, kallas det en rekursiv relation. Till exempel, i en chef och handledare-relation, är en tabell som lagrar anställdas register involverad i en relation med sig själv. I detta fall spelar personalbordet rollen som handledare på ena sidan av relationen, och samma bord som övervakare på andra sidan.

Mappningsscheman kan inkludera rekursiva relationer där ett element och dess förfader är av samma typ.

Exempel A

Betrakta följande tabell:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

I denna tabell lagrar kolumnen ReportsTo chefens medarbetar-ID.

Antag att du vill generera en XML-hierarki av anställda där chefsanställda är högst upp i hierarkin, och där de anställda som rapporterar till den chefen finns i motsvarande hierarki som visas i följande exempel-XML-fragment. Vad detta fragment visar är det rekursiva trädet för anställd 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>

I detta fragment rapporterar anställd 5 till anställd 4, anställd 4 till anställd 3, och anställda 3 och 2 rapporterar till anställd 1.

För att producera detta resultat kan du använda följande XSD-schema och specificera en XPath-fråga mot det. Schemat beskriver ett <Emp-element> av typen EmployeeType, bestående av ett <Emp-barnelement> av samma typ, EmployeeType. Detta är en rekursiv relation (grundämnet och dess förfader är av samma typ). Dessutom använder schemat en <sql:relation> för att beskriva förälder-barn-relationen mellan handledare och övervakare. Observera att i denna <sql:relationship> är Emp både förälder- och barntabellen.

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

Eftersom relationen är rekursiv behöver du något sätt att specificera djupet av rekursion i schemat. Annars blir resultatet en oändlig rekursion (anställd rapporterar till anställd rapporterar till anställd, och så vidare). sql:max-depth-annotationen låter dig ange hur djupt in i rekursionen du ska gå. I just det här exemplet, för att specificera ett värde för sql:max-depth, måste du veta hur djupt ledningshierarkin går i företaget.

Anmärkning

Schemat specificerar sql:limit-field-annotationen , men specificerar inte sql:limit-value-annotationen . Detta begränsar den översta noden i den resulterande hierarkin till endast de anställda som inte rapporterar till någon. (ReportsTo är NULL.) Att specificera sql:limit-field och inte specsera sql:limit-value (som som standard är NULL) annotation åstadkommer detta. Om du vill att den resulterande XML:en ska inkludera varje möjlig rapporteringsträd (rapporteringsträdet för varje anställd i tabellen), ta bort sql:limit-field-annoteringen från schemat.

Anmärkning

Följande procedur använder tempdb-databasen.

För att testa en exempel-XPath-fråga mot schemat

  1. Skapa en exempeltabell kallad Emp i tempdb-databasen där de virtuella roterna pekar.

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

  2. Lägg till denna exempeldata:

    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. Kopiera schemakoden ovan och klistra in den i en textfil. Spara filen som maxDepth.xml.

  4. Kopiera följande mall och klistra in den i en textfil. Spara filen som maxDepthT.xml i samma katalog där du sparade maxDepth.xml. Frågan i mallen returnerar alla anställda i Emp-tabellen.

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

    Den katalogväg som anges för mappningsschemat (maxDepth.xml) är relativ till katalogen där mallen sparas. En absolut väg kan också specificeras, till exempel:

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

  5. Skapa och använd SQLXML 4.0 Test Script (Sqlxml4test.vbs) för att köra mallen. Mer information finns i Använda ADO för att köra SQLXML 4.0-frågor.

Det här är resultatet:

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

Anmärkning

För att skapa olika djup av hierarkier i resultatet, ändra värdet på sql:max-depth-annotationen i schemat och kör mallen igen efter varje ändring.

I det tidigare schemat hade alla <Emp-element> exakt samma uppsättning attribut (EmployeeID, FirstName och Efternamn). Följande schema har modifierats något för att returnera ett ytterligare ReportsTo-attribut för alla <Emp-element> som rapporterar till en chef.

Till exempel visar detta XML-fragment underordnade anställd 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">  
...  
...

Detta är det reviderade schemat:

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

sql:max-depth Annotation

I ett schema som består av rekursiva relationer måste rekursionsdjupet uttryckligen specificeras i schemat. Detta krävs för att framgångsrikt producera motsvarande FOR XML EXPLICIT-fråga som returnerar de begärda resultaten.

Använd sql:max-depth-annotationen i schemat för att specificera djupet av rekursion i en rekursiv relation som beskrivs i schemat. Värdet på sql:max-depth-annotationen är ett positivt heltal (1 till 50) som anger antalet rekursioner: Ett värde på 1 stoppar rekursionen vid det element för vilket sql:max-depth-annotationen är specificerad; Ett värde på 2 stoppar rekursionen på nästa nivå från det element där SQL:Max-djup specificeras; och så vidare.

Anmärkning

I den underliggande implementationen omvandlas en XPath-fråga som specificeras mot ett mappningsschema till en SELECT ... FÖR XML explicit fråga. Denna fråga kräver att du specificerar ett ändligt djup av rekursion. Ju högre värde du anger för sql:max-depth, desto större är DEN FÖR XML EXPLICIT-fråga som genereras. Detta kan fördröja hämtningstiden.

Anmärkning

Updategrams och XML Bulk Load ignorerar max-djup-annoteringen. Detta innebär att rekursiva uppdateringar eller insättningar sker oavsett vilket värde du anger för maxdjup.

Specificering av sql:max-depth på komplexa element

sql:max-depth-annotationen kan specificeras på vilket komplext innehållselement som helst.

Rekursiva element

Om sql:max-depth anges på både förälderelementet och barnelementet i en rekursiv relation, har sql:max-depth-annotationen som anges på föräldern företräde. Till exempel specificeras sql:max-depth-annoteringen i följande schema både på föräldra- och barnanställdelementen. I detta fall har sql:max-depth=4, specificerat på Emp-föräldraelementet <> (som spelar rollen som supervisor), företräde. sql:max-depth som anges på barn-Emp-elementet <> (som spelar rollen som supervisee) ignoreras.

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

För att testa detta schema, följ de steg som angavs för exempel A, tidigare i ämnet.

Icke-rekursiva element

Om sql:max-depth-annotationen specificeras på ett element i schemat som inte orsakar någon rekursion, ignoreras den. I följande schema består ett <Emp-element> av ett <Constant-barnelement> , som i sin tur har ett <Emp-barnelement> .

I detta schema ignoreras sql:max-depth-annotationen som specificeras på Constant-elementet <> eftersom det inte finns någon rekursion mellan <Emp-föräldern> och <Constant-barnelementet>. Men det finns rekursion mellan Emp-förfadern <> och <Emp-barnet>. Schemat specificerar sql:max-depth-annotationen för båda. Därför har sql:max-depth-annoteringen som anges på förfadern (<Emp> i supervisorrollen) företräde.

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

För att testa detta schema, följ stegen som angavs för Exempel A, tidigare i ämnet.

Komplexa typer härledda genom restriktion

Om du har en komplex typ-härledning genom <begränsning> kan element av motsvarande baskomplexa typ inte specificera sql:max-depth-annotationen . I dessa fall kan sql:max-depth-annotationen läggas till elementet av den härledda typen.

Å andra sidan, om du har en komplex typ-härledning genom <extension>, kan elementen i motsvarande baskomplexa typ specificera sql:max-depth-annotationen .

Till exempel genererar följande XSD-schema ett fel eftersom sql:max-depth-annotationen anges på bastypen. Denna annotering stöds inte på en typ som härleds genom <begränsning> från en annan typ. För att lösa detta problem måste du ändra schemat och specificera sql:max-depth-annotationen på element i den härledda typen.

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

I schemat specificeras sql:max-depth på en CustomerBaseType-komplex typ. Schemat specificerar också ett <kundelement> av typen CustomerType, som härstammar från CustomerBaseType. En XPath-fråga specificerad på ett sådant schema kommer att generera ett fel, eftersom sql:max-depth inte stöds på ett element som är definierat i en begränsningsbastyp.

Scheman med en djup hierarki

Du kan ha ett schema som inkluderar en djup hierarki där ett element innehåller ett barnelement, som i sin tur innehåller ett annat barnelement, och så vidare. Om sql:max-depth-annotationen som specificeras i ett sådant schema genererar ett XML-dokument som inkluderar en hierarki på mer än 500 nivåer (med toppnivåelement på nivå 1, dess barn på nivå 2, och så vidare), returneras ett fel.

  • Last updated on