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

let 句の使用

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

AdventureWorks データベース内の製造手順には、必要なツールとツールを使用する場所の情報が保存されています。次のクエリは、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 句を使用して、繰り返しの結果をフィルタ選択できます。これについては、次の例で AdventureWorks サンプル データベースを使用して説明します。

自転車を製造するときは、ワーク センター拠点をいくつか経て製造プロセスが進行します。ワーク センター拠点ごとに、一連の製造手順が定義されています。次のクエリは、あるモデルの自転車を製造するためのワーク センター拠点のうち、製造手順が 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 句内の式の結果は、次の規則を順に適用してブール値に変換されます。この規則は整数を使用できない点を除き、パス式の述語を評価するときの規則と同じです。

1. where 式が空のシーケンスを返す場合、有効なブール値は False です。

2. where 式が単純な Boolean 型の値を 1 つ返す場合、その値が有効なブール値になります。

3. where 式が 1 つ以上のノードを含んだシーケンスを返す場合、有効なブール値は True です。

4. 上記以外の場合、静的エラーが発生します。

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 変数を定義します。

• $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 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 列から特定の顧客のすべての電話番号を取得します。結果は電話番号順に並べ替えます。

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.Contact
WHERE ContactID=3

アトミック化 (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.Contact
WHERE ContactID=3

属性値による並べ替えも行えます。たとえば次のクエリでは、新しく作成した、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" />

実装の制限事項

関連項目

概念