Change column name generated by FOR XML PATH in SQL Server

Question

I am using the FOR XML PATH function in order to concatenate multiple columns into a single row. However, I can't figure out how to alias the column name of the resulting table.

Here is the SQL query:

SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('')

I have tried the following two methods which generate an error message:

SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('') [Comment_Agg];

SELECT * AS Comment_Agg
FROM
    (SELECT Comment
     FROM Comments
     WHERE ID = 1006
     FOR XML PATH(''));

FYI, I am using SSMS 18.

F*Y*I, SSMS is irrelevant and is just a client using SQL Server like any other application. — Stu, Aug 22 '21 at 09:08
Why not use `STRING_AGG` if you want to aggregate your strings? — Thom A, Aug 22 '21 at 09:14
@user9532692 SSMS is just an IDE. SSMS 18 supports up to SQL Server 2019, which does include `STRING_AGG` (it was added in SQL Server 2017). I can assure you you can use `STRING_AGG` when using SSMS 18 when connected to an instance that supports it. — Thom A, Aug 22 '21 at 09:25
@user9532692 in SSMS (or any application that connects to SQL Server - even a command prompt using osql) `select @@version` - if it's 14.x or 15.x you can use `string_agg` — Stu, Aug 22 '21 at 09:30
@Larnu Thanks for your suggestion! Where can I check my current instance and how can I connect to an instance that supports the STRING_AGG function? — user9532692, Aug 22 '21 at 09:30
Does this answer your question? [string_agg for sql server pre 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017) — Thom A, Aug 22 '21 at 09:32
@Larnu My version is 13.x so I can't use STRING_AGG function unless I connect to an instance that supports it. The problem is that I don't know how to connect to a new instance... So I thought I might as well just stick with the XML PATH to do aggregation. Now, I face an [issue with adding GROUP BY clause](https://stackoverflow.com/questions/68880021/using-group-by-with-xlm-path-in-sql-server-version-13-x) to it — user9532692, Aug 22 '21 at 09:42

user9532692 · Answer 1 · 2021-08-22T09:43:14.450

0

I googled more and found the resolution as follows:

SELECT SUBSTRING(
(
SELECT Comment
FROM Comments
WHERE ID = 1006
FOR XML PATH('')
),1,999999) AS Comment_Agg

edited Aug 22 '21 at 09:43

answered Aug 22 '21 at 09:04

user9532692

584
7
28

Why do you not want the first character? Also, note that you'll lose any characters after the first 10,000 as a result here. – Thom A Aug 22 '21 at 09:13
If you want to remove the first character, `stuff()` would be a better choice. – Stu Aug 22 '21 at 09:17
@Stu it was a mistake to exclude the first character. Do you happen to know how to add group by ID on the query above? I created a new post [here](https://stackoverflow.com/questions/68880021/using-group-by-with-xlm-path-in-sql-server-version-13-x) – user9532692 Aug 22 '21 at 09:44

score 0 · Answer 2 · answered Aug 22 '21 at 13:28

To use FOR XML PATH to concatenate, the column needs to be unnamed. If you don't want a separator, you can just add + ''

SELECT
 (SELECT Comment + ''   -- causes the column to be unnamed
  FROM Comments
  WHERE ID = 1006
  FOR XML PATH(''), TYPE
 ).value('text()[1]','nvarchar(max)')  -- prevents XML escaping

Александр Фокин · Answer 3 · 2023-03-15T16:53:44.890

0

You can use CTE:

with Comment_Agg (your_field_name) as (
  select 
      Comment 
  from
      Comments
  where
      ID = 1006
for xml path(''))
select * from Comment_Agg

edited Mar 15 '23 at 16:53

answered Mar 15 '23 at 16:47

Александр Фокин

1
2

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 21 '23 at 00:21

Change column name generated by FOR XML PATH in SQL Server

3 Answers3