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

[アーティクル]
11/19/2024

適用対象: 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 では入力シーケンスとして使用できません。
反復子変数。この変数は、 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 テーブルの Instructions 列 (型指定された xml 列) に対して指定されている点が異なります。クエリは、特定の製品の最初のワークセンターの場所にあるすべての製造ステップ (要素 <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>要素から文字列値を返します。

string 関数 (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 Construction 構文と共に頻繁に使用されます。

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> 要素のシーケンスを取得します。
data 関数 (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

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

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式は、$Loc変数と $FirstStep 変数を定義します。
two式 (/ManuInstructions/Locationと$FirstStep in $Loc/Step[1]) は、$FirstStepの値が$Locの値に依存するという点で相関しています。
$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 を構築します。
既定の要素名前空間を宣言は、結果の XML 内のすべての名前空間宣言が最上位要素に表示されるように、XQuery プロローグで使用されます。これにより、結果が読みやすくなります。既定の名前空間の詳細については、「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 句を使用して実行されます。 order by句に渡される並べ替え式は、gt 演算子に対して有効な型の値を返す必要があります。各並べ替え式は、1 つの項目を持つシーケンスをシングルトンにする必要があります。既定では、並べ替えは昇順で実行されます。必要に応じて、並べ替え式ごとに昇順または降順を指定できます。

Note

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) プロセスでは、order byに渡す前に、<number>要素のアトミック値が取得されることに注意してください。 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>Aluminum 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 句の使用

実装の制限事項

参照

フィードバック

その他のリソース