Thanks Guys....

I got it to work using the following Recursive query :

;With RCTE

As

(

Select SModuleId,ModuleId,SMName,

Convert(char(11),SDate,106)as SDate, Convert(char(11), SDate, 103) as ddmmyyyySDate,

Convert(char(11),EDate,106) as EDate, Convert(char(11), EDate, 103) as ddmmyyyyEDate,

Status,ToolTip, ParentId, 0 As Level, SModuleId As Root, CAST(SModuleId AS varchar(MAX)) AS TreeOrder

from ES_SubModuleMaster1 Where SModuleId=Coalesce(NULL,SModuleId) AND ParentId = 0

Union ALL

Select b.SModuleId,b.ModuleId,b.SMName,

Convert(char(11),b.SDate,106)as SDate, Convert(char(11), b.SDate, 103) as ddmmyyyySDate,

Convert(char(11),b.EDate,106) as EDate, Convert(char(11), b.EDate, 103) as ddmmyyyyEDate,

b.Status, b.ToolTip, b.ParentId, a.Level + 1, a.Root As Root, a.TreeOrder+'/'+CAST(b.SmoduleId AS varchar(20)) AS TreeOrder

From RCTE As a JOIN ES_SubModuleMaster1 As b ON a.SModuleId = b.ParentId

)

Select * From RCTE Order By TreeOrder

Got the solution from here after some research...thought it might be useful to someone. 🙂