Sample : SQL Server 2008 FOR XML - AUTO Mode

-- It is helpful to how to create the XML in auto mode
--AUTO

declare @Std table (std_id int, name varchar(10))
insert into @Std values (1,'Rajasekhar')
insert into @Std values (2,'Mihir')
insert into @Std values (3,'Hari')

declare @Sub table (sbj_id int,std_id int, sname varchar(10))
insert into @Sub values (1,3,'Reporting')
insert into @Sub values (2,3,'Scorecard')
insert into @Sub values (3,3,'Consolidation')
insert into @Sub values (4,2,'ETL')
insert into @Sub values (5,2,'Budgeting')insertinto @Sub values (6,2,'HATF')
--- Auto
select
std .name,sub.sbj_id,sub.snamefrom @Std std,@Sub subwhere std.std_id=sub.std_idfor

select
std xml Auto .name,sub.sbj_id,sub.snamefrom @Std std,@Sub subwhere std.std_id=sub.std_idfor xml Auto,Elements