使用 sql：max-depth 设置递归深度关系 - SQL Server

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
相关文章推荐
爱玩的黑框眼镜 · 八掛海_百度百科· 2 月前 ·
爱看书的羽毛球 · 杭州一小学取消寒假书面作业，你觉得寒暑假作业 ...· 5 月前 ·
长情的豆腐 · 腹黑老公甜宠妻_腹黑老公甜宠妻箫喻丞_腹黑老 ...· 1 年前 ·
刚分手的皮蛋 · 第十届西宁FIRST青年电影展入围作品名单公布· 1 年前 ·
善良的煎饼果子 · 高达新作《水星的魔女》开播超神，大河内会不会 ...· 1 年前 ·
适用于： SQL Server Azure SQL 数据库
在关系数据库中，当表涉及与其自身的关系时，将它称为递归关系。例如，在监督者和被监督者关系中，存储雇员记录的表涉及与其自身的关系。在这种情况下，雇员表在关系的一端扮演监督者的角色，而在另一端则扮演被监督者的角色。
映射架构可以包含元素及其祖先属于相同类型的递归关系。
考虑以下表：
Emp (EmployeeID, FirstName, LastName, ReportsTo)  
在该表中，ReportsTo 列存储经理的雇员 ID。
假设您想生成一个雇员 XML 层次结构，其中经理雇员位于层次结构的顶部，而向该经理报告的雇员出现在对应的层次结构中，如以下 XML 片段示例所示。 此片段显示的员工 1 的递归树 。
<?xml version="1.0" encoding="utf-8" ?>   
  <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>  
在该片段中，雇员 5 向雇员 4 报告，雇员 4 向雇员 3 报告，雇员 3 和 2 向雇员 1 报告。
若要产生该结果，可以使用以下 XSD 架构，并指定针对它的 XPath 查询。 架构描述 <EmployeeType 类型的 Emp> 元素，该<元素由同一类型 EmployeeType 的 Emp> 子元素组成。 此即属于递归关系（元素和其祖先属于相同类型）。 此外，架构使用 <sql：relationship> 来描述监督者和监督者之间的父子关系。 请注意，在此 <sql：relationship> 中，Emp 既是父表，也是子表。
<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>  
因为该关系是递归关系，所以需要以某种方式指定在架构中的递归深度。 否则，结果将是无穷递归的（雇员向雇员报告而该雇员又向雇员报告，以此类推）。 sql ：max-depth 批注允许指定递归中的深度。 在此特定示例中，若要指定 sql：max 深度的值，必须知道管理层次结构在公司中的深度。
架构指定 sql：limit-field 批注，但不指定 sql：limit-value 批注。 这会使得所生成的层次结构的顶级节点仅为那些不向任何人报告的雇员。 （ReportsTo 为 NULL。）指定 sql：limit-field ，而不指定 sql：limit-value （默认值为 NULL）批注可实现此目的。 如果希望生成的 XML 包含每个可能的报表树（表中每个员工的报表树），请从架构中删除 sql：limit-field 批注。
以下过程使用 tempdb 数据库。
针对架构测试示例 XPath 查询
在虚拟根目录所指向的 tempdb 数据库中创建称为 Emp 的示例表。
USE tempdb  
CREATE TABLE Emp (  
       EmployeeID int primary key,   
       FirstName  varchar(20),   
       LastName   varchar(20),   
       ReportsTo int)  
添加以下示例数据：
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)  
复制上面的架构代码，并将它粘贴到文本文件中。 将该文件另存为 maxDepth.xml。
复制以下模板，并将它粘贴到文本文件中。 该文件另存为 maxDepthT.xml，保存在与 maxDepth.xml 相同的目录中。 模板中的查询将返回 Emp 表中的所有雇员。
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <sql:xpath-query mapping-schema="maxDepth.xml">  
  </sql:xpath-query>  
</ROOT>  
为映射架构 (maxDepth.xml) 指定的目录路径是相对于模板保存目录的相对路径。 也可以指定绝对路径，例如：
mapping-schema="C:\MyDir\maxDepth.xml"  
创建并使用 SQLXML 4.0 测试脚本 (Sqlxml4test.vbs) 执行该模板。 有关详细信息，请参阅使用 ADO 执行 SQLXML 4.0 查询。
结果如下：
<?xml version="1.0" encoding="utf-8" ?>   
  <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" />   
</root>  
若要在结果中生成不同的层次结构深度，请更改架构中 sql：max 深度注释的值，并在每次更改后再次执行模板。
在前面的架构中，所有 <Emp> 元素具有相同的属性集（EmployeeID、 FirstName 和 LastName）。 已稍微修改以下架构，以返回向管理器报告的所有 Emp 元素的其他 <ReportsTo 属性。>
例如，该 XML 片段显示雇员 1 的下级：
<?xml version="1.0" encoding="utf-8" ?>   
<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">  
这是修改后的架构：
<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  
    </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 批注
在由递归关系组成的架构中，必须在架构中显式指定递归的深度。 若要成功生成可返回所请求的结果的相应 FOR XML EXPLICIT 查询，则必须这样做。
使用 架构中的 sql：max 深度 批注指定递归关系的递归深度，该关系在架构中描述。 sql：max-depth 批注的值是一个正整数（1 到 50），指示递归数：值 1 将停止指定 sql：max-depth 批注的元素上的递归;值 2 将停止下一级别的递归，从指定 sql：max-depth 的元素处停止递归;等等。
在基础实现中，针对映射架构指定的 XPath 查询将转换为 SELECT ...FOR XML EXPLICIT 查询。 该查询需要您指定一个有限的递归深度。 为 sql：max 深度指定的值越高，生成的 FOR XML EXPLICIT 查询越大。 这可能会使检索时间变长。
Updategram 和 XML 大容量加载将忽略最大深度批注。 这意味着，不管为最大深度指定了什么值，都将发生递归更新或插入。
在复杂元素上指定 sql:max-depth
可以在任何复杂内容元素上指定 sql：max-depth 批注。
如果在 递归关系中对父元素和子元素指定 sql：max-depth ， 则父元素上指定的 sql：max-depth 批注优先。 例如，在以下架构中， 在父元素和子员工元素上指定 sql：max 深度 注释。 在这种情况下，在 Emp> 父元素上<指定的 sql：max-depth=4（扮演主管角色）优先。 忽略对子 <Emp> 元素（扮演监督角色）指定的 sql：max-depth。
<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>  
若要测试此架构，请按照本主题前面的示例 A 提供的步骤进行操作。
非递归元素
如果在架构中不会导致任何递归的元素上指定 sql：max 深度注释，则忽略它。 在以下架构中，Emp> 元素由常量>子元素组成<，后者又具有 Emp>< 子元素。<

在此架构中，忽略对 Constant 元素指定的 <sql：max-depth 批注，因为 Emp> 父元素和 <Constant>> 子元素之间<没有递归。 但是，Emp 祖先和 <Emp> 子级之间<有递归。> 架构指定 两者上的 sql：max 深度 注释。 因此，在上级（<主管角色中的 Emp>）上指定的 sql：max 深度注释优先。
<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>  
若要测试该架构，请执行为本主题前面的示例 A 提供的步骤。
由限制派生的复杂类型
如果具有按 <限制>派生的复杂类型，则相应的基复杂类型的元素无法指定 sql：max 深度 批注。 在这些情况下， 可以将 sql：max 深度 批注添加到派生类型的元素。
另一方面，如果有按 <扩展>派生的复杂类型，则相应的基复杂类型的元素可以指定 sql：max 深度 注释。
例如，以下 XSD 架构生成错误，因为 sql：max 深度 注释是在基类型上指定的。 此批注不受其他类型的限制>派生<的类型的支持。 若要解决此问题，必须更改架构，并在派生类型中的元素上指定 sql：max 深度 注释。
<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>   
在架构中，sql：max-depth 是在 CustomerBaseType 复杂类型上指定的。 该架构还指定 CustomerType 类型的 Customer> 元素，该元素派生自 CustomerBaseType。< 针对此类架构指定的 XPath 查询将生成错误，因为 限制基类型中定义的元素不支持 sql：max 深度 。
具有深层次结构的架构
您可能有一个包含深层次结构的架构，在该层次结构中，元素包含子元素，而该子元素又包含另一个子元素，以此类推。 如果此类架构中指定的 sql：max 深度注释生成一个 XML 文档，该文档包含层次结构超过 500 个级别（级别为 1 的顶级元素、第 2 级子元素等），则返回错误。