FOR XML Syntac error UDB 16

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 , ''
      )
  , '&amp;', '&')
) 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?

xml ultralite

asked 10 Jul '22, 19:31

almonddaves
26●1●3
accept rate: 0%

FOR XML is not supported in an UltraLite SELECT statement.

(10 Jul '22, 23:58) Chris Keating

flat view

One Answer:

active answers oldest answers newest answers popular answers

Welp. It was a lot easier than I thought. Just had to use List(). Hope this helps someone else!

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;

permanent link

answered 10 Jul '22, 19:32

almonddaves
26●1●3
accept rate: 0%

Yes, LIST() is a very helpful aggregate SQL Anywhere has had for times. MS SQL Server added a similar STRING_AGG aggregate function with SQL Server 17, AFAIK.

Note, it's often helpful to sort the entries in the list, say via

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

Additionally, as LIST is an aggregate function, you would usually use it with a GROUP BY, so you should easily be able to use a query without the need for a separate sub select in the SELECT clause.

(11 Jul '22, 03:16) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

ultralite ×160
xml ×28

question asked: 10 Jul '22, 19:31

question was seen: 255 times

last updated: 11 Jul '22, 03:21

SAP SQL Anywhere

FOR XML Syntac error UDB 16

Follow this question

Related questions