Iteração e instrução FLWOR (XQuery)
Aplica-se a:
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. Nós XML construídos não são permitidos como sequências de entrada em SQL Server.
Uma cláusula
letopcional. 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. Nós XML construídos não são permitidos como sequências de entrada em 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
whereopcional. Essa cláusula aplica um predicado de filtro na iteração.Uma cláusula
order byopcional.Uma expressão
return. A expressão na cláusulareturnconstró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 para 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ó de elemento do primeiro <Location> nó de elemento.A cláusula de predicado opcional,
where, não é usada.A
returnexpressã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
Em 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 é frequentemente usada junto com a sintaxe de Construção XML 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 da central 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 where cláusula para filtrar os 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:
O
wherepalavra-chave usa a função count() para contar o número de elementos filho em cada local do centro de <step> trabalho.A expressão
returnconstró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
whereretornar uma sequência vazia, seu valor Booliano efetivo será False.Se a expressão
whereretornar um valor de tipo Booliano simples, esse valor será o valor Booliano efetivo.Se a expressão
whereretornar 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> elemento filho 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
forexpressão define$Loce $FirstStepvariables.As expressões
two,/ManuInstructions/Locatione$FirstStep in $Loc/Step[1], são correlacionadas pelo fato de que os valores de$FirstStepdependem dos valores de$Loc.A expressão associada
$Loca gera uma sequência de <Location> elementos. Para cada <Location> elemento,$FirstStepgera 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 Instruções, coluna xml digitada, da tabela ProductModel . A XQuery (Construção XML) é 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
fordefine duas variáveis,$WCe$S. A expressão associada com$WCgera 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$Sgera 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 declarar elemento padrã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 do XQuery em SQL Server são sempre executadas usando a ordenação binária de ponto de código Unicode.
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 <number> passá-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 os filhos 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 os filhos do elemento 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>Almuminum 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 elementos <Employee> 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
Consulte Também
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários