What is "STUFF AND FOR XML PATH" used for?

Question

There are bulk of sites providing information on STUFF AND FOR XML PATH but I want to know the actual use and where it can be implemented.

Please guide

SELECT DISTINCT 
    sno,
    STUFF ((SELECT ','+Scity
            FROM @abcdef T1
            WHERE T1.sno = T2.sno
            FOR XML PATH('')), 1, 1, '') 
FROM
    @abcdef T2

Now in the example above please tell me what are those quotes and 1,1 used for.

oops! sorry I forgot to mention I am using it in sql server. I will edit my question. SO , it will get clear to you — Shrikant Jadhav, Dec 04 '19 at 04:30
Did you look at the documentation at all? https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver15 — Sami Kuhmonen, Dec 04 '19 at 05:09
In this case STUFF just deletes first comma. Replace first symbol (comma) with empty string. — Max Zolotenko, Dec 04 '19 at 07:05
Here's a reasonable explanation: https://stackoverflow.com/a/35172160/243373 — TT., Dec 04 '19 at 07:19

score 0 · Answer 1 · answered Dec 04 '19 at 21:08

0

In short, this is an idiomatic way of producing a CSV in T-SQL. If you're on a version of SQL Server that supports STRING_AGG() (SQL 2017+ iirc), then use that.

answered Dec 04 '19 at 21:08

Ben Thul

31,080
4
45
68

What is "STUFF AND FOR XML PATH" used for?

1 Answers1