Select sub query in function

I have to create an inline function (stored procedure not allowed, as it cannot be accessed by end users, multistatement function not allowed because of performance issues). I am struck up in a piece of code.

The code goes like this.

ALTER FUNCTION [CDC].[FN_CBR_COPERNICUS_DBO_AGMASTER_ANY] (@START_LSN BINARY(10), @END_LSN BINARY(10),@APPLNID SMALLINT)
RETURNS TABLE
AS
RETURN(
SELECT
  (
  SELECT  
       STUFF((SELECT ', ' + CBR.CBRCA_COLUMNNANME
         FROM CBRCOLAPPLN CBR 
       WHERE CBR.CBRCA_APPLICATIONID = 1 AND CBR.CBRCA_TABLENAME = 'COPERNICUS_CORE_PARAMETER'
         FOR XML PATH(''), TYPE)
        .VALUE('.','NVARCHAR(MAX)'),1,2,' ') CHANGED_COLUMNS) ,__$OPERATION AS CBR_OPERATION  
              FROM CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER(0X0009631C000016F90029, 0X0009631D000008720001, 'ALL WITH MASK') AS CBRUPDATEDCOLUMNS)

The subquery select will return values , if I execute the subquery alone, it returns the below

CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN

When I place the values of subquery inside the function and place the code as

Select  CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN,__$operation as CBR_OPERATION  
              FROM CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER (0x0009631c000016f90029, 0x0009631d000008720001, 'all with mask') AS CBRUPDATEDCOLUMNS

Upon executing it , I get the desired results,from the function CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER

Whereas if I execute the first set of code (the one written in function), placed in the begining of the question, it just results me the value of the subquery * number of rows in the function . It just gives me the result as below, along with the some ,__$OPERATION value

CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN

Could you please help me where am I going wrong?

Regards.

via Chebli Mohamed