USP_BUSINESSPROCESSDURATIONKPI_FINDKPIINSTANCES

CREATE procedure dbo.USP_BUSINESSPROCESSDURATIONKPI_FINDKPIINSTANCES 
  @BUSINESSPROCESSCATALOGID nvarchar(36),
  @PARAMETERSETID nvarchar(36)
with 
  execute as owner 
as    
  set nocount on;

  ;with xmlnamespaces(DEFAULT 'bb_appfx_dataforms')
  select 
    ID 
  from 
    dbo.KPIINSTANCE 
    cross apply PARAMETERSXML.nodes('/DataFormItem/Values/fv[@ID=PARAMETERSETID]') T(c)
  where 
    KPICATALOGID = 'CF8C8FC4-ADCD-4B6C-A7EC-32D818F05CB9' -- Only check Business Process Duration KPIs to avoid XML shredding of KPIs we don't care about

    and
    c.value('Value[1]', 'uniqueidentifier') = @PARAMETERSETID
  group by ID