Concatenate Rows using FOR XML PATH ()

Some time we are getting requirement to concatenate the row values. For example suppose I have product and sub product and want to report like Category and list of sub category in a single row. We can get the desirer output by using STUFF function and FOR XML PATH ().

The STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.

The syntax for the STUFF function in SQL Server (Transact-SQL) is:

Syntax:

STUFF (Source_String, Start, Length, add_string)

FOR XML Clause

The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. 

In a FOR XML clause, you specify one of these modes:

Ø  RAW

Ø  AUTO

Ø  EXPLICIT

Ø  PATH

RAW:

 The RAW mode generates a single XML element for each row in the result set returned by the query.

See the example

For example I am using AdventureWorks2008R2 database

Select name  FROM Production.ProductSubcategory For XML RAW

Result

AUTO:

The AUTO mode in a FOR XML clause is slightly different from the RAW mode in the way that it generates the XML result set. The AUTO mode generates the XML by using heuristics based on how the SELECT statement is defined. 

select Name  FROM Production.ProductSubcategory FOR XML AUTO

PATH:

When we specify the PATH mode in the FOR XML clause, column names (or their aliases) are treated as XPath expressions that determine how the data values will be mapped to the XML result set. By default, XML elements are defined based on column names. 

Suppose I use column as text datatype then the tag will be remove

See it in example.

  

EXPLICIT:

The EXPLICIT mode provides very specific control over our XML, but this mode is much more complex to use than the other modes. To use this mode, we must build our SELECT statements in such as way as to define the XML hierarchy and structure. In addition, we must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.

Concatenate Rows using FOR XML PATH ()

Use below sql script

SELECT    PC.Name AS [Category],             STUFF((    SELECT ',' + PSB.Name AS [text()]                         FROM Production.ProductSubcategory PSB                         WHERE                         PSB.ProductCategoryID = PC.ProductCategoryID                         FOR XML PATH('')                         ), 1, 1, '' )                         AS [Sub Categories] FROM  Production.ProductCategory PC

Output