How FOR XML Works in SQL Server

Question

Can somebody explain how is FOR XML used in SQL Server? Is this the only way to concatenate strings by rows without using an user-defined function? I got it from this post, did what I need to do, but I don't understand it.

The person who posted this did not explain anything. After an exhaustive search I couldn't find anything understandable. And no, MSDN was not helpful given my limited SQL skills.

Here's the my SQL, with only the names changed from the one in that post.

SELECT 
  ID,
  STUFF((
    SELECT ' -' + Code
    FROM #Z 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,''
 ) AS ConcatCode
FROM #Z Results
GROUP BY ID

MSDN's [documentation](https://msdn.microsoft.com/en-us/library/ms178107.aspx) seems to be pretty clear. It's even more clear how to use `PATH` mode [here](https://msdn.microsoft.com/en-us/library/ms189885.aspx). — Ken White, Jan 21 '15 at 20:49
That particular use of `FOR XML XPATH` is one of the [hacks](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) to do GROUP_CONCAT or ListAgg in Sql Server. — StuartLC, Jan 21 '15 at 20:49
Well I don't know about attributes, elements, or XPath expressions. That sounds more like HTML lingo. — Concerned_Citizen, Jan 21 '15 at 20:51
`XML` is quite similar to `HTML`; they both use attributes, elements, and can have XPath queries run against them. What **specific question** would you like us to answer for you here? We don't do tutorials. — Ken White, Jan 21 '15 at 20:53
it could be the [TYPE directive](https://msdn.microsoft.com/en-nz/library/ms190025.aspx) that is confusing things for you. Basically, it is specifying that an xml data type should be returned. Then [.Value](https://msdn.microsoft.com/en-nz/library/ms178030.aspx) is a method that acts on that datatype — G B, Jan 21 '15 at 21:12

score 1 · Accepted Answer · answered Jan 22 '15 at 00:35

Is this the only way to concatenate strings by rows without using an user-defined function?

This is the simplest method to concatenate rows

how FOR XML is used in SQL Server

I suppose examples below will give more or less clear understanding how use it

------------------------------------------------------------
--Create temp table for testing
IF OBJECT_ID('Tempdb..#Z') IS NOT NULL 
    DROP TABLE #Z
CREATE TABLE #Z
    (
      ID INT ,
      SomeText VARCHAR(3)
    )
INSERT  INTO #Z
        ( ID, SomeText )
VALUES  ( 1, 'AAA' ),
        ( 2, 'BBB' ),
        ( 3, 'CCC' ),
        ( 1, 'ZZZ' ),
        ( 1, 'XXX' ),
        ( 2, 'YYY' )
------------------------------------------------------------
--1. Concatenate
SELECT  SUBSTRING(( SELECT  ',' + SomeText
                    FROM    #Z
                  FOR
                    XML PATH('')
                  ), 2, 1000) AS Concatenated
------------------------------------------------------------
--2. Concatenate for each ID
SELECT DISTINCT
        Z_out.id ,
        SUBSTRING(( SELECT  ',' + SomeText
                    FROM    #Z AS Z_in
                    WHERE   Z_in.ID = Z_out.id
                  FOR
                    XML PATH('')
                  ), 2, 1000) AS Concatenated
FROM    #Z AS Z_out

Thank you for your help. Some questions, 1) Why use substring? 2) Why take in ' ' as parameter for XML PATH function? — Concerned_Citizen, Jan 22 '15 at 17:41
1) substring used to remove first comma 2) If you provide an empty string (FOR XML PATH ('')), no wrapper element is generated. For more information use this link https://msdn.microsoft.com/en-us/library/ms190922.aspx — Vasily, Jan 23 '15 at 00:36

How FOR XML Works in SQL Server

1 Answers1