Iteração e instrução FLWOR (XQuery)

Artigo
04/03/2023

Aplica-se: SQL Server

O XQuery define uma sintaxe de iteração FLWOR. FLWOR é o acrônimo para for, let, where, order by e return.

Uma instrução FLWOR é composta pelas seguintes partes:

Uma ou mais cláusulas FOR que associam uma ou mais variáveis de iterador para sequências de entrada.

As sequências de entrada podem ser outras expressões XQuery, como expressões XPath. Elas são sequências de nós ou sequências de valores atômicos. Podem ser construídas sequências de valor atômico usando funções literais ou de construtor. Os nós XML construídos não são permitidos como sequências de entrada no SQL Server.
Uma cláusula let opcional. Essa cláusula atribui um valor à determinada variável para uma iteração específica. A expressão atribuída pode ser uma expressão Xquery, como uma expressão Xpath, e pode retornar uma sequência de nós ou uma sequência de valores atômicos. As sequências de valor atômico podem ser construídas usando funções literais ou de construtor. Os nós XML construídos não são permitidos como sequências de entrada no SQL Server.
Uma variável de iterador. Essa variável pode ter uma asserção de tipo opcional usando a palavra-chave as.
Uma cláusula where opcional. Essa cláusula aplica um predicado de filtro na iteração.
Uma cláusula order by opcional.
Uma expressão return. A expressão na cláusula return constrói o resultado da instrução FLWOR.

Por exemplo, a consulta a seguir itera sobre os <Step> elementos no primeiro local de fabricação e retorna o valor da cadeia de caracteres dos <Step> nós:

declare @x xml  
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >  
<Location LocationID="L1" >  
  <Step>Manu step 1 at Loc 1</Step>  
  <Step>Manu step 2 at Loc 1</Step>  
  <Step>Manu step 3 at Loc 1</Step>  
</Location>  
<Location LocationID="L2" >  
  <Step>Manu step 1 at Loc 2</Step>  
  <Step>Manu step 2 at Loc 2</Step>  
  <Step>Manu step 3 at Loc 2</Step>  
</Location>  
</ManuInstructions>'  
SELECT @x.query('  
   for $step in /ManuInstructions/Location[1]/Step  
   return string($step)  
')

Este é o resultado:

Manu step 1 at Loc 1 Manu step 2 at Loc 1 Manu step 3 at Loc 1

A consulta a seguir é semelhante à anterior, exceto que é especificada em relação à coluna Instructions, uma coluna xml digitada, da tabela ProductModel. A consulta itera em todas as etapas de fabricação, <step> elementos, no primeiro local do centro de trabalho de um produto específico.

SELECT Instructions.query('  
   declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
for $Step in //AWMI:root/AWMI:Location[1]/AWMI:step  
      return  
           string($Step)   
') as Result  
FROM Production.ProductModel  
where ProductModelID=7

Observe o seguinte na consulta anterior:

A $Step é a variável de iterador.
A expressão de caminho, //AWMI:root/AWMI:Location[1]/AWMI:step, gera a sequência de entrada. Essa sequência é a sequência dos filhos do <step> nó do primeiro <Location> elemento.
A cláusula de predicado opcional, where, não é usada.
A return expressão retorna um valor de cadeia de caracteres do <step> elemento.

A função de cadeia de caracteres (XQuery) é usada para recuperar o valor da cadeia de caracteres do <step> nó.

Este é o resultado parcial:

Insert aluminum sheet MS-2341 into the T-85A framing tool.   
Attach Trim Jig TJ-26 to the upper and lower right corners of   
the aluminum sheet. ....

Estes são exemplos de sequências de entrada adicionais permitidas:

declare @x xml  
set @x=''  
SELECT @x.query('  
for $a in (1, 2, 3)  
  return $a')  
-- result = 1 2 3   
  
declare @x xml  
set @x=''  
SELECT @x.query('  
for $a in   
   for $b in (1, 2, 3)  
      return $b  
return $a')  
-- result = 1 2 3  
  
declare @x xml  
set @x='<ROOT><a>111</a></ROOT>'  
SELECT @x.query('  
  for $a in (xs:string( "test"), xs:double( "12" ), data(/ROOT/a ))  
  return $a')  
-- result test 12 111

No SQL Server, sequências heterogêneas não são permitidas. Especificamente, não são permitidas sequências que tenham uma combinação de valores atômicos e nós.

A iteração é freqüentemente usada junto com a sintaxe XML Construction na transformação de formatos XML, conforme mostrado na próxima consulta.

No banco de dados de exemplo AdventureWorks, as instruções de fabricação armazenadas na coluna Instruções da tabela Production.ProductModel têm o seguinte formato:

<Location LocationID="10" LaborHours="1.2"   
            SetupHours=".2" MachineHours=".1">  
  <step>describes 1st manu step</step>  
   <step>describes 2nd manu step</step>  
   ...  
</Location>  
...

A consulta a seguir constrói um novo XML que tem os <Location> elementos com os atributos de localização do centro de trabalho retornados como elementos filho:

<Location>  
   <LocationID>10</LocationID>  
   <LaborHours>1.2</LaborHours>  
   <SetupHours>.2</SetupHours>  
   <MachineHours>.1</MachineHours>  
</Location>  
...

Esta é a consulta:

SELECT Instructions.query('  
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
        for $WC in /AWMI:root/AWMI:Location  
        return  
          <Location>  
            <LocationID> { data($WC/@LocationID) } </LocationID>  
            <LaborHours>   { data($WC/@LaborHours) }   </LaborHours>  
            <SetupHours>   { data($WC/@SetupHours) }   </SetupHours>  
            <MachineHours> { data($WC/@MachineHours) } </MachineHours>  
          </Location>  
') as Result  
FROM Production.ProductModel  
where ProductModelID=7

Observe o seguinte na consulta anterior:

A instrução FLWOR recupera uma sequência de <Location> elementos para um produto específico.
A função de dados (XQuery) é usada para extrair o valor de cada atributo para que eles sejam adicionados ao XML resultante como nós de texto em vez de como atributos.
A expressão na cláusula RETURN constrói o XML desejado.

Este é um resultado parcial:

<Location>  
  <LocationID>10</LocationID>  
  <LaborHours>2.5</LaborHours>  
  <SetupHours>0.5</SetupHours>  
  <MachineHours>3</MachineHours>  
</Location>  
<Location>  
   ...  
<Location>  
...

Usando a cláusula let

Você pode usar a cláusula let para nomear expressões repetidas as quais é possível fazer referência quando referir-se à variável. A expressão atribuída a uma variável let é inserida na consulta toda vez que a variável é referenciada na consulta. Isso significa que a instrução será executada tantas vezes quantas forem as referências à expressão.

No banco de dados AdventureWorks2022, as instruções de fabricação contêm informações sobre as ferramentas necessárias e o local em que elas são usadas. A consulta a seguir usa a cláusula let para listar as ferramentas necessárias para construir o modelo de produção, assim como os locais em que cada ferramenta é necessária.

SELECT Instructions.query('  
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
        for $T in //AWMI:tool  
            let $L := //AWMI:Location[.//AWMI:tool[.=data($T)]]  
        return  
          <tool desc="{data($T)}" Locations="{data($L/@LocationID)}"/>  
') as Result  
FROM Production.ProductModel  
where ProductModelID=7

Usando a cláusula where

Você pode usar a cláusula para filtrar os where resultados de uma iteração. Isso é ilustrado no exemplo a seguir.

Na fabricação de uma bicicleta, o processo de produção passa por uma série de locais do centro de trabalho. Cada local do centro de trabalho define uma sequência de etapas de produção. A consulta a seguir recupera somente os locais do centro de trabalho que fabricam um modelo de bicicleta e têm menos de três etapas de produção. Ou seja, eles têm menos de três <step> elementos.

SELECT Instructions.query('  
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
for $WC in /AWMI:root/AWMI:Location  
      where count($WC/AWMI:step) < 3  
      return  
          <Location >  
           { $WC/@LocationID }   
          </Location>  
') as Result  
FROM Production.ProductModel  
where ProductModelID=7

Observe o seguinte na consulta anterior:

A where palavra-chave usa a função count() para contar o número de elementos filhos em cada local do centro de <step> trabalho.
A expressão return constrói o XML desejado a partir dos resultados da iteração.

Este é o resultado:

<Location LocationID="30"/>

O resultado da expressão na cláusula where é convertido em um valor Booliano usando as regras a seguir, na ordem especificada. Essas são as mesmas regras para predicados em expressões de caminho, exceto que não são permitidos inteiros:

Se a expressão where retornar uma sequência vazia, seu valor Booliano efetivo será False.
Se a expressão where retornar um valor de tipo Booliano simples, esse valor será o valor Booliano efetivo.
Se a expressão where retornar uma sequência que contém pelo menos um nó, o valor Booliano efetivo será True.
Caso contrário, ele gera um erro estático.

Associação de variável múltipla em FLWOR

Você pode ter uma única expressão FLWOR que associa diversas variáveis a sequências de entrada. No exemplo a seguir, a consulta é especificada em uma coluna xml não digitada. A expressão FLOWR retorna o primeiro <Step> filho de elemento em cada <Location> elemento.

declare @x xml  
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >  
<Location LocationID="L1" >  
  <Step>Manu step 1 at Loc 1</Step>  
  <Step>Manu step 2 at Loc 1</Step>  
  <Step>Manu step 3 at Loc 1</Step>  
</Location>  
<Location LocationID="L2" >  
  <Step>Manu step 1 at Loc 2</Step>  
  <Step>Manu step 2 at Loc 2</Step>  
  <Step>Manu step 3 at Loc 2</Step>  
</Location>  
</ManuInstructions>'  
SELECT @x.query('  
   for $Loc in /ManuInstructions/Location,  
       $FirstStep in $Loc/Step[1]  
   return   
       string($FirstStep)  
')

Observe o seguinte na consulta anterior:

A for expressão define $Loc e $FirstStep variáveis.
As expressões two, /ManuInstructions/Location e $FirstStep in $Loc/Step[1], são correlacionadas pelo fato de que os valores de $FirstStep dependem dos valores de $Loc.
A expressão associada a $Loc gera uma sequência de <Location> elementos. Para cada <Location> elemento, $FirstStep gera uma sequência de um <Step> elemento, um singleton.
$Loc é especificado na expressão associada com a variável $FirstStep.

Este é o resultado:

Manu step 1 at Loc 1   
Manu step 1 at Loc 2

A consulta a seguir é semelhante, exceto que ela é especificada na coluna Instructions, coluna xml digitada, da tabela ProductModel. A construção XML (XQuery) é usada para gerar o XML desejado.

SELECT Instructions.query('  
     declare default element namespace "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
for $WC in /root/Location,  
            $S  in $WC/step  
      return  
          <Step LocationID= "{$WC/@LocationID }" >  
            { $S/node() }  
          </Step>  
') as Result  
FROM  Production.ProductModel  
WHERE ProductModelID=7

Observe o seguinte na consulta anterior:

A cláusula for define duas variáveis, $WC e $S. A expressão associada com $WC gera uma sequência de locais de centro de trabalho na fabricação de um modelo do produto de bicicleta. A expressão de caminho atribuída à variável $S gera uma sequência de etapas para cada sequência de local do centro de trabalho no $WC.
A instrução return constrói XML que tem um <Step> elemento que contém a etapa de fabricação e o LocationID como seu atributo.
O namespace do elemento padrão de declaração é usado no prólogo XQuery para que todas as declarações de namespace no XML resultante apareçam no elemento de nível superior. Isso torna o resultado mais legível. Para obter mais informações sobre namespaces padrão, consulte Manipulando namespaces no XQuery.

Este é o resultado parcial:

<Step xmlns=  
    "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"     
  LocationID="10">  
     Insert <material>aluminum sheet MS-2341</material> into the <tool>T-   
     85A framing tool</tool>.   
</Step>  
...  
<Step xmlns=  
      "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"     
    LocationID="20">  
        Assemble all frame components following blueprint   
        <blueprint>1299</blueprint>.  
</Step>  
...

Usando a cláusula order by

A classificação no XQuery é executada usando a cláusula order by na expressão FLWOR. As expressões de classificação passadas para a order by cláusula devem retornar valores cujos tipos são válidos para o operador gt . Cada expressão de classificação deve resultar em uma sequência singleton com um item. Por padrão, a classificação é executada em ordem crescente. Você pode especificar opcionalmente a ordem crescente ou decrescente para cada expressão de classificação.

Observação

As comparações de classificação em valores de cadeia de caracteres executadas pela implementação XQuery no SQL Server são sempre executadas usando a ordenação de ponto de código Unicode binário.

A consulta a seguir recupera todos os números de telefone de um determinando cliente da coluna AdditionalContactInfo. Os resultados são classificados pelo número de telefone.

USE AdventureWorks2022;  
GO  
SELECT AdditionalContactInfo.query('  
   declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
   declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
   for $a in /aci:AdditionalContactInfo//act:telephoneNumber   
   order by $a/act:number[1] descending  
   return $a  
') As Result  
FROM Person.Person  
WHERE BusinessEntityID=291;

Observe que o processo de atomização (XQuery) recupera o valor atômico dos elementos antes de> <numberpassá-lo para .order by Você pode escrever a expressão usando a função data(), mas isso não é necessário.

order by data($a/act:number[1]) descending

Este é o resultado:

<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">  
  <act:number>333-333-3334</act:number>  
</act:telephoneNumber>  
<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">  
  <act:number>333-333-3333</act:number>  
</act:telephoneNumber>

Em vez de declarar os namespaces no prólogo da consulta, você pode declará-los usando WITH XMLNAMESPACES.

WITH XMLNAMESPACES (  
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS act,  
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo'  AS aci)  
  
SELECT AdditionalContactInfo.query('  
   for $a in /aci:AdditionalContactInfo//act:telephoneNumber   
   order by $a/act:number[1] descending  
   return $a  
') As Result  
FROM Person.Person  
WHERE BusinessEntityID=291;

Também é possível a classificação por valor do atributo. Por exemplo, a consulta a seguir recupera os elementos recém-criados <Location> que têm os atributos LocationID e LaborHours classificados pelo atributo LaborHours em ordem decrescente. Consequentemente, os locais do centro de trabalho com o maior número de horas de trabalho são retornados primeiro.

SELECT Instructions.query('  
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
for $WC in /AWMI:root/AWMI:Location   
order by $WC/@LaborHours descending  
        return  
          <Location>  
             { $WC/@LocationID }   
             { $WC/@LaborHours }   
          </Location>  
') as Result  
FROM Production.ProductModel  
WHERE ProductModelID=7;

Este é o resultado:

<Location LocationID="60" LaborHours="4"/>  
<Location LocationID="50" LaborHours="3"/>  
<Location LocationID="10" LaborHours="2.5"/>  
<Location LocationID="20" LaborHours="1.75"/>  
<Location LocationID="30" LaborHours="1"/>  
<Location LocationID="45" LaborHours=".5"/>

Na consulta a seguir, os resultados são classificados por nome de elemento. A consulta recupera as especificações de um produto específico do catálogo de produtos. As especificações são filhas do <Specifications> elemento.

SELECT CatalogDescription.query('  
     declare namespace  
 pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
      for $a in /pd:ProductDescription/pd:Specifications/*   
     order by local-name($a)  
      return $a  
    ') as Result  
FROM Production.ProductModel  
where ProductModelID=19;

Observe o seguinte na consulta anterior:

A /p1:ProductDescription/p1:Specifications/* expressão retorna filhos de elementos deSpecifications<> .
A expressão order by (local-name($a)) classifica a sequência pela parte local do nome de elemento.

Este é o resultado:

<Color>Available in most colors</Color>  
<Material>Aluminum Alloy</Material>  
<ProductLine>Mountain bike</ProductLine>  
<RiderExperience>Advanced to Professional riders</RiderExperience>  
<Style>Unisex</Style>

Os nós em que a expressão de classificação retorna vazia são classificados no início da sequência, como mostrado no exemplo a seguir:

declare @x xml  
set @x='<root>  
  <Person Name="A" />  
  <Person />  
  <Person Name="B" />  
</root>  
'  
select @x.query('  
  for $person in //Person  
  order by $person/@Name  
  return   $person  
')

Este é o resultado:

<Person />  
<Person Name="A" />  
<Person Name="B" />

Você pode especificar vários critérios de classificação, como mostrado no exemplo a seguir. A consulta neste exemplo classifica os <Employee> elementos primeiro por Título e, em seguida, por valores de atributo Administrador.

declare @x xml  
set @x='<root>  
  <Employee ID="10" Title="Teacher"        Gender="M" />  
  <Employee ID="15" Title="Teacher"  Gender="F" />  
  <Employee ID="5" Title="Teacher"         Gender="M" />  
  <Employee ID="11" Title="Teacher"        Gender="F" />  
  <Employee ID="8" Title="Administrator"   Gender="M" />  
  <Employee ID="4" Title="Administrator"   Gender="F" />  
  <Employee ID="3" Title="Teacher"         Gender="F" />  
  <Employee ID="125" Title="Administrator" Gender="F" /></root>'  
SELECT @x.query('for $e in /root/Employee  
order by $e/@Title ascending, $e/@Gender descending  
  
  return  
     $e  
')

Este é o resultado:

<Employee ID="8" Title="Administrator" Gender="M" />  
<Employee ID="4" Title="Administrator" Gender="F" />  
<Employee ID="125" Title="Administrator" Gender="F" />  
<Employee ID="10" Title="Teacher" Gender="M" />  
<Employee ID="5" Title="Teacher" Gender="M" />  
<Employee ID="11" Title="Teacher" Gender="F" />  
<Employee ID="15" Title="Teacher" Gender="F" />  
<Employee ID="3" Title="Teacher" Gender="F" />

Limitações de implementação

Estas são as limitações:

As expressões de classificação devem ser digitadas homogeneamente. Isso é verificado estaticamente.
A classificação de sequências vazias não pode ser controlada.
Não há suporte para as palavras-chave menos vazio, mais vazio e ordenação em order by

Confira também

Expressões XQuery

Partilhar via

Iteração e instrução FLWOR (XQuery)

Usando a cláusula let

Usando a cláusula where

Associação de variável múltipla em FLWOR

Usando a cláusula order by

Limitações de implementação

Confira também

Comentários

Recursos adicionais