;WITH CTE AS (SELECT [RGTCID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Collection]  cross apply RGTCData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs AS(select y.t_str,count(distinct x.RGTCID) CollectionCount from cte x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE2 AS (SELECT [RGTBID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Brand]  cross apply RGTBData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs2 AS(select y.t_str,count(distinct x.RGTBID) CollectionCount from cte2 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE3 AS (SELECT [RGTGID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Guru]  cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs3 AS(select y.t_str,count(distinct x.RGTGID) CollectionCount from cte3 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE4 AS (SELECT [RGTSID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Story]  cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs4 AS(select y.t_str,count(distinct x.RGTSID) CollectionCount from cte4 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str)select c.CollectionID,isnull(m.CollectionCountSum,0) as CollectionCountSum FROM [R_GT_Collection] cleft join (select t_str,sum(CollectionCount) as CollectionCountSum from (select t_str,CollectionCount from CollectionIDsunion allselect t_str,CollectionCount from CollectionIDs2union allselect t_str,CollectionCount from CollectionIDs3union allselect t_str,CollectionCount from CollectionIDs4) z group by t_str) mon c.CollectionID=m.t_strorder by 1