For XML PATH ROOT in SQL Server 2008

Wednesday, August 18, 2010 |

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.

Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:

    <ID value="1" />
    <FirstName value="Ritesh" />
    <LastName value="Shah" />
    <ID value="2" />
    <FirstName value="Rajan" />
    <LastName value="Jain" />

Let me give you TSQL to generate table in SQL Server and insert records in that.

Create Table EmployeeData
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)

insert into EmployeeData
select 'Ritesh','Shah' UNION ALL
select 'Rajan','Jain'

So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.

select ID as "ID/@value"
     , FirstName as "FirstName/@value"
     , LastName as "LastName/@value"
from (
   Select * from EmployeeData
) as t
for xml path('Employee'), root('Employees');

Isn’t it easy to use?

BTW, below given are some links which will redirect you to my some of the past articles on XML subject.

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of