XQuery の一般的な使用例
このトピックでは、XQuery の一般的な使用例について説明します。
例
A. 製品と重量を検索するためのカタログ説明のクエリ
次のクエリでは、製品カタログの説明に製品モデル ID と重量が含まれている場合、これらの情報を返します。クエリでは、次の形式で XML が構築されます。
<Product ProductModelID="E>
<Weight>Elt;/Weight>
</Product>
クエリは次のとおりです。
SELECT CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product ProductModelID="{ (/p1:ProductDescription/@ProductModelID)[1] }">
{
/p1:ProductDescription/p1:Specifications/Weight
}
</Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not null
上のクエリに関して、次の点に注意してください。
- XQuery プロローグ内の namespace キーワードにより、クエリ本文で使用する名前空間プレフィックスが定義されています。
- クエリ本文で、必要な XML が構築されます。
- WHERE 句では、exist() メソッドを使用して製品カタログの説明を含む行のみを検索しています。つまり、<
ProductDescription> 要素が含まれる XML です。
次に結果を示します。
<Product ProductModelID="19"/>
<Product ProductModelID="23"/>
<Product ProductModelID="25"/>
<Product ProductModelID="28"><Weight>Varies with size.</Weight></Product>
<Product ProductModelID="34"/>
<Product ProductModelID="35"/>
次のクエリは前と同じ情報を取得しますが、カタログの説明で、仕様部分 (<Specifications> 要素) に重量 (<Weight> 要素) が含まれる製品モデルのみを取得します。この例では、WITH XMLNAMESPACES を使用して pd プレフィックスとこれにバインドされる名前空間を定義しています。このため、バインドは query() メソッドと exist() メソッドに記述されていません。
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT CatalogDescription.query('
<Product ProductModelID="{ (/pd:ProductDescription/@ProductModelID)[1] }">
{
/pd:ProductDescription/pd:Specifications/Weight
}
</Product>
') as x
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Specifications//Weight ') = 1
上のクエリでは、WHERE 句内の xml データ型の exist() メソッドにより、<Specifications> 要素内に <Weight> 要素があるかどうかを確認しています。
B. カタログの説明に正面からの小さな製品写真が含まれる製品モデルの製品モデル ID の検索
XML 製品カタログの説明には製品の写真 (<Picture> 要素) が含まれています。各写真には、いくつかのプロパティがあります。プロパティには、写真の撮影方向 (<Angle> 要素) と写真のサイズ (<Size> 要素) などがあります。
カタログの説明に正面からの小さな写真が含まれる製品モデルを取得するために、クエリは次の形式の XML を構築します。
< Product ProductModelID="E>
<Picture>
<Angle>front</Angle>
<Size>small</Size>
</Picture>
</Product>
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{ (/pd:ProductDescription/@ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]', 'varchar(20)') = 'front'
AND CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]', 'varchar(20)') = 'small'
上のクエリに関して、次の点に注意してください。
- WHERE 句は exist() メソッドを使用して、<
Picture> 要素のある製品カタログの説明を含む行のみを検索しています。 - WHERE 句では value() メソッドを 2 回使用して、<
Size> 要素と <Angle> 要素の値を比較しています。
次に結果の一部を示します。
<p1:Product
xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"
ProductModelID="19">
<Picture>
<p1:Angle>front</p1:Angle>
<p1:Size>small</p1:Size>
</Picture>
</p1:Product>
...
C. 製品モデルの名前と特徴を組にし、各組を <Features> 要素で囲んだフラットなリストの作成
製品モデル カタログの説明の XML には、いくつか製品の特徴が含まれています。これらの特徴のデータは、<Features> 要素に含まれています。このクエリでは、「XML の構築 (XQuery)」を使用して、必要な XML を構築しています。中かっこに囲まれた式が、結果に置き換えられます。
SELECT CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in /p1:ProductDescription,
$f in $pd/p1:Features/*
return
<Feature>
<ProductModelName> { data($pd/@ProductModelName) } </ProductModelName>
{ $f }
</Feature>
') as x
FROM Production.ProductModel
WHERE ProductModelID=19
上のクエリに関して、次の点に注意してください。
- $pd/p1:Features/* は、<
Features> の子要素ノードしか返しませんが、$pd/p1:Features/node() はすべてのノードを返します。返されるノードには、要素ノード、テキスト ノード、処理命令、コメントがあります。 - 2 つの FOR ループにより、製品名と個々の特徴を返すデカルト積が生成されます。
- ProductName は属性です。このクエリで構築された XML では、この属性を要素として返します。
次に結果の一部を示します。
<Feature>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p1:Warranty
xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
<p1:WarrantyPeriod>3 year</p1:WarrantyPeriod>
<p1:Description>parts and labor</p1:Description>
</p1:Warranty>
</Feature>
<Feature>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p2:Maintenance xmlns:p2="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
<p2:NoOfYears>10</p2:NoOfYears>
<p2:Description>maintenance contact available through your dealer
or any AdventureWorks retail store.</p2:Description>
</p2:Maintenance>
</Feature>
...
...
D. 製品モデルのカタログの説明から製品モデルの名前、モデル ID、特徴のデータを取得し、<Product> 要素で囲んだ一覧の作成
次のクエリは、製品モデルのカタログの説明内の情報を使用して、製品モデルの名前、モデル ID、特徴のデータを取得し、<Product> 要素で囲んだ一覧を作成します。
SELECT ProductModelID, CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product>
<ProductModelName>
{ data(/pd:ProductDescription/@ProductModelName) }
</ProductModelName>
<ProductModelID>
{ data(/pd:ProductDescription/@ProductModelID) }
</ProductModelID>
{ /pd:ProductDescription/pd:Features/* }
</Product>
') as x
FROM Production.ProductModel
WHERE ProductModelID=19
結果の一部を次に示します。
<Product>
<ProductModelName>Mountain 100</ProductModelName>
<ProductModelID>19</ProductModelID>
<p1:Warranty>... </p1:Warranty>
<p2:Maintenance>... </p2:Maintenance>
<p3:wheel xmlns:p3="https://www.adventure-works.com/schemas/OtherFeatures">High performance wheels.</p3:wheel>
<p4:saddle xmlns:p4="https://www.adventure-works.com/schemas/OtherFeatures">
<p5:i xmlns:p5="http://www.w3.org/1999/xhtml">Anatomic design</p5:i> and made from durable leather for a full-day of riding in comfort.</p4:saddle>
<p6:pedal xmlns:p6="https://www.adventure-works.com/schemas/OtherFeatures">
<p7:b xmlns:p7="http://www.w3.org/1999/xhtml">Top-of-the-line</p7:b> clipless pedals with adjustable tension.</p6:pedal>
...
E. 製品モデルの特徴の説明の取得
次の要素は、ProducModelID 属性、ProductModelName 属性、および最初の 2 製品の特徴のデータを保持する <Product> 要素を含む XML を構築します。具体的には、最初の 2 製品の特徴は、<Features> 要素の最初の 2 つの子要素です。それ以上に特徴がある場合は、空の <There-is-more/> 要素が返されます。
SELECT CatalogDescription.query('
declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product>
{ /pd:ProductDescription/@ProductModelID }
{ /pd:ProductDescription/@ProductModelName }
{
for $f in /pd:ProductDescription/pd:Features/*[position()<=2]
return
$f
}
{
if (count(/pd:ProductDescription/pd:Features/*) > 2)
then <there-is-more/>
else ()
}
</Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
上のクエリに関して、次の点に注意してください。
- FOR ...RETURN ループ部分により、最初の 2 つの製品の特徴情報が取得されます。ここでは position() 関数により、シーケンス内の要素の位置を指定しています。
F. 製品カタログの説明からの "ons" で終わる要素名の検索
次のクエリでは、カタログの説明内を検索し、<ProductDescription> 要素から名前が "ons"で終わる要素をすべて返します。
SELECT ProductModelID, CatalogDescription.query('
declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
for $pd in /p1:ProductDescription/*[substring(local-name(.),string-length(local-name(.))-2,3)="ons"]
return
<Root>
{ $pd }
</Root>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
結果の一部を次に示します。
ProductModelID Result
-----------------------------------------
19 <Root>
<p1:Specifications xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
...
</p1:Specifications>
</Root>
G. "Aerodynamic"という語を含む概要説明の検索
次のクエリでは、カタログの説明の概要説明に "Aerodynamic" という語を含む製品モデルを取得します。
WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT ProductModelID, CatalogDescription.query('
<Prod >
{ /pd:ProductDescription/@ProductModelID }
{ /pd:ProductDescription/pd:Summary }
</Prod>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.value('
contains( string( (/pd:ProductDescription/pd:Summary)[1] ),"Aerodynamic")','bit') = 1
SELECT クエリに xml データ型の query() メソッドと value() メソッドが指定されています。このため、2 つの異なるクエリ プロローグ内でそれぞれ名前空間の宣言を繰り返す手間を省き、クエリ内で使用されるプレフィックス pd を WITH XMLNAMESPACES を使用して一度だけ定義しています。
上のクエリに関して、次の点に注意してください。
- WHERE 句を使用して、カタログの説明の <
Summary> に "Aerodynamic" という語が含まれる行のみを取得しています。 - contains() 関数を使用して、目的の語がテキストに含まれているかどうかを確認しています。
- xml データ型の value() メソッドにより、contains() により返された値と 1 を比較しています。
次に結果を示します。
ProductModelID Result
-------------- ------------------------------------------
28 <Prod ProductModelID="28">
<pd:Summary xmlns:pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">
<p1:p xmlns:p1="http://www.w3.org/1999/xhtml">
A TRUE multi-sport bike that offers streamlined riding and a
revolutionary design. Aerodynamic design lets you ride with the
pros, and the gearing will conquer hilly roads.</p1:p>
</pd:Summary>
</Prod>
H. カタログの説明に製品モデルの写真が含まれない製品モデルの検索
次のクエリは、カタログの説明に <Picture> 要素が含まれない製品モデルの ProductModelID を取得します。
SELECT ProductModelID
FROM Production.ProductModel
WHERE CatalogDescription is not NULL
AND CatalogDescription.exist('declare namespace p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
/p1:ProductDescription/p1:Picture
') = 0
上のクエリに関して、次の点に注意してください。
- WHERE 句の exist() メソッドが False (0) を返した場合、製品モデル ID は返されません。それ以外の場合は、製品モデル ID が返されます。
- この例では、<
Picture> 要素はすべての製品の説明に含まれているので、結果セットは空になります。
参照
関連項目
階層に関係する XQuery
順序に関係する XQuery
リレーショナル データを処理する XQuery
XQuery での名前空間の処理
概念
XQuery での文字列検索
WITH XMLNAMESPACES を使用した名前空間の追加
xml データ型
xml データ型
その他の技術情報
xml データ型に対する XQuery
AdventureWorks データベースの xml データ型表現
xml データ型に対する XQuery