FLWOR ステートメントと繰り返し (XQuery)

[アーティクル]
04/03/2023

適用対象:SQL Server

XQuery は FLWOR 反復構文を定義します。 FLWOR とは、for、let、where、order by、および return の頭文字です。

FLWOR ステートメントは、次の部分で構成されます。

1 つ以上の反復子変数を入力シーケンスにバインドする 1 つ以上の FOR 句。

入力シーケンスには、XPath 式などの他の XQuery 式を指定できます。ノードのシーケンスまたはアトミック値のシーケンスです。アトミック値シーケンスは、リテラルまたはコンストラクター関数を使用して構築できます。構築された XML ノードは、SQL Serverの入力シーケンスとして使用できません。
省略可能な let 句。この句は、特定の繰り返し処理の変数に値を割り当てます。代入された式には、XPath 式などの XQuery 式を指定できます。また、ノードのシーケンスまたはアトミック値のシーケンスを返すことができます。アトミック値シーケンスは、リテラルまたはコンストラクター関数を使用して構築できます。構築された XML ノードは、SQL Serverの入力シーケンスとして使用できません。
反復子変数。この変数には、キーワード (keyword)を使用して、省略可能な型アサーションをas指定できます。
省略可能な where 句。繰り返しにフィルター述語を適用します。
省略可能な order by 句。
return 式。句の return 式は、FLWOR ステートメントの結果を作成します。

たとえば、次のクエリは、最初の <Step> 製造場所にある要素を反復処理し、ノードの文字列値を <Step> 返します。

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)  
')

結果を次に示します。

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

次のクエリは、ProductModel テーブルの型指定された xml 列である Instructions 列に対して指定されている点を除き、前のクエリと似ています。クエリは、 <step> 特定の製品の最初の作業センターの場所ですべての製造ステップ (要素) を反復処理します。

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

上のクエリに関して、次の点に注意してください。

$Step は反復子変数です。
path 式は、//AWMI:root/AWMI:Location[1]/AWMI:step入力シーケンスを生成します。このシーケンスは、最初><Locationの要素ノードの<step>要素ノードの子のシーケンスです。
省略可能な述語句 where は使用しません。
式は return 、要素から文字列値を <step> 返します。

文字列関数 (XQuery) は、ノードの<step>文字列値を取得するために使用されます。

結果の一部を次に示します。

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. ....

許可される追加の入力シーケンスの例を次に示します。

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

SQL Serverでは、異種シーケンスは使用できません。具体的には、アトミック値とノードの組み合わせを含むシーケンスは許可されません。

反復は、次のクエリに示すように、XML 形式の変換で XML 構築構文と共に頻繁に使用されます。

AdventureWorks サンプルデータベースでは、Production.ProductModel テーブルの Instructions 列に格納されている製造指示の形式は次のとおりです。

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

次のクエリでは、子要素として返されるワークセンターの場所属性を持つ要素を含 <Location> む新しい XML を構築します。

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

クエリは次のとおりです。

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

上のクエリに関して、次の点に注意してください。

FLWOR ステートメントは、特定の製品の要素の <Location> シーケンスを取得します。
データ関数 (XQuery) は、各属性の値を抽出するために使用されるため、結果の XML に属性としてではなくテキストノードとして追加されます。
RETURN 句の式で、必要な XML を生成します。

これは部分的な結果です。

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

let 句の使用

句を let 使用すると、変数を参照して参照できる繰り返し式に名前を付けることができます。 let 変数に割り当てられた式は、変数がクエリ内で参照されるたびにクエリに挿入されます。これは、式が参照される回数だけステートメントが実行されることを意味します。

データベースの AdventureWorks2022 製造手順には、必要なツールとツールが使用されている場所に関する情報が含まれています。次のクエリは、let 句を使用して、製品モデルの作成に必要なツールと、それぞれのツールが必要となる場所を一覧表示します。

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

where 句の使用

句を where 使用して、イテレーションの結果をフィルター処理できます。このことを次の例で説明します。

自転車の製造では、製造プロセスは一連のワークセンターの場所を通過します。各作業センターの場所では、一連の製造手順が定義されます。次のクエリでは、自転車モデルを製造し、3 つ未満の製造手順を持つワークセンターの場所のみを取得します。つまり、3 つ <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

上のクエリに関して、次の点に注意してください。

キーワード (keyword)ではwhere、count() 関数を使用して、各作業センターの場所にある子要素の<step>数をカウントします。
式は return 、イテレーションの結果から必要な XML を構築します。

結果を次に示します。

<Location LocationID="30"/>

where 句内の式の結果は、次の規則を順に適用してブール値に変換されます。これらは、パス式の述語の規則と同じですが、整数は許可されません。

式が空の where シーケンスを返す場合、有効なブール値は False です。
where 式が単純な Boolean 型の値を 1 つ返す場合、その値が有効なブール値になります。
式が where 少なくとも 1 つのノードを含むシーケンスを返す場合、有効なブール値は True です。
それ以外の場合は、静的エラーが発生します。

FLWOR での複数の変数のバインド

1 つの FLWOR 式で入力シーケンスに複数の変数をバインドできます。次の例では、型指定されていない xml 変数に対してクエリを指定します。 FLOWR 式は、各>Location<要素の最初<Step>の要素の子を返します。

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)  
')

上のクエリに関して、次の点に注意してください。

式は for および $FirstStep 変数を定義します$Loc。
式と $FirstStep in $Loc/Step[1]はtwo、 /ManuInstructions/Location の値がの値$Loc$FirstStepに依存するという点で相関しています。
に $Loc 関連付けられた式は、要素の <Location> シーケンスを生成します。要素ごとに <Location> 、 $FirstStep 1 つの <Step> 要素のシーケンス (シングルトン) を生成します。
$Loc は、変数に関連付 $FirstStep けられている式で指定されます。

結果を次に示します。

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

次のクエリは、ProductModel テーブルの Instructions 列 (型指定された xml 列) に対して指定される点を除き、同様です。 XML 構築 (XQuery) は、必要な XML を生成するために使用されます。

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

上のクエリに関して、次の点に注意してください。

for 句で 2 つの変数 $WC および $S を定義します。 $WC に関連付けられた式により、ある製造モデルの自転車の製造で使用されるワークセンター拠点のシーケンスが生成されます。 $S 変数に代入されたパス式は、$WC で示すワークセンター拠点のシーケンスごとに製造手順のシーケンスを生成します。
return ステートメントは、製造ステップとその属性として LocationID を含む要素を持つ <Step> XML を構築します。
既定の要素の宣言名前空間は XQuery プロローグで使用され、結果の XML 内のすべての名前空間宣言が最上位の要素に表示されます。これにより、結果が読みやすくなります。既定の名前空間の詳細については、「 XQuery での名前空間の処理」を参照してください。

結果の一部を次に示します。

<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>  
...

order by 句の使用

XQuery での並べ替えは、FLWOR 式の order by 句を使用して実行されます。句に渡される並べ替え式は、gt 演算子に対してorder by有効な型の値を返す必要があります。各並べ替え式では、1 つの項目を持つシーケンスがシングルトンになる必要があります。既定では、並べ替えは昇順で実行されます。必要に応じて、並べ替え式ごとに昇順または降順を指定できます。

注意

SQL Server の XQuery 実装によって実行される文字列値の並べ替え比較は、常にバイナリ Unicode コードポイント照合順序を使用して実行されます。

次のクエリでは、AdditionalContactInfo 列から特定の顧客のすべての電話番号を取得します。結果は電話番号順に並べ替えます。

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;

Atomization (XQuery) プロセスでは、に渡す前に要素のアトミック値が<number>取得されることに注意してください。order by 式は data() 関数を使用して記述できますが、必須ではありません。

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

結果を次に示します。

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

名前空間は、クエリのプロローグではなく 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;

属性値で並べ替えることもできます。たとえば、次のクエリでは、LocationID 属性と LaborHours 属性を LaborHours 属性で降順に並べ替えた新しく作成 <Location> された要素を取得します。その結果、最大労働時間を持つワークセンターの場所が最初に返されます。

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;

結果を次に示します。

<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"/>

次のクエリは、結果を要素名順に並べ替えます。クエリは、製品カタログから特定の製品の仕様を取得します。仕様は、要素の <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;

上のクエリに関して、次の点に注意してください。

式は /p1:ProductDescription/p1:Specifications/* 、の要素の <Specifications>子を返します。
式は order by (local-name($a)) 、要素名のローカル部分でシーケンスを並べ替えます。

結果を次に示します。

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

次の例に示すように、順序付け式が空を返すノードは、シーケンスの先頭に並べ替えられます。

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  
')

結果を次に示します。

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

次の例に示すように、複数の並べ替え条件を指定できます。この例のクエリでは、最初に要素を <Employee> Title で並べ替え、次に 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  
')

結果を次に示します。

<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" />

実装の制限事項

制限事項は次のとおりです。

並べ替え式では、型を混在させないようにする必要があります。これは静的にチェックされます。
空のシーケンスの並べ替えは制御できません。
order by ではキーワード empty least、empty greatest、および collation を使用できません。

参照

XQuery 式

次の方法で共有

FLWOR ステートメントと繰り返し (XQuery)

let 句の使用

where 句の使用

FLWOR での複数の変数のバインド

order by 句の使用

実装の制限事項

参照

フィードバック

その他のリソース