EMP

      

      
ID
      
Name
      
Country
      
Salary
      

      

      
1
      
Vijay     
      
India     
      
6000
      

      

      
2
      
Bill      
      
USA       
      
8000
      

      

      
3
      
Kris      
      
France    
      
3000
      

      

      
4
      
Bhagat    
      
India     
      
2000
      

      

      
5
      
Steve     
      
USA       
      
1000
      

      
    

      

      

      
SELECT * FROM EMP FOR XML 
      PATH('Country'), ROOT('Root')                   
      
      SELECT p.Country AS "@Name",
      (
      SELECT
          ID      AS "@ID",
          Name    AS "Name",
          Salary  AS "Salary"
      FROM    EMP c
      WHERE   p.Country = c.Country
      FOR XML PATH('EMP'), TYPE
      )
      FROM ( SELECT DISTINCT Country FROM EMP ) p
      FOR XML PATH('Country'), ROOT('Root')