Бөлісу құралы:

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

  • Мақала

Область применения: SQL Server База данных SQL Azure

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

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

Пример 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-deep позволяет указать, насколько глубоко в рекурсии следует идти. В этом примере, чтобы указать значение для sql:max-deep, необходимо знать, насколько глубоко иерархия управления идет в компании.

Примечание.

Схема задает заметку 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-глубины в схеме и снова выполните шаблон после каждого изменения.

В предыдущей схеме все <элементы 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-глубины — положительное целое число (от 1 до 50), указывающее количество рекурсий: значение 1 останавливает рекурсию в элементе, для которого указана заметка sql:max-глубины; значение 2 останавливает рекурсию на следующем уровне от элемента, на котором указана sql:max-глубина; и т. д.

Примечание.

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

Примечание.

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

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

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

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

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

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

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

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

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

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