Partilhar via

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

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

Em bases de dados relacionais, quando uma tabela está envolvida numa relação consigo mesma, chama-se relação recursiva. Por exemplo, numa relação supervisor-supervisionado, uma tabela que armazena registos de funcionários está envolvida numa relação consigo mesma. Neste caso, a mesa dos funcionários desempenha o papel de supervisor de um lado da relação, e a mesma mesa desempenha o papel de supervisionado do outro.

Os esquemas de mapeamento podem incluir relações recursivas onde um elemento e o seu antecessor são do mesmo tipo.

Exemplo A

Considere a tabela seguinte:

Emp (EmployeeID, FirstName, LastName, ReportsTo)

Nesta tabela, a coluna ReportsTo armazena o ID do funcionário do gestor.

Suponha que pretende gerar uma hierarquia XML de funcionários em que o gestor está no topo da hierarquia, e em que os funcionários que reportam a esse gestor aparecem na hierarquia correspondente, conforme mostrado no seguinte fragmento XML de exemplo. O que este fragmento mostra é a árvore recursiva para o empregado 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>

Neste fragmento, o funcionário 5 reporta ao funcionário 4, o empregado 4 reporta ao funcionário 3, e os funcionários 3 e 2 reportam ao empregado 1.

Para obter este resultado, pode usar o seguinte esquema XSD e especificar uma consulta XPath contra ele. O esquema descreve um <elemento Emp> do tipo EmployeeType, consistindo num <elemento filho Emp> do mesmo tipo, EmployeeType. Esta é uma relação recursiva (o elemento e o seu antecessor são do mesmo tipo). Além disso, o esquema utiliza uma <relação sql:> relationship para descrever a relação pai-filho entre o supervisor e o supervisionado. Note que nesta <relação sql:>, o Emp é tanto a tabela pai como a filha.

<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, precisas de alguma forma de especificar a profundidade da recursão no esquema. Caso contrário, o resultado será uma recursão interminável (colaborador a reportar a funcionário a reportar a funcionário, e assim sucessivamente). A anotação sql:max-depth permite-te especificar até que ponto na recursão deves avançar. Neste exemplo em particular, para especificar um valor para sql:max-depth, é necessário saber até que ponto a hierarquia de gestão da empresa vai profundamente.

Observação

O esquema especifica a anotação sql:limit-field , mas não especifica a anotação sql:limit-value . Isto limita o nó superior na hierarquia resultante apenas àqueles funcionários que não reportam a ninguém. (ReportsTo é NULL.) Especificar a anotação sql:limit-field e não especificar sql:limit-value (que por defeito é NULL) faz isto. Se quiser que o XML resultante inclua todas as árvores de reporte possíveis (a árvore de reporte para cada colaborador na tabela), remova a anotação sql:limit-field do esquema.

Observação

O procedimento seguinte utiliza a base de dados tempdb.

Para testar uma consulta XPath de exemplo contra o esquema

  1. Crie uma tabela de exemplo chamada Emp na base de dados tempdb para a 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 do esquema acima e cole num ficheiro de texto. Guarde o ficheiro como maxDepth.xml.

  4. Copie o modelo seguinte e cole-o num ficheiro de texto. Guarda o ficheiro como maxDepthT.xml no mesmo diretório onde guardaste maxDepth.xml. A consulta no modelo devolve 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 do diretório especificado para o esquema de mapeamento (maxDepth.xml) é relativo ao diretório onde o modelo é guardado. Um caminho absoluto também pode ser especificado, 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 Usando 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 novamente o modelo após cada alteração.

No esquema anterior, todos os <elementos Emp> tinham exatamente o mesmo conjunto de atributos (EmployeeID, FirstName e Apelido). O esquema seguinte foi ligeiramente modificado para devolver um atributo adicional ReportsTo para todos os <elementos Emp> que reportam a um gestor.

Por exemplo, este fragmento XML mostra os subordinados do empregado 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 revisto:

<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

Num esquema composto por relações recursivas, a profundidade da recursão deve ser explicitamente especificada no esquema. Isto é necessário para produzir com sucesso a correspondente consulta FOR XML EXPLICIT que devolve os resultados solicitados.

Use a anotação sql:max-depth no esquema para especificar a profundidade da recursão numa relação recursiva descrita no esquema. O valor da anotação sql:max-depth é um inteiro positivo (de 1 a 50) que indica o número de recursões: Um valor de 1 para a recursão no elemento para o qual a anotação sql:max-depth é especificada; Um valor de 2 impede a recursão no nível seguinte do elemento onde a profundidade SQL:Max é especificada; e assim sucessivamente.

Observação

Na implementação subjacente, uma consulta XPath especificada contra um esquema de mapeamento é convertida num SELECT ... CONSULTA EXPLÍCITA PARA XML. Esta consulta exige que especifique uma profundidade finita de recursão. Quanto maior for o valor especificado para sql:max-depth, maior será a consulta FOR XML EXPLÍCITA gerada. Isto pode atrasar o tempo de recolha.

Observação

Updategrams e XML Bulk Load ignoram a anotação de profundidade máxima. Isto significa que atualizações recursivas ou inserções acontecerão independentemente do valor que especificar para a profundidade máxima.

Especificação de 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 tanto no elemento pai como no elemento filho numa relação recursiva, a anotação sql:max-depth especificada no pai tem precedência. Por exemplo, no esquema seguinte, a anotação sql:max-depth é especificada tanto no elemento pai como no filho do empregado. Neste caso, sql:max-depth=4, especificado no <elemento pai Emp> (desempenhando o papel de supervisor), tem prioridade. A profundidade sql:max especificada no elemento Emp> filho< (que desempenha o papel de supervisionado) é ignorada.

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 este esquema, siga os passos fornecidos para o Exemplo A, anteriormente neste tópico.

Elementos não recursivos

Se a anotação sql:max-depth for especificada num elemento do esquema que não cause qualquer recursão, é ignorada. No esquema seguinte, um <elemento Emp> consiste num <elemento filho Constante> , que, por sua vez, tem um <elemento filho Emp> .

Neste 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 a <criança Emp> . O esquema especifica a anotação sql:max-depth em ambos. Portanto, a anotação sql:max-depth especificada no antecessor (<Emp> no papel de supervisor) tem prioridade.

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 este esquema, siga os passos fornecidos para o Exemplo A, anteriormente neste tópico.

Tipos Complexos Derivados por Restrição

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

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

Por exemplo, o esquema XSD seguinte gera um erro porque a anotação sql:max-depth é especificada no tipo base. Esta anotação não é suportada num tipo derivado por <restrição> de outro tipo. Para resolver este problema, é necessário alterar o esquema e especificar a anotação sql:max-depth no elemento do 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 num tipo de complexo CustomerBaseType . O esquema também especifica um <elemento Cliente> do tipo CustomerType, que deriva de CustomerBaseType. Uma consulta XPath especificada num esquema deste tipo gerará um erro, porque sql:max-depth não é suportado num elemento definido num tipo base de restrição.

Esquemas com uma Hierarquia Profunda

Podes ter um esquema que inclui uma hierarquia profunda em que um elemento contém um elemento filho, que por sua vez contém outro elemento filho, e assim sucessivamente. Se a anotação sql:max-depth especificada num esquema deste tipo gerar um documento XML que inclui uma hierarquia de mais de 500 níveis (com o elemento de topo no nível 1, o seu filho no nível 2, e assim sucessivamente), é devolvido um erro.

  • Last updated on