XMLAGG (SQL)
構文
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 データ型です。
-
シンプルな 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 |
関連項目
-
集約関数の概要
-
%DLIST 集約関数
-
JSON_ARRAYAGG 集約関数
-
LIST 集約関数
-
XMLELEMENT 関数
-
SELECT 文