XMLAGG

値を連結した文字列を作成する集約関数です。

Synopsis

XMLAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])

引数

ALL	オプション — XMLAGG が string-expr のすべての値を結合した文字列を返すように指定します。キーワードが指定されていない場合は、これが既定になります。
DISTINCT	オプション — 一意の string-expr 値のみを含む連結文字列を XMLAGG が返すように指定する DISTINCT 節。DISTINCT で BY(col-list) 従属節を指定できます。col-list には 1 つのフィールド、またはコンマ区切りのフィールドのリストを指定できます。
string-expr	文字列として評価する SQL 式。一般的には、これはデータを取得する列の名前です。
%FOREACH(col-list)	オプション — 列名、またはコンマで区切られた列名のリスト。%FOREACH の詳細は、"SELECT" を参照してください。
%AFTERHAVING	オプション — HAVING 節にある条件を適用します。

概要

XMLAGG 集約関数は、string-expr のすべての値を連結した文字列を返します。返り値は、データ型 VARCHAR で、既定の長さは 4096 です。

シンプルな 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