Bagikan melalui

Menentukan Kedalaman dalam Hubungan Rekursif dengan Menggunakan sql:max-depth

Berlaku untuk: SQL ServerAzure SQL Database

Dalam database relasional, ketika tabel terlibat dalam hubungan dengan dirinya sendiri, itu disebut hubungan rekursif. Misalnya, dalam hubungan supervisor-supervisee, tabel yang menyimpan catatan karyawan terlibat dalam hubungan dengan dirinya sendiri. Dalam hal ini, tabel karyawan memainkan peran pengawas di satu sisi hubungan, dan tabel yang sama memainkan peran pengawas di sisi lain.

Skema pemetaan dapat mencakup hubungan rekursif di mana elemen dan leluhurnya memiliki jenis yang sama.

Contoh A

Pertimbangkan tabel berikut:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

Dalam tabel ini, kolom ReportsTo menyimpan ID karyawan manajer.

Asumsikan bahwa Anda ingin menghasilkan hierarki XML karyawan di mana karyawan manajer berada di bagian atas hierarki, dan di mana karyawan yang melaporkan ke manajer tersebut muncul dalam hierarki yang sesuai seperti yang ditunjukkan dalam sampel fragmen XML berikut. Apa yang ditunjukkan fragmen ini adalah pohon rekursif untuk karyawan 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>

Dalam fragmen ini, karyawan 5 melaporkan kepada karyawan 4, karyawan 4 laporan kepada karyawan 3, dan karyawan 3 dan 2 laporan kepada karyawan 1.

Untuk menghasilkan hasil ini, Anda dapat menggunakan skema XSD berikut dan menentukan kueri XPath terhadapnya. Skema ini menjelaskan <elemen Emp> jenis EmployeeType, yang <terdiri dari elemen anak Emp> dengan jenis yang sama, EmployeeType. Ini adalah hubungan rekursif (elemen dan leluhurnya memiliki jenis yang sama). Selain itu, skema menggunakan <sql:relationship> untuk menggambarkan hubungan induk-anak antara supervisor dan supervisee. Perhatikan bahwa dalam sql:relationship<>, Emp adalah tabel induk dan anak.

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

Karena hubungannya rekursif, Anda memerlukan beberapa cara untuk menentukan kedalaman rekursi dalam skema. Jika tidak, hasilnya akan menjadi rekursi tanpa akhir (pelaporan karyawan ke pelaporan karyawan kepada karyawan, dan sebagainya). Anotasi sql:max-depth memungkinkan Anda menentukan seberapa dalam rekursi. Dalam contoh khusus ini, untuk menentukan nilai untuk sql:max-depth, Anda harus tahu seberapa dalam hierarki manajemen di perusahaan.

Catatan

Skema menentukan anotasi sql:limit-field , tetapi tidak menentukan anotasi sql:limit-value . Ini membatasi simpul teratas dalam hierarki yang dihasilkan hanya untuk karyawan yang tidak melaporkan kepada siapa pun. (ReportsTo adalah NULL.) Menentukan sql:limit-field dan tidak menentukan anotasi sql:limit-value (yang default ke NULL) menyelesaikan ini. Jika Anda ingin XML yang dihasilkan menyertakan setiap pohon pelaporan yang mungkin (pohon pelaporan untuk setiap karyawan dalam tabel), hapus anotasi sql:limit-field dari skema.

Catatan

Prosedur berikut menggunakan database tempdb.

Untuk menguji sampel kueri XPath terhadap skema

  1. Buat tabel sampel yang disebut Emp di database tempdb tempat titik akar virtual.

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

  2. Tambahkan data sampel ini:

    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. Salin kode skema di atas dan tempelkan ke dalam file teks. Simpan file sebagai maxDepth.xml.

  4. Salin templat berikut dan tempelkan ke dalam file teks. Simpan file sebagai maxDepthT.xml di direktori yang sama tempat Anda menyimpan maxDepth.xml. Kueri dalam templat mengembalikan semua karyawan dalam tabel Emp.

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

    Jalur direktori yang ditentukan untuk skema pemetaan (maxDepth.xml) relatif terhadap direktori tempat templat disimpan. Jalur absolut juga dapat ditentukan, misalnya:

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

  5. Buat dan gunakan Skrip Pengujian SQLXML 4.0 (Sqlxml4test.vbs) untuk menjalankan templat. Untuk informasi selengkapnya, lihat Menggunakan ADO untuk Menjalankan Kueri SQLXML 4.0.

Ini adalah hasilnya:

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

Catatan

Untuk menghasilkan kedalaman hierarki yang berbeda dalam hasil, ubah nilai anotasi sql:max-depth dalam skema dan jalankan templat lagi setelah setiap perubahan.

Dalam skema sebelumnya, semua <elemen Emp> memiliki set atribut yang sama persis (EmployeeID, FirstName, dan LastName). Skema berikut telah sedikit dimodifikasi untuk mengembalikan atribut ReportsTo tambahan untuk semua<> yang melaporkan ke manajer.

Misalnya, fragmen XML ini menunjukkan bawahan karyawan 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">  
...  
...

Ini adalah skema yang direvisi:

<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:anotasi kedalaman maks

Dalam skema yang terdiri dari hubungan rekursif, kedalaman rekursi harus ditentukan secara eksplisit dalam skema. Ini diperlukan untuk berhasil menghasilkan kueri EKSPLISIT FOR XML yang sesuai yang mengembalikan hasil yang diminta.

Gunakan anotasi sql:max-depth dalam skema untuk menentukan kedalaman rekursi dalam hubungan rekursif yang dijelaskan dalam skema. Nilai anotasi sql:max-depth adalah bilangan bulat positif (1 hingga 50) yang menunjukkan jumlah rekursi: Nilai 1 menghentikan rekursi pada elemen tempat anotasi sql:max-depth ditentukan; nilai 2 menghentikan rekursi pada tingkat berikutnya dari elemen di mana sql:max-depth ditentukan; dan sebagainya.

Catatan

Dalam implementasi yang mendasar, kueri XPath yang ditentukan terhadap skema pemetaan dikonversi ke SELECT ... UNTUK kueri EKSPLISIT XML. Kueri ini mengharuskan Anda menentukan kedalaman rekursi yang terbatas. Semakin tinggi nilai yang Anda tentukan untuk sql:max-depth, semakin besar kueri FOR XML EXPLICIT yang dihasilkan. Ini mungkin memperlambat waktu pengambilan.

Catatan

Updategrams dan XML Bulk Load mengabaikan anotasi kedalaman maks. Ini berarti, pembaruan atau penyisipan rekursif akan terjadi terlepas dari nilai apa yang Anda tentukan untuk kedalaman maks.

Menentukan sql:max-depth pada Elemen Kompleks

Anotasi sql:max-depth dapat ditentukan pada elemen konten kompleks apa pun.

Elemen Rekursif

Jika sql:max-depth ditentukan pada elemen induk dan elemen turunan dalam hubungan rekursif, anotasi sql:max-depth yang ditentukan pada induk lebih diutamakan. Misalnya, dalam skema berikut, anotasi sql:max-depth ditentukan pada elemen karyawan induk dan anak. Dalam hal ini, sql:max-depth=4, yang ditentukan pada <elemen induk Emp> (memainkan peran supervisor), lebih diutamakan. Sql:max-depth yang ditentukan pada elemen Emp<>(memainkan peran pengawas) diabaikan.

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

Untuk menguji skema ini, ikuti langkah-langkah yang disediakan untuk Sampel A, sebelumnya dalam topik ini.

Elemen Nonrekursif

Jika anotasi sql:max-depth ditentukan pada elemen dalam skema yang tidak menyebabkan rekursi apa pun, anotasi akan diabaikan. Dalam skema berikut, > terdiri dari elemen anak Konstanta>, yang, pada gilirannya, memiliki >

Dalam skema ini, anotasi sql:max-depth yang ditentukan pada< elemen Konstanta> diabaikan karena tidak ada rekursi antara <induk Emp> dan< elemen turunan Konstanta.> Tapi ada rekursi antara > dan <>. Skema menentukan anotasi sql:max-depth pada keduanya. Oleh karena itu, anotasi sql:max-depth yang ditentukan pada leluhur (<Emp> dalam peran supervisor) lebih diutamakan.

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

Untuk menguji skema ini, ikuti langkah-langkah yang disediakan untuk Contoh A, sebelumnya dalam topik ini.

Jenis Kompleks Diturunkan oleh Pembatasan

Jika Anda memiliki derivasi jenis kompleks berdasarkan <pembatasan>, elemen dari jenis kompleks dasar yang sesuai tidak dapat menentukan anotasi sql:max-depth . Dalam kasus ini, anotasi sql:max-depth dapat ditambahkan ke elemen jenis turunan.

Di sisi lain, jika Anda memiliki derivasi jenis kompleks berdasarkan <ekstensi>, elemen dari jenis kompleks dasar yang sesuai dapat menentukan anotasi sql:max-depth .

Misalnya, skema XSD berikut menghasilkan kesalahan karena anotasi sql:max-depth ditentukan pada jenis dasar. Anotasi ini tidak didukung pada jenis yang diturunkan oleh <pembatasan> dari jenis lain. Untuk memperbaiki masalah ini, Anda harus mengubah skema dan menentukan anotasi sql:max-depth pada elemen dalam jenis turunan.

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

Dalam skema, sql:max-depth ditentukan pada jenis kompleks CustomerBaseType . Skema ini juga menentukan > jenis CustomerType, yang berasal dari CustomerBaseType. Kueri XPath yang ditentukan pada skema tersebut akan menghasilkan kesalahan, karena sql:max-depth tidak didukung pada elemen yang ditentukan dalam jenis dasar pembatasan.

Skema dengan Hierarki Mendalam

Anda mungkin memiliki skema yang mencakup hierarki mendalam di mana elemen berisi elemen turunan, yang pada gilirannya berisi elemen anak lain, dan sebagainya. Jika anotasi sql:max-depth yang ditentukan dalam skema tersebut menghasilkan dokumen XML yang menyertakan hierarki lebih dari 500 tingkat (dengan elemen tingkat atas pada tingkat 1, turunannya pada tingkat 2, dan sebagainya), kesalahan dikembalikan.

  • Last updated on