create xml file from sql script

how to Generate xml file in sql server 2005 using tsql

From stackoverflow

• Google is your friend.

• Stored proc exec DBName.dbo.Table is select * from table for xml path SET @SQL= 'bcp "exec DBName.dbo.Table" queryout c:\myfile.xml -w -r -t -SServerName -T' EXEC Master..xp_CmdShell @SQL

• Select Emp_Id,Emp_Name from tblEmployee Where Emp_Id < 3
  For XML AUTO,ELEMENTS
  --For XML [MODE],ELEMENTS
  

  MODE

  AUTO, RAW, EXPLICIT

  Result [AUTO]

  <tblEmployee>
   <Emp_Id>1</Emp_Id>
   <Emp_Name>AAA</Emp_Name>
  </tblEmployee>
  <tblEmployee>
   <Emp_Id>2</Emp_Id>
   <Emp_Name>BBB</Emp_Name>
  </tblEmployee>
  

  Result [RAW]

  Above Xml with Instead of <tblEmployee> here..... <ROW> and </tblEmployee> is </ROW>