Aggregate results in SQLAnywhere 16 (For XML replacement)

Question

I'm using Ultralite 16.0 and trying to aggregate multiple rows into one.

I have a film table and a genre table, that have a many-to-many relationship through the film_genre table.

I want a result like this:

| mFilm     | idGenre |
| --------- | ------- |
| Film_One  | 1, 2    |
| Film_Two  | 1, 3, 4 |

I can do this easily in SQL Server Management Studio (SSMS) with

SELECT f.nmFilm, 
(
  REPLACE(
      STUFF(
          (SELECT g.idGenre FROM genre g
              JOIN film_genre fg on g.idGenre = fg.idGenre
              WHERE fg.idFilm = f.idFilm
              FOR XML PATH('')
          ) , 1 , 1 , ''
      )
  , '&', '&')
) AS genres FROM film f;

However, any time I try to use FOR XML in SQLAnywhere such as in the query below

SELECT f.nmFilm, 
    (SELECT g.idGenre FROM genre g
      JOIN film_genre fg on g.idGenre = fg.idGenre
      WHERE fg.idFilm = f.idFilm
      FOR XML AUTO
    ) AS genres FROM film f;

I get syntax error

Could not execute statement. [UltraLite Database] Syntax error near 'XML' [SQL Offset: 57] SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

I can't find any reference to FOR XML in the documentation, so I'm unsure if this is available in version 16.0.

How could I achieve this?

Answer 1

In case someone else needs this, all I had to do was use the LIST() function.

SELECT f.nmFilm, 
  (SELECT LIST(g.idGenre) FROM genre g
    JOIN film_genre fg on g.idGenre = fg.idGenre
    WHERE fg.idFilm = f.idFilm
  ) AS genres
FROM film f;