Instruction et itération FLWOR (XQuery)
S’applique à :
SQL Server
XQuery définit la syntaxe d'itération FLWOR. FLWOR est l'acronyme de for, let, where, order by et return.
Une instruction FLWOR est constituée des parties suivantes :
Une ou plusieurs clauses FOR qui relient une ou plusieurs variables d'itération aux séquences en entrée.
Les séquences en entrée peuvent être d'autres expressions XQuery telles que des expressions XPath. Il peut s'agir de séquences de nœuds ou de séquences de valeurs atomiques. Les séquences de valeurs atomiques peuvent être construites à l’aide de littéraux ou de fonctions de constructeur. Les nœuds XML construits ne sont pas autorisés en tant que séquences d’entrée dans SQL Server.
Une clause
letfacultative. Cette clause attribue une valeur à la variable donnée pour une itération spécifique. L'expression attribuée peut être une expression XQuery, telle qu'une expression XPath, et peut retourner une séquence de nœuds ou une séquence de valeurs atomiques. Les séquences de valeurs atomiques peuvent être construites à l'aide de littéraux ou de fonctions constructeur. Les nœuds XML construits ne sont pas autorisés en tant que séquences d’entrée dans SQL Server.Une variable d'itération. Il est possible d'associer une assertion de type à cette variable à l'aide du mot clé
as.Une clause
wherefacultative. Cette clause applique un prédicat de filtre sur l'itération.Une clause
order byfacultative.Expression
return. L'expression de la clausereturnconstruit le résultat de l'instruction FLWOR.
Par exemple, la requête suivante itère sur les <Step> éléments du premier emplacement de fabrication et retourne la valeur de chaîne des <Step> nœuds :
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)
')
Voici le résultat obtenu :
Manu step 1 at Loc 1 Manu step 2 at Loc 1 Manu step 3 at Loc 1
La requête suivante est très similaire à la précédente, à une exception près : elle porte sur la colonne Instructions, une colonne typée xml, de la table ProductModel. La requête itère sur toutes les étapes de fabrication, les éléments, <step> au premier emplacement du centre de travail d’un produit spécifique.
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
Notez les points suivants dans la requête précédente :
$Stepest la variable d'itération.L’expression de chemin d’accès,
//AWMI:root/AWMI:Location[1]/AWMI:step, génère la séquence d’entrée. Cette séquence est la séquence des enfants du <step> nœud d’élément du premier <Location> nœud d’élément.La clause de prédicat facultative,
where, n'est pas utilisée.L’expression
returnretourne une valeur de chaîne de l’élémentstep<> .
La fonction string (XQuery) est utilisée pour récupérer la valeur de chaîne du <step> nœud.
Voici le résultat partiel :
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. ....
Voici des exemples d'autres séquences en entrée autorisées :
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
Dans SQL Server, les séquences hétérogènes ne sont pas autorisées. En particulier, les séquences contenant un mélange de valeurs atomiques et de nœuds sont interdites.
L’itération est fréquemment utilisée avec la syntaxe de construction XML dans la transformation des formats XML, comme indiqué dans la requête suivante.
Dans l’exemple de base de données AdventureWorks, les instructions de fabrication stockées dans la colonne Instructions de la table Production.ProductModel ont la forme suivante :
<Location LocationID="10" LaborHours="1.2"
SetupHours=".2" MachineHours=".1">
<step>describes 1st manu step</step>
<step>describes 2nd manu step</step>
...
</Location>
...
La requête suivante construit un nouveau code XML contenant les éléments avec les <Location> attributs d’emplacement du centre de travail retournés en tant qu’éléments enfants :
<Location>
<LocationID>10</LocationID>
<LaborHours>1.2</LaborHours>
<SetupHours>.2</SetupHours>
<MachineHours>.1</MachineHours>
</Location>
...
Voici la requête :
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
Notez les points suivants dans la requête précédente :
L’instruction FLWOR récupère une séquence d’éléments <
Location> pour un produit spécifique.La fonction de données (XQuery) est utilisée pour extraire la valeur de chaque attribut afin qu’ils soient ajoutés au XML résultant en tant que nœuds de texte au lieu d’attributs.
L'expression de la clause RETURN construit le code XML dont vous avez besoin.
Voici un extrait du résultat :
<Location>
<LocationID>10</LocationID>
<LaborHours>2.5</LaborHours>
<SetupHours>0.5</SetupHours>
<MachineHours>3</MachineHours>
</Location>
<Location>
...
<Location>
...
Utilisation de la clause let
Vous pouvez utiliser la clause let pour nommer des expressions récurrentes auxquelles vous pouvez faire référence en faisant référence à la variable. L'expression attribuée à une variable let est insérée dans la requête chaque fois que la variable est référencée dans la requête. Cela signifie que l'instruction est exécutée autant de fois que l'expression est référencée.
Dans la base de données AdventureWorks2022, les instructions de fabrication contiennent des informations sur les outils requis et les emplacements où les outils sont utilisés. La requête suivante utilise la clause let pour répertorier les outils requis pour construire un modèle de production, ainsi que les emplacements où chaque outil est requis.
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
Utilisation de la clause where
Vous pouvez utiliser la clause pour filtrer les where résultats d’une itération. L'exemple suivant illustre ce concept.
Dans le cadre de la fabrication d'une bicyclette, le processus de fabrication passe par une série de postes de travail. Chaque poste de travail définit une séquence d'étapes de fabrication. La requête suivante récupère uniquement les postes de travail qui fabriquent un modèle de bicyclette et comportent moins de trois étapes de fabrication, Autrement dit, ils ont moins de trois <step> éléments.
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
Notez les points suivants par rapport à la requête ci-dessus :
Le
wheremot clé utilise la fonction count() pour compter le nombre d’éléments <step> enfants dans chaque emplacement du centre de travail.L'expression
returnconstruit le code XML qu'il vous faut d'après les résultats de l'itération.
Voici le résultat obtenu :
<Location LocationID="30"/>
Le résultat de l'expression de la clause where est converti en une valeur booléenne à l'aide des règles suivantes, dans l'ordre précisé. Il s'agit des mêmes règles que celles utilisées pour les prédicats dans les expressions de chemin, mis à part que les entiers ne sont pas autorisés :
Si l'expression
whererenvoie une séquence vide, sa valeur booléenne effective est False.Si l'expression
whererenvoie une valeur booléenne simple, cette valeur est la valeur booléenne effective.Si l'expression
whererenvoie une séquence contenant au moins un nœud, la valeur booléenne effective est True.Sinon, une erreur statique est générée.
Liaison de plusieurs variables dans FLWOR
Une seule expression FLWOR peut servir à lier plusieurs variables aux séquences en entrée. Dans l'exemple suivant, la requête est spécifiée sur une variable de type xml non typé. L’expression FLOWR retourne le premier <Step> élément enfant de chaque <Location> élément.
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)
')
Notez les points suivants dans la requête précédente :
L’expression
fordéfinit$Locet $FirstStepvariables.Les
twoexpressions,/ManuInstructions/Locationand$FirstStep in $Loc/Step[1], sont corrélées en ce sens que les valeurs de$FirstStepdépendent des valeurs de$Loc.L’expression associée à
$Locgénère une séquence d’élémentsLocation<>. Pour chaque <Location> élément,$FirstStepgénère une séquence d’un <Step> élément, un singleton.$Locest spécifié dans l'expression associée à la variable$FirstStep.
Voici le résultat obtenu :
Manu step 1 at Loc 1
Manu step 1 at Loc 2
La requête suivante est similaire, sauf qu’elle est spécifiée par rapport à la colonne Instructions, colonne xml typée, de la table ProductModel . La construction XML (XQuery) est utilisée pour générer le code XML souhaité.
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
Notez les points suivants par rapport à la requête ci-dessus :
La clause
fordéfinit deux variables,$WCet$S. L'expression associée à$WCgénère une séquence de postes de travail impliqués dans la fabrication d'une bicyclette (modèle de produit). L'expression de chemin affectée à la variable$Sgénère une séquence d'étapes pour chaque séquence de postes de travail de$WC.L’instruction return construit du code XML qui a un <
Step> élément qui contient l’étape de fabrication et l’attribut LocationID .L’espace de noms declare default element est utilisé dans le prolog XQuery afin que toutes les déclarations d’espace de noms dans le xml résultant s’affichent à l’élément de niveau supérieur. Le résultat est ainsi beaucoup plus facile à lire. Pour plus d’informations sur les espaces de noms par défaut, consultez Gestion des espaces de noms dans XQuery.
Voici le résultat partiel :
<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>
...
Utilisation de la clause order by
Dans XQuery, le tri est réalisé à l'aide de la clause order by dans l'expression FLWOR. Les expressions de tri passées à la order by clause doivent retourner des valeurs dont les types sont valides pour l’opérateur gt . Chaque expression de tri doit avoir pour résultat un singleton (une séquence composée d'un seul et unique élément). Par défaut, le tri s'effectue en ordre croissant, mais vous pouvez spécifier l'ordre croissant ou décroissant pour chaque expression de tri.
Notes
Les comparaisons de tri sur les valeurs de chaîne effectuées par l’implémentation XQuery dans SQL Server sont toujours effectuées à l’aide du classement de points de code Unicode binaire.
La requête suivante récupère tous les numéros de téléphone existants pour un client spécifique à partir de la colonne AdditionalContactInfo. Les résultats sont triés par numéro de téléphone.
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;
Notez que le processus d’atomisation (XQuery) récupère la valeur atomique des éléments avant de la <number> passer à .order by Vous pouvez écrire l’expression à l’aide de la fonction data(), mais cela n’est pas obligatoire.
order by data($a/act:number[1]) descending
Voici le résultat obtenu :
<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>
Au lieu de déclarer les espaces de noms dans le prologue de la requête, vous pouvez les déclarer à l'aide de 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;
Vous pouvez aussi réaliser le tri en fonction de la valeur d'attribut. Par exemple, la requête suivante récupère les éléments nouvellement créés <Location> qui ont les attributs LocationID et LaborHours triés par l’attribut LaborHours dans l’ordre décroissant. Par conséquent, les postes de travail enregistrant le plus d'heures de main-d'œuvre sont renvoyés en premier.
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;
Voici le résultat obtenu :
<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"/>
Dans la requête suivante, les résultats sont triés par le nom d'élément. La requête récupère les spécifications d'un produit particulier dans le catalogue de produits. Les spécifications sont les enfants de l’élément <Specifications> .
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;
Notez les points suivants dans la requête précédente :
L’expression
/p1:ProductDescription/p1:Specifications/*retourne l’élément enfants deSpecifications<> .L'expression
order by (local-name($a))trie la séquence par la partie locale du nom d'élément.
Voici le résultat obtenu :
<Color>Available in most colors</Color>
<Material>Almuminum Alloy</Material>
<ProductLine>Mountain bike</ProductLine>
<RiderExperience>Advanced to Professional riders</RiderExperience>
<Style>Unisex</Style>
Les nœuds dans lesquels l'expression de tri renvoie une valeur vide sont placés au début de la séquence, comme le montre l'exemple suivant :
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
')
Voici le résultat obtenu :
<Person />
<Person Name="A" />
<Person Name="B" />
Vous pouvez spécifier plusieurs critères de tri, comme le montre l'exemple suivant. La requête de cet exemple trie d’abord les <Employee> éléments par Titre, puis par valeurs d’attribut Administrateur.
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
')
Voici le résultat obtenu :
<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" />
Limites de mise en œuvre
Les limitations suivantes s'appliquent :
Les expressions de tri doivent être homogènes en ce qui concerne le type. Le contrôle s'effectue de façon statique.
Le tri des séquences vides ne peut pas être contrôlé.
Les mots clés empty least, empty greatest et collation de
order byne sont pas pris en charge.
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour