Convert SQL Table to XML in SQL Server

Convert SQL Table to XML in SQL Server 

--> XML stands for EXtensible Markup Language.

--> XML was designed to store and transport data.

--> XML was designed to be both human- and machine-readable.

SQL Table

DECLARE @Employee as table (Sno int identity(1,1),EmpName varchar(50),Company varchar(50),

Location varchar(50),Stream varchar(50))

insert into @Employee(EmpName,Company,Location,Stream) VALUES('Venkatesh','Company1','Hyd','BI')

insert into @Employee(EmpName,Company,Location,Stream) VALUES('Krishna','Company2','Bng','SQl')

insert into @Employee(EmpName,Company,Location,Stream) VALUES('Rama','Company3','Pune','.Net')

insert into @Employee(EmpName,Company,Location,Stream) VALUES('Gopal','Company4','Chennai','Java')

SELECT Sno as '@Sno',

EmpName as 'EmpName',

Company as 'EmployeeInDetail/Company',

Location as 'EmployeeInDetail/Location',

Stream as 'EmployeeInDetail/Stream'

from @Employee

for XML PATH('EmployeeDetails')

XML output

<EmployeeDetails Sno="1">

  <EmpName>Venkatesh</EmpName>

  <EmployeeInDetail>

    <Company>Company1</Company>

    <Location>Hyd</Location>

    <Stream>BI</Stream>

  </EmployeeInDetail>

</EmployeeDetails>

<EmployeeDetails Sno="2">

  <EmpName>Krishna</EmpName>

  <EmployeeInDetail>

    <Company>Company2</Company>

    <Location>Bng</Location>

    <Stream>SQl</Stream>

  </EmployeeInDetail>

</EmployeeDetails>

<EmployeeDetails Sno="3">

  <EmpName>Rama</EmpName>

  <EmployeeInDetail>

    <Company>Company3</Company>

    <Location>Pune</Location>

    <Stream>.Net</Stream>

  </EmployeeInDetail>

</EmployeeDetails>

<EmployeeDetails Sno="4">

  <EmpName>Gopal</EmpName>

  <EmployeeInDetail>

    <Company>Company4</Company>

    <Location>Chennai</Location>

    <Stream>Java</Stream>

  </EmployeeInDetail>

</EmployeeDetails>