?
Solved

UDF can't be used in XML FOR clause but SP's can be used - SQL Server 2005 / 2008

Posted on 2009-04-19
9
Medium Priority
?
171 Views
Last Modified: 2012-05-06
Hi,

Can you please explain me in simple terms and examples :

UDF can't be used in XML FOR clause but SP's can be used - SQL Server 2005 / 2008.

Thanks
0
Comment
Question by:milani_lucie
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24180948
FOR XML is a clause that transforms the output of an SQL statement into xml formatted block.

Basically both table valued UDF's and stored procedures can return data from an SQL statement, but while you can use the XML FOR clause to transform the results in a stored procedure and pass it back to a client application, a UDF or a VIEW  is not allowed to use the FOR XML clause within the object definition text.

Instead you can select from the view or function and then transform the output using FOR XML clause..
0
 

Author Comment

by:milani_lucie
ID: 24181003
Means

CREATE PROC spTest
AS
SELECT X, Y FROM T FOR XML AUTO -- This is allowed
GO

CREATE FUNCTION funcTest ......
AS
SELECT X, Y FROM T FOR XML AUTO  -- This is NOT allowed
GO

Am i correct ? Please correct me if i am wrong !

Thanks
0
 
LVL 25

Expert Comment

by:reb73
ID: 24181066
Yes, more or less correct (UDF needs to be declared with the return type)

However for the second, you can do the following -

SELECT * from dbo.funcTest FOR XML AUTO
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 25

Expert Comment

by:reb73
ID: 24181068
Sorry it should be -

SELECT * from dbo.funcTest() FOR XML AUTO

(The brackets are necessary for functions)
0
 

Author Comment

by:milani_lucie
ID: 24181149
reb73,

SELECT * from dbo.funcTest() FOR XML AUTO  -- You can't do this

but

How can it be for stored procedure ? Can you please provide SQL command for that ?

Thanks
0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24181195
See the UDF(function) and storedprocedure(sp) examples below -
/* function example */
create function dbo.objnames()
returns table 
as
return (select [name] as objname from sysobjects)
go
 
select * from dbo.objnames() for xml auto
 
/* sp example */
create procedure dbo.up_objnames
as
	select [name] as objname from sysobjects for xml auto
go
 
exec dbo.up_objnames

Open in new window

0
 

Author Comment

by:milani_lucie
ID: 24181210
reb73:

UDF can't be used in XML FOR clause. But according to your theory:

select * from dbo.objnames() for xml auto

We can able to use it. Am i missing something here ?

Thanks
0
 
LVL 25

Expert Comment

by:reb73
ID: 24181245
You cannot use a FOR XML within the function definition. But you can use it with the select statement that calls the function

So you cannot do something like this -

create function dbo.objnames()
returns table
as
return (select [name] as objname from sysobjects FOR XML AUTO)
go

Does this make it clearer?

0
 

Author Comment

by:milani_lucie
ID: 24181254
OK...I understood right now. Thanks a lot for your explanation with examples.

Thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question