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.

SQL Script to rename a document type table

My team came across with a situation and required to change a document type table in database. Apparently, You can change document type namespace, But it would not rename the table. Finally, my team came up with below script.

DECLARE @OldTableName VARCHAR (MAX) 

DECLARE @NewTableName VARCHAR (MAX)DECLARE @ClassID INT 

SET @OldTableName = 'OLD_TableName'SET @NewTableName = 'New_TableName'

SELECT @ClassID= ClassID   FROM dbo .CMS_Class WHERE ClassTableName = @OldTableName

UPDATE dbo. CMS_Class SET 

ClassTableName = REPLACE(ClassTableName , 'Old_' ,'New_' ), 

ClassDisplayName = REPLACE(ClassDisplayName , 'Old ' ,'New ' ), 

ClassXmlSchema = REPLACE(ClassXmlSchema , 'Old_' ,'New_' ), 

ClassName = REPLACE(ClassName , 'Old.' ,'New.' ) 

WHERE ClassID = @ClassID 

EXEC sp_rename @OldTablename, @NewTableName

It is NOT done yet. You need to go back to that document type and save it. This would trigger Kentico to regenerate the view for the document type with the new namespace. You may also remove the old view.

We ran this script on Kentico v7 instance and did not test on other versions. Please make sure you back up database before running the script.

-----------

Updated: I've tried the script on Kentico v8. It works fine.