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>
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>
0 comments: