Задание глубины рекурсивных связей с использованием sql:max-depth

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

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

Пример А

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

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-depth; значение 2 останавливает рекурсию на следующем уровне от элемента, для которого указана заметка sql:max-depth; и т. д.

ПримечаниеПримечание

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

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

Задание sql:max-depth для сложных элементов

Заметка 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>

Для проверки этой схемы выполните шаги, приведенные для образца А ранее в этом разделе.

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

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

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

Пример В

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

Для проверки этой схемы выполните шаги, приведенные для примера А ранее в этом разделе.

Сложные типы, наследуемые по ограничению

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

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

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

  • Last updated on