IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'getPeople'
AND type = 'P')
DROP PROCEDURE getPeople
GO
FROM sysobjects
WHERE name = N'getPeople'
AND type = 'P')
DROP PROCEDURE getPeople
GO
CREATE PROCEDURE getPeople
AS
declare @au_lname varchar(40),@au_fname varchar(40),@returnXML varchar(4000)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
set @returnXML = '<peoples>'
FETCH NEXT FROM Employee_Cursor
into @au_lname,@au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @returnXML = rtrim(@returnXML) + '<people><lname>' + @au_lname +'</lname><fname>' + @au_fname +'</fname></people>'
FETCH NEXT FROM Employee_Cursor
into @au_lname,@au_fname
END
set @returnXML = rtrim(@returnXML) + '</peoples>'
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
select @returnXML
GO
AS
declare @au_lname varchar(40),@au_fname varchar(40),@returnXML varchar(4000)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
set @returnXML = '<peoples>'
FETCH NEXT FROM Employee_Cursor
into @au_lname,@au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
set @returnXML = rtrim(@returnXML) + '<people><lname>' + @au_lname +'</lname><fname>' + @au_fname +'</fname></people>'
FETCH NEXT FROM Employee_Cursor
into @au_lname,@au_fname
END
set @returnXML = rtrim(@returnXML) + '</peoples>'
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
select @returnXML
GO