DECLARE @xmlPredicate Table (Name varchar(50), Predicate xml)

INSERT INTO @xmlPredicate
SELECT NamePart, wite.Predicate
FROM [Tfs_DefaultCollection].[dbo].[Constants] c
JOIN [Tfs_DefaultCollection].[dbo].[tbl_Board] b
ON b.TeamId = c.TeamFoundationId
JOIN [Tfs_DefaultCollection].[dbo].[tbl_WorkItemTypeExtensions] wite
ON wite.Id = b.ExtensionId
where NamePart IN (@pBoards)
AND DomainPart = ‘<GUID>’
AND b.CategoryReferenceName = ‘Microsoft.RequirementCategory’

SELECT
Name,
cnp1.id
,cnp1.[AreaPath]
,m.c.value(‘.’,’varchar(50)’) as RollupId
,cnp.AreaPath ‘RollupPath’
,cnp.NodeName ‘RollupNode’

FROM @xmlPredicate s
outer apply s.Predicate.nodes(‘//under[@field=”System.AreaId”]’) as m(c)
JOIN [Tfs_DefaultCollection].[dbo].[tbl_ClassificationNodePath] cnp
ON cnp.Id = m.c.value(‘.’,’varchar(50)’)
JOIN [Tfs_DefaultCollection].[dbo].[tbl_ClassificationNodePath] cnp1
ON cnp1.AreaPath like cnp.AreaPath + ‘%’
AND cnp1.AreaLevel1 = ‘Area’

Advertisements