Cognos嵌套组层级提取问题：现有查询无法处理组包含组场景

我需要从Cognos内容存储（content store）中提取组及其父级信息。找到的查询语句在组包含其他组（嵌套组）时无法正常工作，试过MotioPi也得不到需要的信息。

我的查询语句如下：

with RecursiveCTE
as(
SELECT 
          cmobjnames.cmid as object_id,
          cmobjnames.name objnames,
          CAST(cmobjnames.name AS VARCHAR(MAX)) AS TreePath,
          cmobjects.PCMID as parent_id          
   FROM   cmobjnames           
          INNER JOIN cmobjects
                     ON cmobjnames.cmid = cmobjects.cmid
        where cmobjnames.cmid=104 -- Cognos目录父成员ID
        and cmobjnames.ISDEFAULT=1
 UNION ALL
 SELECT 
          cmobjnames.cmid as object_id,
           cmobjnames.name objnames,
           CAST(H.TreePath + '\'
         + CAST(cmobjnames.name AS VARCHAR(100)) AS VARCHAR(MAX)) as TreePath,
           cmobjects.PCMID as parent_id          
   FROM   cmobjnames           
          INNER JOIN cmobjects
                     ON cmobjnames.cmid = cmobjects.cmid
          INNER JOIN RecursiveCTE H
          ON H.object_id =cmobjects.PCMID
        where  cmobjects.classid IN ( 54,8,26) -- 筛选文件夹、组、角色
        and cmobjnames.ISDEFAULT=1
)       
 SELECT objnames as Name
 , object_id
 , parent_id 
 , TreePath
FROM RecursiveCTE
INNER JOIN cmobjects
 ON object_id = cmobjects.cmid
 where cmobjects.classid IN ( 54,26) -- 筛选组、角色

内容的提问来源于stack exchange，提问作者Samuele