How Stuff and For Xml Path work in SQL Server

Question

Table is      Id Name     1 aaa   1 bbb   1 ccc   1 ddd   1 eee     Required output      Id abc     1 aaa bbb ccc ddd eee     Query  SELECT ID       abc   STUFF                    SELECT       name FROM temp1 FOR XML PATH        1  1                       FROM temp1 GROUP BY id  This query is working properly  But I just need the explanation how it works or is there any other or short way to do this  I am getting very confused to understand this

User · Answer

This article covers various ways of concatenating strings in SQL, including an improved version of your code which doesn't XML-encode the concatenated values.

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        -- You only want to combine rows for a single ID here:
        WHERE T2.ID = T1.ID
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id

To understand what's happening, start with the inner query:

SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE

Because you're specifying FOR XML, you'll get a single row containing an XML fragment representing all of the rows.

Because you haven't specified a column alias for the first column, each row would be wrapped in an XML element with the name specified in brackets after the FOR XML PATH. For example, if you had FOR XML PATH ('X'), you'd get an XML document that looked like:

<X>,aaa</X>
<X>,bbb</X>
...

But, since you haven't specified an element name, you just get a list of values:

,aaa,bbb,...

The .value('.', 'varchar(max)') simply retrieves the value from the resulting XML fragment, without XML-encoding any "special" characters. You now have a string that looks like:

',aaa,bbb,...'

The STUFF function then removes the leading comma, giving you a final result that looks like:

'aaa,bbb,...'

It looks quite confusing at first glance, but it does tend to perform quite well compared to some of the other options.

User · Answer

SELECT ID       abc   STUFF                    SELECT       name FROM temp1 FOR XML PATH        1  1                       FROM temp1 GROUP BY id   Here in the above query STUFF function is used to just remove the first comma     from the generated xml string   aaa bbb ccc ddd eee  then it will become  aaa bbb ccc ddd eee    And FOR XML PATH     simply converts column data into   aaa bbb ccc ddd eee  string but in PATH we are passing    so it will not create a XML tag   And at the end we have grouped records using ID column

User · Answer

Declare  Temp As Table  Id Int Name Varchar 100   Insert Into  Temp values 1  A    1  B    1  C    2  D    2  E    3  F    3  G    3  H    4  I    5  J    5  K   Select X ID  stuff  Select      Z Name from  Temp Z Where X Id  Z Id For XML Path      1 1     from  Temp X Group by X ID

User · Answer

I did debugging and finally returned my  stuffed  query to it it s normal way   Simply   select   from myTable for xml path  myTable     gives me contents of the table to write to a log table from a trigger I debug

User · Answer

In for xml path  if we define any value like   for xml path  ENVLOPE     then these tags will be added with each row    lt ENVLOPE gt   lt  ENVLOPE gt

User · Answer

STUFF  SELECT distinct       CAST T ID  FROM Table T where T ID  1 FOR XML PATH      1 1      AS Name

User · Answer

PATH mode is used in generating XML from a SELECT query  1  SELECT           ID           Name   FROM temp1 FOR XML PATH     Ouput   lt row gt   lt ID gt 1 lt  ID gt   lt Name gt aaa lt  Name gt   lt  row gt    lt row gt   lt ID gt 1 lt  ID gt   lt Name gt bbb lt  Name gt   lt  row gt    lt row gt   lt ID gt 1 lt  ID gt   lt Name gt ccc lt  Name gt   lt  row gt    lt row gt   lt ID gt 1 lt  ID gt   lt Name gt ddd lt  Name gt   lt  row gt    lt row gt   lt ID gt 1 lt  ID gt   lt Name gt eee lt  Name gt   lt  row gt    The Output is element-centric XML where each column value in the resulting rowset is wrapped in an row element  Because the SELECT clause does not specify any aliases for the column names  the child element names generated are the same as the corresponding column names in the SELECT clause    For each row in the rowset a  tag is added   2  SELECT           ID           Name   FROM temp1 FOR XML PATH       Ouput   lt ID gt 1 lt  ID gt   lt Name gt aaa lt  Name gt   lt ID gt 1 lt  ID gt   lt Name gt bbb lt  Name gt   lt ID gt 1 lt  ID gt   lt Name gt ccc lt  Name gt   lt ID gt 1 lt  ID gt   lt Name gt ddd lt  Name gt   lt ID gt 1 lt  ID gt   lt Name gt eee lt  Name gt    For Step 2  If you specify a zero-length string  the wrapping element is not produced   3        SELECT                Name       FROM temp1     FOR XML PATH           Ouput       lt Name gt aaa lt  Name gt       lt Name gt bbb lt  Name gt       lt Name gt ccc lt  Name gt       lt Name gt ddd lt  Name gt       lt Name gt eee lt  Name gt   4  SELECT                 Name   FROM temp1 FOR XML PATH      Ouput   aaa bbb ccc ddd eee   In Step 4 we are concatenating the values   5  SELECT ID      abc    SELECT                     Name       FROM temp1     FOR XML PATH       FROM temp1  Ouput  1    aaa bbb ccc ddd eee 1    aaa bbb ccc ddd eee 1    aaa bbb ccc ddd eee 1    aaa bbb ccc ddd eee 1    aaa bbb ccc ddd eee   6  SELECT ID      abc    SELECT                     Name       FROM temp1     FOR XML PATH       FROM temp1 GROUP by iD  Ouput  ID  abc 1    aaa bbb ccc ddd eee   In Step 6 we are grouping the date by ID   STUFF  source string  start  length  add string   Parameters or Arguments source string The source string to modify  start The position in the source string to delete length characters and then insert add string  length The number of characters to delete from source string  add string The sequence of characters to insert into the source string at the start position   SELECT ID      abc        STUFF            SELECT                         Name           FROM temp1         FOR XML PATH       1  1           FROM temp1 GROUP by iD  Output  -----------------------------------   Id          Name                   ---------------------------------    1           aaa bbb ccc ddd eee   -----------------------------------

User · Answer

There is very new functionality in Azure SQL Database and SQL Server  starting with 2017  to handle this exact scenario  I believe this would serve as a native official method for what you are trying to accomplish with the XML STUFF method  Example   select id  STRING AGG name       as abc from temp1 group by id   STRING AGG - https   msdn microsoft com en-us library mt790580 aspx  EDIT  When I originally posted this I made mention of SQL Server 2016 as I thought I saw that on a potential feature that was to be included  Either I remembered that incorrectly or something changed  thanks for the suggested edit fixing the version  Also  pretty impressed and wasn t fully aware of the multi-step review process that just pulled me in for a final option

User · Answer

Here is how it works  1  Get XML element string with FOR XML Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements  with the element name contained in the PATH argument  For example  if we were to run the following statement  SELECT       name                FROM temp1               FOR XML PATH       By passing in a blank string  FOR XML PATH       we get the following instead   aaa bbb ccc ddd eee  2   Remove leading comma with STUFF The STUFF statement literally  quot stuffs    one string into another  replacing characters within the first string  We  however  are using it simply to remove the first character of the resultant list of values  SELECT abc   STUFF               SELECT       NAME             FROM temp1             FOR XML PATH                    1  1      FROM temp1  The parameters of STUFF are   The string to be    stuffed     in our case the full list of name with a leading comma  The location to start deleting and inserting characters  1  we   re stuffing into a blank string  The number of characters to delete  1  being the leading comma   So we end up with  aaa bbb ccc ddd eee  3  Join on id to get full list Next we just join this on the list of id in the temp table  to get a list of IDs with name  SELECT ID   abc   STUFF                SELECT       name                FROM temp1 t1               WHERE t1 id   t2 id               FOR XML PATH                      1  1      from temp1 t2 group by id   And we have our result      Id Name     1 aaa bbb ccc ddd eee     Hope this helps

[sql] How Stuff and 'For Xml Path' work in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to for-xml-path

Examples related to stuff