Instruction et itération FLWOR (XQuery)

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. Notez que dans SQL Server 2008, 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 n'est pas exécutée une seule fois, mais autant de fois que l'expression est référencée.

Dans la base de données AdventureWorks2008R2, 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 where pour filtrer les résultats d'une itération. L'exemple suivant illustre ce concept en s'appuyant sur l'exemple de base de données AdventureWorks2008R2.

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, soit moins de trois éléments <step>.

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 mot clé where utilise la fonction count() pour compter le nombre d'éléments enfants <step> dans chaque poste de travail.

• L'expression return construit 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 :

1. Si l'expression where renvoie une séquence vide, sa valeur booléenne effective est False.

2. Si l'expression where renvoie une valeur booléenne simple, cette valeur est la valeur booléenne effective.

3. Si l'expression where renvoie une séquence contenant au moins un nœud, la valeur booléenne effective est True.

4. 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 renvoie le premier enfant de l'élément <Step> de chaque élément <Location>.

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 à propos de la requête précédente :

• L'expression for définit les variables $Loc et $FirstStep.

• Les deux expressions, /ManuInstructions/Location et $FirstStep in $Loc/Step[1], sont corrélées en ce sens que les valeurs de $FirstStep dépendent des valeurs de $Loc.

• L'expression associée à $Loc génère une séquence d'éléments <Location>. Pour chaque élément <Location>, $FirstStep génère une séquence à un seul élément <Step>, soit un singleton.

• $Loc est 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 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 construction XML (XQuery) sert à générer le code XML dont vous avez besoin.

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 for définit deux variables, $WC et $S. L'expression associée à $WC gé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 $S génère une séquence d'étapes pour chaque séquence de postes de travail de $WC.

• L'instruction return construit le code XML où figure un élément <Step> ayant pour attributs l'étape de fabrication et LocationID.

• L'instruction declare default element namespace est utilisée dans le prologue XQuery de façon à ce que toutes les déclarations d'espace de noms dans le code résultant apparaissent avec l'élément de premier niveau. 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 transmises à la clause order by doivent renvoyer des valeurs dont les types sont acceptés par 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.

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.

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

Notez que le processus d'atomisation (XQuery) récupère la valeur atomique des éléments <number> avant de la transmettre à order by. Vous pouvez écrire l'expression à l'aide de la fonction data(), mais rien ne vous y oblige.

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 = 293;

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 <Location> nouvellement créés dont les attributs LocationID et LaborHours sont triés dans l'ordre décroissant par l'attribut LaborHours. 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 à propos de la requête précédente :

• L'expression /p1:ProductDescription/p1:Specifications/* renvoie les enfants de <Specifications>.

• 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. Dans cet exemple, la requête trie les éléments <Employee> d'abord par Title, puis par les valeurs d'attribut Administrator.

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 la mise en œuvre

Voir aussi

Concepts