Having multiple rows in one row using for xml path

Question

I have the following query but i get this error: Syntax error, unexpected XML_SYM, expecting UPDATE_SYM

select ','+ description from users 
join access_rights 
on users.id_users=access_rights.users_id_users
join doors 
on doors.id_doors=access_rights.doors_id_doors 
for xml path('');

Can anyone help me

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

MySQL doesn't support XML queries like this - see this question for alternatives. It seems they do consider it as something they might implement in the future.

Right now, MySQL is complaining about the word XML which it doesn't know - the word FOR is only used in the combination FOR UPDATE.

edited May 23 '17 at 12:30

Community

1
1

answered Aug 08 '15 at 11:01

Glorfindel

21,988
13
81
109

Thank you for your question but how can I do this, to have multiple rows in one row result. I search on the internet and I found this FOR XML and STUFF function. – Iris Aug 08 '15 at 11:07
Do you have a working example for a query like this without a JOIN? – Glorfindel Aug 08 '15 at 11:11
I have a query working example with join because this query result is what I want, but to multiple rows in only one row. This is the query that works – Iris Aug 08 '15 at 11:48
select name,description from users join access_rights on users.id_users=access_rights.users_id_users join doors on doors.id_doors=access_rights.doors_id_doors order by name; – Iris Aug 08 '15 at 11:48
But that's not an `FOR XML` query, is it? – Glorfindel Aug 08 '15 at 11:49
No it isn't because how you said mysql doesn't support xml. That my problem, what to do instead of this, because the query that I've posted in this question works in SQL-Server – Iris Aug 08 '15 at 11:55
http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with – Iris Aug 08 '15 at 11:56
Then, please have a look at [this question](http://stackoverflow.com/questions/2847674/xml-output-from-mysql) which I already mentioned in my answer. – Glorfindel Aug 08 '15 at 11:56
this is the site where I got the information, it's exactly what I want to do, but in MYSQL – Iris Aug 08 '15 at 11:57
Please keep in mind that while MySQL and SQL Server both understand SQL, they both have different 'dialects'. `FOR XML` is simply not in MySQL's dialect. – Glorfindel Aug 08 '15 at 11:57

score 0 · Accepted Answer · edited May 23 '17 at 10:26

FOR XML is supported in SQL Server, but I'm not aware if the same also supported in MySQL. Looks like it is not, according to what happened when you run the query containing FOR XML in MySQL.

Search for solution that is specifically proven to be working with the same DBMS. In SQL Server, your query will return descriptions as comma-separated values. To return comma-separated values in MySQL, use GROUP_CONCAT() instead of FOR XML :

select GROUP_CONCAT(description) from users 
.....

Thank you very much, it was really helpful – Iris Aug 08 '15 at 12:20 — Iris, Aug 08 '15 at 12:20

Having multiple rows in one row using for xml path

2 Answers2