UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT

CREATE function dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](
  @EFFORTNAMESCHEMETEXT xml,
  @COUNTERVALUE nvarchar(10) --this is "optional" as the function will use what is in @EFFORTNAMESCHEMETEXT if this is NULL

)
returns nvarchar(max)
with execute as caller
as begin

  --build the name

  return
    (select(select
      case [NAMESCHEMEPARTSTABLE].[NAMEPARTTYPECODE] 
        when 10 then [NAMESCHEMEPARTSTABLE].[FREEFORMPART] 
        when 8 then case when @COUNTERVALUE is null then [NAMESCHEMEPARTSTABLE].[TEXTPART] else @COUNTERVALUE end
        else [NAMESCHEMEPARTSTABLE].[TEXTPART]
      end
      from
        (
          select
            case when T.c.value('(@SEPARATORTYPECODE)[1]', 'tinyint') = 1 then ' ' else T.c.value('(@TEXTPART)[1]', 'nvarchar(100)') end as [TEXTPART],
            T.c.value('(@FREEFORMPART)[1]', 'nvarchar(100)') as [FREEFORMPART],
            T.c.value('(@NAMEPARTTYPECODE)[1]', 'tinyint') as [NAMEPARTTYPECODE],
            T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE]
          from 
            @EFFORTNAMESCHEMETEXT.nodes('/EFFORTNAMESCHEMETEXT/ITEM') T(c)
      ) as [NAMESCHEMEPARTSTABLE]
      order by
        [NAMESCHEMEPARTSTABLE].[SEQUENCE]
      for xml path(''),TYPE).value('text()[1]','nvarchar(max)'));

end