Pin It

Creating Xml in Stored Procedure using FOR Xml Path

I always like to create xml in Stored Procedure because its one of the simplest and fastest method, within a fraction of second it must form an xml,  no need to loop through the dataset or datable in C#. Lets go over and start with simple example to create Xml in stored procedure using FOR Xml Path

Create and sample Employee Table 

 CREATE TABLE [dbo].[Employee]
( [ID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) NULL,
[Address] [varchar](100) NULL,
 [PhoneNo] [varchar](50) NULL ) ON [PRIMARY]

Insert Some sample values like

  insert into [employee] (EmpName,Address,PhoneNo) values ('Swan','West street','9899434343')

Creating Xml using FOR Xml Path
 declare @result nvarchar(max)
 set @result = (SELECT t.ID,t.EmpName, t.Address, t.PhoneNo FROM employee t FOR XML
 PATH('Employee') ) select @result as Result
   
  Output : 
<Employee><ID>1</ID><EmpName>Swan</EmpName><Address>West street</Address><PhoneNo>9899434343</PhoneNo></Employee>

  Creating XML in STORED Procedure using FOR Xml Path

0 comments: