Поделиться через

Указание глубины в рекурсивных отношениях с помощью sql:max-depth

В реляционных базах данных, когда таблица участвует в связи с собой, она называется рекурсивной связью. Например, в отношениях с руководителем, контролируемым руководителем, таблица, включающая записи сотрудников, участвует в самой связи. В этом случае таблица сотрудников играет роль руководителя на одной стороне отношения, а та же таблица играет роль контролируемого на другой стороне.

Схемы сопоставления могут включать рекурсивные связи, в которых элемент и его предок имеют одинаковый тип.

Пример A

Рассмотрим следующую таблицу:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

В этой таблице столбец ReportsTo хранит идентификатор сотрудника руководителя.

Предположим, что вы хотите создать XML-иерархию сотрудников, в которой сотрудник руководителя находится в верхней части иерархии, и в которой сотрудники, сообщающие этому руководителю, отображаются в соответствующей иерархии, как показано в следующем примере фрагмента XML. В этом фрагменте показано рекурсивное дерево для сотрудника 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>

В этом фрагменте сотрудник 5 сообщает сотрудникам 4, сотрудник 4 сообщает сотрудникам 3, а сотрудники 3 и 2 отчеты сотрудникам 1.

Чтобы получить этот результат, можно использовать следующую схему XSD и указать запрос XPath к нему. Схема описывает <элемент Emp типа EmployeeType, состоящий из дочернего элемента Emp>>того же типа EmployeeType.< Это рекурсивная связь (элемент и его предок имеют одинаковый тип). Кроме того, схема использует <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-depthнеобходимо знать, насколько глубоко иерархия управления идет в компании.

Замечание

Схема задает заметку sql:limit-field , но не указывает заметку sql:limit-value . Это ограничивает верхний узел в результирующей иерархии только тем сотрудникам, которые не сообщают никому. (ReportsTo имеет значение NULL.) При sql:limit-field указании и не указании sql:limit-value заметки (по умолчанию для NULL) выполняется это. Если вы хотите, чтобы полученный XML-код включал все возможные деревья отчетов (дерево отчетов для каждого сотрудника в таблице), удалите sql:limit-field заметку из схемы.

Замечание

В следующей процедуре используется база данных tempdb.

Проверка образца запроса XPath к схеме

  1. Создайте пример таблицы с именем Emp в базе данных tempdb, к которой указываются виртуальные корневые точки.

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

  2. Добавьте эти примеры данных:

    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. Скопируйте приведенный выше код схемы и вставьте его в текстовый файл. Сохраните файл как maxDepth.xml.

  4. Скопируйте следующий шаблон и вставьте его в текстовый файл. Сохраните файл как maxDepthT.xml в том же каталоге, где вы сохранили maxDepth.xml. Запрос в шаблоне возвращает всех сотрудников в таблице Emp.

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

    Путь к каталогу, указанный для схемы сопоставления (maxDepth.xml), относится к каталогу, в котором сохраняется шаблон. Можно также задать абсолютный путь, например:

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

  5. Создайте и запустите тестовый скрипт SQLXML 4.0 (Sqlxml4test.vbs), чтобы выполнить шаблон. Дополнительные сведения см. в разделе "Использование ADO для выполнения запросов SQLXML 4.0".

Результат:

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

Замечание

Чтобы создать различные глубины иерархий в результате, измените значение sql:max-depth заметки в схеме и снова выполните шаблон после каждого изменения.

В предыдущей схеме все <элементы Emp> имели точно тот же набор атрибутов (EmployeeID, FirstName и LastName). Следующая схема была немного изменена, чтобы вернуть дополнительный атрибут ReportsTo для всех <элементов Emp> , которые сообщают руководителю.

Например, в этом фрагменте XML показаны подчиненные сотрудника 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">  
...  
...

Это измененная схема:

<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

В схеме, состоящей из рекурсивных связей, глубина рекурсии должна быть явно указана в схеме. Это необходимо для успешного создания соответствующего запроса FOR XML EXPLICIT, возвращающего запрошенные результаты.

sql:max-depth Используйте заметку в схеме, чтобы указать глубину рекурсии в рекурсивной связи, описанной в схеме. Значение sql:max-depth заметки — положительное целое число (от 1 до 50), указывающее количество рекурсий: значение 1 останавливает рекурсию в элементе, для которого sql:max-depthsql:max-depth указана заметка; значение 2 останавливает рекурсию на следующем уровне от указанного элемента; и т. д.

Замечание

В базовой реализации запрос XPath, указанный в схеме сопоставления, преобразуется в select ... ЗАПРОС FOR XML EXPLICIT. Этот запрос требует указания конечной глубины рекурсии. Чем выше указанное значение sql:max-depth, тем больше созданного запроса FOR XML EXPLICIT. Это может замедлить время извлечения.

Замечание

Диаграммы обновления и массовая загрузка XML игнорируют заметку с максимальной глубиной. Это означает, что рекурсивные обновления или вставки будут выполняться независимо от значения, указанного для максимальной глубины.

Указание sql:max-глубины для сложных элементов

Заметку sql:max-depth можно указать в любом сложном элементе содержимого.

Рекурсивные элементы

Если sql:max-depth указано как для родительского элемента, так и дочернего элемента в рекурсивной связи, заметка, указанная в родительском элементе, sql:max-depth имеет приоритет. Например, в следующей схеме sql:max-depth заметка указывается как на родительских, так и на дочерних элементах сотрудника. В этом случае, sql:max-depth=4указанный в <родительском элементе Emp> (играя роль руководителя), имеет приоритет. Указанный sql:max-depth на дочернем <элементе 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"   
                          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-depth Если заметка указана в элементе схемы, которая не вызывает рекурсии, она игнорируется. В следующей схеме <элемент Emp состоит из дочернего элемента Константы, который, в свою очередь, имеет дочерний элемент Emp>><.><

В этой схеме заметкаsql:max-depth, указанная в элементе <Constant>, игнорируется, так как между родительским элементом Emp> и дочерним элементом<<Constant> отсутствует рекурсия. Но существует рекурсия между <предком Emp> и ребенком <Emp> . Схема задает заметку sql:max-depth для обоих. Таким образом, sql:max-depth заметка, указанная на предке (<Emp> в роли руководителя), имеет приоритет.

Пример 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>

Чтобы проверить эту схему, выполните действия, описанные в примере A, ранее в этом разделе.

Сложные типы, производные по ограничению

Если у вас есть сложное наследование по <ограничению>, элементы соответствующего базового сложного типа не могут указать заметку sql:max-depth . В таких случаях sql:max-depth заметку можно добавить в элемент производного типа.

С другой стороны, если у вас есть сложная производность <по расширению>, элементы соответствующего базового сложного типа могут указать заметку sql:max-depth .

Например, следующая схема XSD создает ошибку, так как sql:max-depth заметка указана в базовом типе. Эта заметка не поддерживается для типа, производного от <другого> типа. Чтобы устранить эту проблему, необходимо изменить схему и указать заметку sql:max-depth для элемента в производном типе.

Пример Г

<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 тип. Схема также задает <элемент типа Customer>, производный от CustomerBaseType.CustomerType Запрос XPath, указанный в такой схеме, приведет к возникновению ошибки, так как sql:max-depth не поддерживается для элемента, определенного в базовом типе ограничения.

Схемы с глубокой иерархией

У вас может быть схема, содержащая глубокую иерархию, в которой элемент содержит дочерний элемент, который, в свою очередь, содержит другой дочерний элемент и т. д. sql:max-depth Если заметка, указанная в такой схеме, создает XML-документ, содержащий иерархию более 500 уровней (с элементом верхнего уровня на уровне 1, дочерним элементом уровня 2 и т. д.), возвращается ошибка.