For Xml Path and String Concatenation

Concatenate a String using FOR XML PATH

You're not qualifying your column names and it's giving preference to the inner query columns.

select 
    [Dimension],
    [Attribute],
    [Value] = STUFF((
        SELECT N'', ' ,' + [AttributeValue] 
        FROM Testy x
        WHERE t.[Dimension] = x.[Dimension] 
        and t.[Attribute] = x.[Attribute]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from Testy t
group by [Dimension], [Attribute]

How FOR XML PATH('') works when concatenating rows

What FOR XML PATH('xxx') does is create an XML string for the resultset that puts each row in a <xxx></xxx> element and each column value inside the row, in an element with the name for that column.

If the PATH is empty (i.e. PATH('')) it omits the row element in the XML generation. If the column has no name it omits the column element in the XML generation. When both PATH is empty and columns have no names it effectively becomes a string concatenation of all rows.

Run the following statements to get a better insight in the process:

-- Each row is in a <beta></beta> element
-- Each column in that row in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('beta');

-- Since the PATH is empty, the rows are not put inside an element
-- Each column in that row is in a <alfa></alfa> element (the column name)
SELECT
    alfa=','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- Since the PATH is empty, the rows are not put inside an element
-- Since the column has no name it is not put inside an element     
SELECT
    ','+TABLE_SCHEMA + '.' + TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

-- This uses the STUFF function to remove the leading comma to get a proper comma-seperated list    
SELECT STUFF((
    SELECT
        ','+TABLE_SCHEMA + '.' + TABLE_NAME
    FROM
        INFORMATION_SCHEMA.TABLES
    FOR
        XML PATH('')
    ),1,1,''
) AS comma_seperated_list;

---

Now I hear you asking: How can I remove the column name when I simply select a column from a table. There are several ways, in order of my preference:

• XQuery properties: SELECT [text()]=column_name ...
• Use a subquery to select the column value: SELECT (SELECT column_name) ...
• CAST the column to its type: SELECT CAST(column_value AS <TYPE of the column>) ...

Examples:

SELECT
    [text()]=TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    (SELECT TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SELECT
    CAST(TABLE_NAME AS SYSNAME)
FROM
    INFORMATION_SCHEMA.TABLES
FOR
    XML PATH('');

SQL Select FOR XML PATH - Combine ordering and concatenate string with increasing counter

You just need to order the rows:

DECLARE @t TABLE (Code VARCHAR(255), Filename VARCHAR(255), ImageOrder INT)

INSERT INTO @t VALUES
('xxxx', 'Noimage.jpg', 0),
('xxxx', 'yyyy.jpg', 2),
('xxxx', 'zzzz.jpg', 1),
('xxxx', 'aaaa.jpg', 3)
 
SELECT REPLACE(
  (SELECT CAST('&p' + CAST(ImageOrder AS varchar) + '=' + Filename AS VARCHAR(MAX))
  FROM @t
  WHERE ImageOrder <> 0
  ORDER BY ImageOrder
  FOR XML PATH (''))
  , 'amp;', '') AS Txt

Which will give you the result:

&p1=zzzz.jpg&p2=yyyy.jpg&p3=aaaa.jpg

sql server Concatenate Multiple Rows Using FOR XML PATH

Try below

SELECT a.[user],a.userid,a.email
STUFF((SELECT  ', ' +  [group] [text()]
      FROM [temptable] b on a.[user]=b.[user]
      for XML PATH (''),TYPE).
      value('.','NVARCHAR(MAX)'),1,2,'') AS [group]
FROM [temptable] as a
GROUP BY a.[user],a.userid,a.email

SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH

You need a correlated subquery:

SELECT hs.department AS department,
       STUFF( (SELECT ', ' + top_employee
               FROM findhighest_secondstep hs2
               WHERE hs2.department = hs.department
               FOR XML PATH('')
              ), 1, 2, ''
            ) as top_employees                   
FROM findhighest_secondstep hs
GROUP BY hs.department

Concatenation of strings by for xml path

Your subquery cannot return two values. If you just want to concatenate strings, you do not need the xml data type at all. You can do the stuff() and subquery in a single statement:

declare @Rep1Names nvarchar(max) = (
    stuff((select ', [' + report_name + ']' as name
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
         ), 1, 1, '');

declare @Rep2Names nvarchar(max) = (
    stuff(select ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
   ), 1, 1, '');

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

SQL for concatenating strings/rows into one string/row? (How to use FOR XML PATH with INSERT?)

Rather than xml path why not do it like this?

DECLARE @Cols VARCHAR(8000) 
SELECT @Cols = COALESCE(@Cols + ', ', '') + 
    ISNULL(col, 'N/A')
FROM t1
Insert into t2 values(@Cols);

---

Related Topics