FOR XML PATH

 

I would like to share the approach of ‘FOR XML PATH’.

We can use ‘FOR XML PATH’ to concatenate rows, if you have the database 'AdventureWorks' in SQLServer, you can run the following statement, for example:

 

1. Concatenate rows

SELECT CAT.Name AS[Category],

       STUFF((SELECT',' + SUB.Name AS [text()]

              FROM  Production.ProductSubcategory SUB

              WHERE SUB.ProductCategoryID = CAT.ProductCategoryID

              FORXML PATH('')), 1, 1, '') -- This is done to remove the first character (,) fromthe result

               AS [Sub Categories]

FROM  Production.ProductCategory CAT

 

2. Get the xml data from a table

SELECT ProductCategoryKey         AS '@CategoryKey',

       EnglishProductCategoryName AS '@CategoryName'

FROM  AdventureWorksDW2012.dbo.DimProductCategory

FOR XML PATH('Category'), ROOT('Categorys')