返回xml資料的預存程序

IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'getPeople'
    AND    type = 'P')
    DROP PROCEDURE getPeople
GOCREATE 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