returning xml from function

I have a sproc I am trying to convert to a function.
The sproc works fine the sproc is giving me errors.
I am not sure if the With or the XML datatype is the problem
Msg 156, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure SurveyResultsXML, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 16
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure SurveyResultsXML, Line 39
Incorrect syntax near ')'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[SurveyResultsXML]
(@.SurveyID int)
RETURNS XML
AS
BEGIN
declare @.result XML;
set @.result=(
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
for xml Raw('Results'), Elements
);
return @.result ;
ENDHi Chunk,
I understand that your SQL statement including CTE failed to be executed
with the error messages.
If I have misunderstood, please let me know.
The problem is that CTE statement cannot be used as a right value of SET
expression. I tried to use a temp table to store the query result; however
since temp table is not supported in a function, I must use a stored
procedure to work it. Please refer to:
CREATE PROCEDURE [dbo].[SurveyResultsXML]
(@.SurveyID int,
@.result xml output)
AS
BEGIN
with
question ( QuestionID ) as
(Select distinct QuestionID From SurveyResults Where
SurveyID=@.SurveyID),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
Where SurveyID=@.SurveyID
group by questionid, answer)
select SurveyName, SurveyQuestions,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
) into #TempResultTable
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE SurveyID=@.SurveyID
set @.result = (
select * from #TempResultTable
for xml Raw('Results'), Elements
)
drop table #TempResultTable
END
I think that using SP is a better way; but I am not sure if you have to use
a function in your situation.
Actually I can use a view to wrap the CTE express by removing "WHERE
SurveyID=@.SurveyID", then use a function to query from the view like this:
CREATE VIEW v_getSurveyQuestions
AS
with
question ( QuestionID ) as
(Select distinct SurveyID, QuestionID From SurveyResults),
results ( QuestionID, Answer, AnswerCount) as
(Select QuestionID, Answer, Count(Answer)
From SurveyResults
group by questionid, answer)
select SurveyName, SurveyQuestions, SurveyID,
(
Select question.QuestionID, results.Answer, results.AnswerCount
From question join results
on question.questionid =results.questionid
for xml auto, Root('QuestionResults'), type
)
from SurveyProfiles Join
Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
CREATE FUNCTION ufn_getSurveyQuestionsByID
( @.surveyId int)
returns xml
AS
BEGIN
declare @.result xml
set @.result = (
select * from v_getSurveyQuestions where SurveyID=@.surveyId
for xml Raw('Results'), Elements
)
return @.result
END
The problem is that if your tables have large amount of data, the
performance may be very poor. I recommend that you use the first method.
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||thanks, it worked great.
"Charles Wang[MSFT]" wrote:

> Hi Chunk,
> I understand that your SQL statement including CTE failed to be executed
> with the error messages.
> If I have misunderstood, please let me know.
> The problem is that CTE statement cannot be used as a right value of SET
> expression. I tried to use a temp table to store the query result; however
> since temp table is not supported in a function, I must use a stored
> procedure to work it. Please refer to:
> CREATE PROCEDURE [dbo].[SurveyResultsXML]
> (@.SurveyID int,
> @.result xml output)
> AS
> BEGIN
> with
> question ( QuestionID ) as
> (Select distinct QuestionID From SurveyResults Where
> SurveyID=@.SurveyID),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> Where SurveyID=@.SurveyID
> group by questionid, answer)
> select SurveyName, SurveyQuestions,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> ) into #TempResultTable
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileI
D
> WHERE SurveyID=@.SurveyID
> set @.result = (
> select * from #TempResultTable
> for xml Raw('Results'), Elements
> )
> drop table #TempResultTable
> END
> I think that using SP is a better way; but I am not sure if you have to us
e
> a function in your situation.
> Actually I can use a view to wrap the CTE express by removing "WHERE
> SurveyID=@.SurveyID", then use a function to query from the view like this:
> CREATE VIEW v_getSurveyQuestions
> AS
> with
> question ( QuestionID ) as
> (Select distinct SurveyID, QuestionID From SurveyResults),
> results ( QuestionID, Answer, AnswerCount) as
> (Select QuestionID, Answer, Count(Answer)
> From SurveyResults
> group by questionid, answer)
> select SurveyName, SurveyQuestions, SurveyID,
> (
> Select question.QuestionID, results.Answer, results.AnswerCount
> From question join results
> on question.questionid =results.questionid
> for xml auto, Root('QuestionResults'), type
> )
> from SurveyProfiles Join
> Surveys on SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileI
D
>
> CREATE FUNCTION ufn_getSurveyQuestionsByID
> ( @.surveyId int)
> returns xml
> AS
> BEGIN
> declare @.result xml
> set @.result = (
> select * from v_getSurveyQuestions where SurveyID=@.surveyId
> for xml Raw('Results'), Elements
> )
> return @.result
> END
> The problem is that if your tables have large amount of data, the
> performance may be very poor. I recommend that you use the first method.
> Hope this helps. Please feel free to let me know if you have any other
> questions or concerns.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi,
Appreciate your update and response. I am glad to hear that the suggestions
are helpful. If you have any other questions or concerns, please do not
hesitate to contact us.
Have a nice day!
Charles Wang
Microsoft Online Community Support