Recursive xml-query approach (just one way of doing it... note that
you can use LINQ-to-SQL to make a udf directly callable):

set nocount on
-- cleanup (ignore first time)
drop table tree
drop function fnSubTree
drop function fnTree

-- schema (TODO: add PK/FK)
create table tree (id int not null identity(1,1), parent int null,
[key] varchar(10) not null, [order] int)

-- demo data
declare @root int
insert tree values (null, 'A', 1)
set @root = scope_identity()
insert tree values (@root, 'B', 1)
insert tree values (@root, 'E', 3)
insert tree values (@root, 'C', 2)
insert tree values (scope_identity(), 'D', 1)
go
-- declare root func
create function dbo.fnTree (@key varchar(10)) returns xml
as
begin
    return (
    select  [key] as [@key], dbo.fnSubTree(id)
    from    tree
    where    [key] = @key
    for xml path('node'), type)
end
go
-- declare recursive func
create function dbo.fnSubTree (@parent int) returns xml
as
begin
    return (
    select  [key] as [@key], dbo.fnSubTree(id)
    from    tree
    where    parent = @parent
   order by [order]
    for xml path('node'), type)
end
go
-- examples
select dbo.fnTree('A')
select dbo.fnTree('C')