FOR XML AUTO question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FOR XML AUTO question

Author

Topic

gtk
Starting Member

6 Posts

Posted - 2007-12-17 : 08:45:48

Hi,
I have the following sample code:


CREATE PROCEDURE SomeProc
AS
BEGIN
   SET NOCOUNT ON;

    SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
       Orders.OrderID, Orders.OrderDate, Orders.EmployeeID
    FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    ORDER BY Employees.EmployeeID ASC
    FOR XML AUTO
END
GO

which provides some output in xml format.
What i want to do is to return the xml output in a variable, so as to use it after that.
if i use the following:


declare @retXml navrchar(200)
exec @retXml=SomeProc
select @retXml

it complaints tha the result must be an integer!How can i take the xml result in a variable?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 08:49:23

Can you try assigning it to a xml variable?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:55:09

set @myxmlvariable = (select ... for xml auto)

E 12°55'05.25"
N 56°04'39.16"

gtk
Starting Member

6 Posts

Posted - 2007-12-17 : 09:38:49

Thanks for your help.
It worked when i modified the procedure like this(using output variable):

CREATE PROCEDURE SomeProc (@xmlvar xml output)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
   
	SET NOCOUNT ON;

    set @xmlvar=(SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
       Orders.OrderID, Orders.OrderDate, Orders.EmployeeID
    FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    ORDER BY Employees.EmployeeID ASC
    FOR XML AUTO)
END
GO

and running the following script

declare @retXml xml
exec SomeProc @xmlvar=@retXml output
select @retXml as 'gamwthpoutanamou'

However, is there a way to achieve this without the need of modifying my procedure??It would be better if i didnt change my original procedure.
I tested the following, but it didnt work:

declare @retXml xml
exec @retXml=SomeProc2
select @retXml

error message:Operand type clash: int is incompatible with xml

Thnaks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-17 : 09:48:16

no. you have to change your sproc.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources