FLWOR ステートメントと繰り返し (XQuery)
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 の構築構文と共によく使用されます。
AdventureWorks2008R2 サンプル データベースの 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 句を使用すると、繰り返し出現する式に名前を付けて変数とすることで、式を参照できます。SQL Server 2008 では、let 変数に割り当てられた式は、変数がクエリ内で参照されるたびにクエリに挿入されます。つまり、ステートメントは 1 回だけではなく、式が参照される回数だけ実行されます。
AdventureWorks2008R2 データベース内の製造手順には、必要なツールとツールを使用する場所の情報が保存されています。次のクエリは、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 句を使用して、繰り返しの結果をフィルター選択できます。これについては、次の例で AdventureWorks2008R2 サンプル データベースを使用して説明します。
自転車を製造するときは、ワーク センター拠点をいくつか経て製造プロセスが進行します。ワーク センター拠点ごとに、一連の製造手順が定義されています。次のクエリは、あるモデルの自転車を製造するためのワーク センター拠点のうち、製造手順が 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 変数に対してクエリを指定しています。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 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 列から特定の顧客のすべての電話番号を取得します。結果は電話番号順に並べ替えます。
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
アトミック化 (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 = 293;
属性値による並べ替えも行えます。たとえば次のクエリでは、新しく作成した、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 を使用できません。