March 6, 2019 at 8:55 am
Hello,
In SQL Server 2008 R2, is it possible to return XML values comma separated from a user table-valued function?
FOR XML PATH('')
I am getting
Incorrect syntax near '.'.
Many thanks,
Vinay
March 6, 2019 at 2:45 pm
You generally want to return either XML values or comma-separated values, not both. It's not clear exactly what you are looking for, but the FOR XML clause can be used in ANY SELECT statement, including those that contain a table-valued function.
Generally when you have syntax error, you want to include the whole query. The parser only flags where it could not continue parsing the query, which is not always where the actual error appears. More importantly, there is no way to determine how to fix your query, because the error message has absolutely no bearing on your code snippet, seeing that the error says the problem is near '.' and your code snippet doesn't even contain a '.'.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 7, 2019 at 9:19 am
Hi Drew,
I wasn't very clear with my question, sorry about that.
What I am after is for the below standard stuff using a table, I now want to use a UDF (table-valued function), which pretty much has all the logic in there for the output. In SQL 2008 R2, I then get an incorrect syntax error.
Table is:
+----+------+
| Id | Name |
+----+------+
| 1 | aaa |
| 1 | bbb |
| 1 | ccc |
| 1 | ddd |
| 1 | eee |
+----+------+
Required output:
+----+---------------------+
| Id | csv_output |
+----+---------------------+
| 1 | aaa,bbb,ccc,ddd,eee |
+----+---------------------+
SQL code for above
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
Many thanks,
Vinay
March 7, 2019 at 10:35 am
You query still doesn't contain a period, and I am not getting an error message when I run your code. The problem is somewhere else.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply