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

[アーティクル]
05/07/2013

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 テーブルの型指定された 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 は反復子変数です。
パス式 //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 の構築構文と共によく使用されます。

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</SteupHours>
   <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 変数に割り当てられた式は、変数がクエリ内で参照されるたびにクエリに挿入されます。つまり、ステートメントは、式が参照される回数だけ実行されます。

AdventureWorks2012 データベース内の製造手順には、必要なツールとツールを使用する場所の情報が保存されています。次のクエリは、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 工程未満の拠点を取得します。つまり、<step> 要素が 3 つ未満のものだけが取得されます。

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 変数に対してクエリを指定しています。 FLWOR 式が、各 <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 変数を定義します。
$FirstStep の値が $Loc の値に依存しているので、2 つの式 /ManuInstructions/Location と $FirstStep in $Loc/Step[1] には相関関係があります。
$Loc に関連付けられた式により、<Location> 要素のシーケンスが生成されます。各 <Location> 要素について、$FirstStep により 1 つの <Step> 要素から成るシーケンス (シングルトン) が生成されます。
$Loc は、$FirstStep 変数に関連付けられた式で指定しています。

結果を次に示します。

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

次のクエリは、上のクエリと似ていますが、ProductModel テーブルの型指定された xml 列である Instructions 列を対象にしている点が異なります。 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 のプロローグで declare default element namespace を使用しています。これにより、結果が読みやすくなります。既定の名前空間の詳細については、「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 つのシーケンス) になる必要があります。既定の並べ替えは昇順です。並べ替え式ごとに昇順か降順かをオプションとして選択できます。

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

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

USE AdventureWorks2012;
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;

アトミック化 (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 属性を含む <Location> 要素を LaborHours 属性の降順で並べ替えて取得します。結果として、労働時間が最も長いワークセンター拠点が最初に返されます。

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 を使用できません。

次の方法で共有

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

let 句の使用

where 句の使用

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

order by 句の使用

実装の制限事項

関連項目

概念

その他のリソース