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),
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
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”
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.