Using STUFF to turn multiple rows into comma delimited values for hierarchical data

Documents are stored in tree form in Kentico, we always deal with hierarchical data. For example, a list of post with tags. A nested repeater is always a good way to display parent/children data. However, it requires n+1 requests to the database which is not ideal in terms of performance.

If we just want a simple list from the children documents, STUFF function can be used to construct a sql query and all required data can be retrieved in one request as below:

SELECT     
View_Custom_Parent_Joined.DocumentName, 
STUFF((SELECT ', ' + View_Custom_Children_Joined.[DocumentName]
 FROM View_Custom_Children_Joined
 WHERE
    View_Custom_Children_Joined.NodeParentId =     View_Custom_Parent_Joined.NodeID
 ORDER BY      View_Custom_Children_Joined.NodeOrder FOR XML  PATH(''), TYPE).value('.', 'varchar(max)'),  1, 2, '') AS ChildrenList
FROM
View_Custom_Parent_Joined

Furthermore, we can store the query in a stored procedure or a view to fine tune the performance.

This approach is only for getting simple data from the child documents. Like the example above, it only gets the children document name.We can tweak the STUFF function a bit further. By adding NodeAliasPath and Html tags, we can have a link. However, if you go further, the code becomes hard to maintain.