USP_SIMPLEDATALIST_MKTQUERYVIEWFIELDS

CREATE procedure dbo.USP_SIMPLEDATALIST_MKTQUERYVIEWFIELDS
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @SHOWHIDDEN bit = 0
)
as
  set nocount on;

  declare @OUTPUTDEFINITIONXML xml;

  if @SHOWHIDDEN is null
    set @SHOWHIDDEN = 0;

  select
    @OUTPUTDEFINITIONXML = [Q].[OUTPUTDEFINITIONXML]
  from
    dbo.[QUERYVIEWCATALOG] [Q]
  where
    [Q].[ID] = @QUERYVIEWCATALOGID;

  select
    T.c.value('(@Name)[1]','nvarchar(255)') as [VALUE],
    isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) as [LABEL]
  from
    @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c)
  where
    (@SHOWHIDDEN = 1 or isnull(T.c.value('(@IsHidden)[1]', 'nvarchar(5)'),'0') in ('false', '0'))
  order by
    [LABEL];

  return 0;