Compartilhar via

Especificando a profundidade em relações recursivas usando sql:max-depth

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure

Em bancos de dados relacionais, quando uma tabela está envolvida em uma relação com si mesma, a relação é chamada de relação recursiva. Por exemplo, em uma relação supervisor-supervisionado, uma tabela que armazena os registros dos funcionários está envolvida em uma relação com si mesma. Nesse caso, a tabela de funcionários desempenha a função de supervisor em um lado da relação e a mesma tabela desempenha a função de supervisionado no outro lado.

O mapeamento de esquemas pode incluir relações recursivas, em que um elemento e seu ancestral são do mesmo tipo.

Exemplo A

Considere a seguinte tabela:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

Nessa tabela, a coluna ReportsTo armazena a ID de funcionário do gerente.

Suponha que você deseje gerar uma hierarquia XML de funcionários na qual o funcionário gerente esteja no topo da hierarquia e na qual os funcionários subordinados a esse gerente apareçam na hierarquia correspondente como mostrado no exemplo de fragmento XML a seguir. O que este fragmento mostra é a árvore recursiva para o funcionário 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>

Nesse fragmento, o funcionário 5 está subordinado ao funcionário 4, o funcionário 4 está subordinado ao funcionário 3 e os funcionários 3 e 2 estão subordinados ao funcionário 1.

Para gerar esse resultado, você pode usar o esquema XSD a seguir e especificar uma consulta XPath para ele. O esquema descreve um <elemento Emp> do tipo EmployeeType, que consiste em um <elemento filho Emp> do mesmo tipo, EmployeeType. Essa é uma relação recursiva (o elemento e seu ancestral são do mesmo tipo). Além disso, o esquema usa um <sql:relationship> para descrever a relação pai-filho entre o supervisor e o supervisionado. Observe que, neste <sql:relationship>, Emp é a tabela pai e filho.

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

Como a relação é recursiva, você precisa de algum modo de especificar a profundidade de recursão no esquema. Caso contrário, o resultado será uma recursão infinita (funcionário subordinado a funcionário subordinado a funcionário e assim por diante). A anotação sql:max-depth permite que você especifique a profundidade da recursão. Neste exemplo específico, para especificar um valor para sql:max-depth, você deve saber a profundidade da hierarquia de gerenciamento na empresa.

Observação

O esquema especifica a anotação sql:limit-field , mas não especifica a anotação sql:limit-value . Isso limita o nó superior da hierarquia resultante apenas aos funcionários que não estão subordinados a ninguém. (ReportsTo é NULL.) Especificar a anotação sql:limit-field e não especificar a anotação sql:limit-value (cujo padrão é NULL) faz isso. Se você quiser que o XML resultante inclua todas as árvores de relatórios possíveis (a árvore de relatórios para cada funcionário na tabela), remova a anotação sql:limit-field do esquema.

Observação

O procedimento a seguir usa o banco de dados tempdb.

Para testar uma consulta XPath de exemplo com relação ao esquema

  1. Crie uma tabela de exemplo chamada Emp no banco de dados tempdb para o qual a raiz virtual aponta.

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

  2. Adicione estes dados de exemplo:

    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. Copie o código de esquema acima e cole-o em um arquivo de texto. Salve o arquivo como maxDepth.xml.

  4. Copie o modelo a seguir e cole-o em um arquivo de texto. Salve o arquivo como maxDepthT.xml no mesmo diretório onde você salvou maxDepth.xml. A consulta no modelo retorna todos os funcionários na tabela Emp.

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

    O caminho de diretório especificado para o esquema de mapeamento (maxDepth.xml) é relativo ao diretório onde o modelo está salvo. Também é possível especificar um caminho absoluto, por exemplo:

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

  5. Crie e use o script de teste SQLXML 4.0 (Sqlxml4test.vbs) para executar o modelo. Para obter mais informações, consulte Usar o ADO para executar consultas SQLXML 4.0.

Este é o resultado:

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

Observação

Para produzir diferentes profundidades de hierarquias no resultado, altere o valor da anotação sql:max-depth no esquema e execute o modelo novamente após cada alteração.

No esquema anterior, todos os elementos Emp> tinham exatamente o mesmo conjunto de atributos (EmployeeID, FirstName e LastName).< O esquema a seguir foi ligeiramente modificado para retornar um atributo ReportsTo adicional para todos os elementos Emp> que se reportam< a um gerente.

Por exemplo, este fragmento XML mostra os subordinados do funcionário 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">  
...  
...

Este é o esquema revisado:

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

Anotação sql:max-depth

Em um esquema que consiste em relações recursivas, a profundidade de recursão deve ser especificada explicitamente no esquema. Isso é necessário para gerar, com êxito, a consulta FOR XML EXPLICIT correspondente que retorna os resultados solicitados.

Use a anotação sql:max-depth no esquema para especificar a profundidade da recursão em uma relação recursiva descrita no esquema. O valor da anotação sql:max-depth é um número inteiro positivo (1 a 50) que indica o número de recursões: Um valor de 1 interrompe a recursão no elemento para o qual a anotação sql:max-depth é especificada; um valor de 2 interrompe a recursão no próximo nível do elemento no qual sql:max-depth é especificado; e assim por diante.

Observação

Na implementação subjacente, uma consulta XPath especificada em um esquema de mapeamento é convertida em um SELECT ... FOR XML EXPLICIT. Essa consulta requer que você especifique uma profundidade finita de recursão. Quanto maior o valor especificado para sql:max-depth, maior será a consulta FOR XML EXPLICIT gerada. Isso poderá pode tornar mais lenta a recuperação.

Observação

Updategrams e o Carregamento em Massa de XML ignoram a anotação max-depth. Isso significa que atualizações ou inserções recursivas acontecerão, independentemente do valor especificado para max-depth.

Especificando sql:max-depth em elementos complexos

A anotação sql:max-depth pode ser especificada em qualquer elemento de conteúdo complexo.

Elementos recursivos

Se sql:max-depth for especificado no elemento pai e no elemento filho em uma relação recursiva, a anotação sql:max-depth especificada no pai terá precedência. Por exemplo, no esquema a seguir, a anotação sql:max-depth é especificada nos elementos pai e filho employee. Nesse caso, sql:max-depth=4, especificado no <elemento pai Emp> (desempenhando uma função de supervisor), tem precedência. O sql:max-depth especificado no elemento Emp> filho <(desempenhando uma função de supervisionado) é ignorado.

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

Para testar esse esquema, siga as etapas fornecidas para o Exemplo A, anteriormente neste tópico.

Elementos não recursivos

Se a anotação sql:max-depth for especificada em um elemento no esquema que não causa nenhuma recursão, ela será ignorada. No esquema a seguir, um elemento Emp> consiste em um <elemento filho Constant>, que, por sua vez, tem um< elemento filho Emp>.<

Nesse esquema, a anotação sql:max-depth especificada no elemento Constant> é ignorada< porque não há recursão entre o< pai Emp> e o< elemento filho Constant.> Mas há recursão entre o ancestral Emp> e o <filho Emp>.< O esquema especifica a anotação sql:max-depth em ambos. Portanto, a anotação sql:max-depth especificada no ancestral (<Emp> na função de supervisor) tem precedência.

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

Para testar esse esquema, siga as etapas fornecidas para o Exemplo A, anteriormente neste tópico.

Tipos complexos derivados por restrição

Se você tiver uma derivação de tipo complexo por restrição>, os elementos do tipo complexo base correspondente não poderão especificar a anotação sql:max-depth.< Nesses casos, a anotação sql:max-depth pode ser adicionada ao elemento do tipo derivado.

Por outro lado, se você tiver uma derivação de tipo complexo por <extensão>, os elementos do tipo complexo base correspondente poderão especificar a anotação sql:max-depth .

Por exemplo, o esquema XSD a seguir gera um erro porque a anotação sql:max-depth é especificada no tipo base. Não há suporte para essa anotação em um tipo derivado por <restrição> de outro tipo. Para corrigir esse problema, você deve alterar o esquema e especificar a anotação sql:max-depth no elemento no tipo derivado.

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

No esquema, sql:max-depth é especificado em um tipo complexo CustomerBaseType . O esquema também especifica um <elemento Customer> do tipo CustomerType, que é derivado de CustomerBaseType. Uma consulta XPath especificada nesse esquema gerará um erro, pois não há suporte para sql:max-depth em um elemento definido em um tipo base de restrição.

Esquemas com uma hierarquia profunda

Você pode ter um esquema que inclua uma hierarquia profunda na qual um elemento contém um elemento filho que, por sua vez, contém outro elemento filho e assim por diante. Se a anotação sql:max-depth especificada nesse esquema gerar um documento XML que inclua uma hierarquia de mais de 500 níveis (com elemento de nível superior no nível 1, seu filho no nível 2 e assim por diante), um erro será retornado.