概要
XMLAGG 集約関数は、string-expr のすべての値を連結した文字列を返します。返り値は VARCHAR データ型です。
-
シンプルな XMLAGG (または XMLAGG ALL) は、選択された行の string-expr のすべての値で構成される連結文字列を含む文字列を返します。string-expr が NULL の行は、無視されます。
以下の 2 つの例は両方とも、Sample.Person テーブルの Home_State 列内にあるすべての値を連結した文字列を 1 つの値として返します。
SELECT XMLAGG(Home_State) AS All_State_Values
FROM Sample.Person
SELECT XMLAGG(ALL Home_State) AS ALL_State_Values
FROM Sample.Person
この結合文字列には重複値が含まれます。
-
XMLAGG DISTINCT は、選択された行の string-expr に対応するすべての個別 (一意の) 値で構成される連結文字列を返します (XMLAGG(DISTINCT col1))。string-expr が NULL の行は、無視されます。XMLAGG(DISTINCT BY(col2) col1) は、col2 値が個別 (一意) であるレコードのこれらの col1 フィールド値のみが含まれる連結文字列を返します。ただし、個別の col2 値には、個別値として NULL が 1 つ含まれる場合があります。
string-expr が NULL の行は、返り値から省略されます。空でない文字列の値が 1 つ以上返される場合、string-expr が空の文字列 ('') の行は、返り値から省略されます。NULL でない string-expr の値のみが空の文字列 ('') である場合、返り値は 1 つの空の文字列となります。
XMLAGG はデータ・ストリーム・フィールドをサポートしません。string-expr にストリーム・フィールドを指定すると、SQLCODE -37 が返されます。
XML および XMLAGG
XMLAGG の一般的な使用方法の 1 つに、列の各データ・アイテムにタグ付けすることがあります。これは以下の例で示すように、XMLAGG と XMLELEMENT を組み合わせて行います。
SELECT XMLAGG(XMLELEMENT("para",Home_State))
FROM Sample.Person
この結果、以下のような文字列が出力されます。
<para>LA</para><para>MN</para><para>LA</para><para>NH</para><para>ME</para>...
XMLAGG と ORDER BY
XMLAGG 関数は、複数の行のテーブル列の値を 1 つの文字列に連結します。すべての集約フィールドが評価された後に ORDER BY 節がクエリ結果セットに適用されるため、ORDER BY がこの文字列内の値の順序に直接影響することはありません。特定の状況下では、XMLAGG の結果が順番に表示されることがありますが、この順番を信頼すべきではありません。特定の集約結果値内にリストされる値を、明示的に順序付けすることはできません。
例
以下の例は、Sample.Person テーブルの FavoriteColors 列内にあるすべての個別値の連結文字列を作成します。したがって、すべての行の All_Colors 列の値は同じになります。ある行が FavoriteColors に NULL 値を持つと、この値は連結文字列に含まれなくなります。データ値は、内部形式で返されます。
SELECT Name,FavoriteColors,
XMLAGG(DISTINCT FavoriteColors) AS All_Colors_In_Table
FROM Sample.Person
ORDER BY FavoriteColors
以下の例は、"A" で始まる Home_State 値の連結文字列を返します。これは、個別の Home_State 値 (DISTINCT Home_State)、個別の Home_City 値に対応する Home_State 値 (DISTINCT BY(Home_City) Home_State、Home_City に対応する一意の NULL が 1 つ含まれる場合があります)、およびすべての Home_State 値を返します。
SELECT XMLAGG(DISTINCT Home_State) AS DistStates,
XMLAGG(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
XMLAGG(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
以下の例は、各州の Home_City 列内にあるすべての個別値の連結文字列を作成します。同じ州のすべての行には、その州の個別の市の値すべてのリストが含まれます。
SELECT Home_State, Home_City,
XMLAGG(DISTINCT Home_City %FOREACH(Home_State)) AS All_Cities_In_State
FROM Sample.Person
ORDER BY Home_State
以下の例では、%AFTERHAVING キーワードを使用します。HAVING 節の条件 (“C” または “K” で始まる名前) を満たす Name 値が 1 つ以上ある各 Home_State の行を返します。最初の XMLAGG 関数は、該当の州にある名前をすべて連結して構成した文字列を返します。2 番目の XMLAGG 関数は、HAVING 節の条件を満たす名前のみを連結して構成した文字列を返します。
SELECT Home_State,
XMLAGG(Name) AS AllNames,
XMLAGG(Name %AFTERHAVING) AS HaveClauseNames
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'C%' OR Name LIKE 'K%'
ORDER BY Home_state
例えば、以下の AutoClub のようなテーブルがあるとします。
Name |
Make |
Model |
Year |
Smith,Joe |
Pontiac |
Firebird |
1971 |
Smith,Joe |
Saturn |
SW2 |
1997 |
Smith,Joe |
Pontiac |
Bonneville |
1999 |
Jones,Scott |
Ford |
Mustang |
1966 |
Jones,Scott |
Mazda |
Miata |
2000 |
クエリは以下のとおりです。
SELECT DISTINCT Name, XMLAGG(Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'
これは、以下を返します。
Name |
String_Of_Makes |
Smith,Joe |
PontiacSaturnPontiac |
クエリは以下のとおりです。
SELECT DISTINCT Name, XMLAGG(DISTINCT Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'
これは、以下を返します。
Name |
String_Of_Makes |
Smith,Joe |
PontiacSaturn |