FLWOR ステートメントと繰り返し (XQuery)
XQuery では FLWOR 繰り返し構文が定義されています。FLWOR とは、for
、let
、where
、order by
、および return
の頭文字です。SQL Server 2005 では、let
はサポートされません。
FLWOR ステートメントの構成要素を次に示します。
- 1 つ以上の反復子変数を入力シーケンスにバインドする 1 つ以上の FOR 句。
入力シーケンスは、XPath 式などの他の XQuery 式でもかまいません。その場合、ノードのシーケンス、またはアトミック値のシーケンスのいずれかを指定します。アトミック値のシーケンスを構成するには、リテラルまたはコンストラクタ関数を使用できます。構成された XML ノードは、SQL Server 2005 の入力シーケンスとしては使用できません。 - 反復子変数。反復子変数には
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 2005 では異種シーケンスが許可されていません。つまり、アトミック値とノードが混在したシーケンスは使用できません。
次のクエリで示すように、繰り返しは 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>
...
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
句内の式の結果は、次の規則を順に適用してブール値に変換されます。この規則は整数を使用できない点を除き、パス式の述語を評価するときの規則と同じです。
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 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" />
実装の制限事項
制限事項を次に示します。
- FLWOR 式内で
let
を使用することはできません。 - 並べ替え式では、型を混在させないようにする必要があります。この点は静的にチェックされます。
- 空のシーケンスの並べ替えを制御することはできません。
order by
ではキーワード empty least、empty greatest、および collation を使用できません。