Another example:
CREATE PROC dbo.temp_workflow_usage2 (@xml XML OUTPUT)
AS
SELECT @xml =
(SELECT 1 AS Tag, NULL AS Parent, [SalesOrderID] AS
[SalesOrderHeader!1!SalesOrderID],
[OrderDate] AS [SalesOrderHeader!1!OrderDate],
[TotalDue] AS [SalesOrderHeader!1!TotalDue]
FROM Sales.SalesOrderHeader
FOR XML EXPLICIT, TYPE)
GO
DECLARE @xml xml
EXEC dbo.temp_workflow_usage2 @xml OUTPUT
SELECT @xml
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Michael Morse"
discussions.microsoft.com> wrote in message
news:4969C5E9-0891-4A30-AC6A-2ABBA6864357@microsoft.com...> if I try to do insert into dbo.test exec temp_workflow_usage I get
>
> the FOR XML clause is not allowed in a insert statement. :-(
>
> "Peter W. DeBetta" wrote:
>
>> Procs always return an int or null.
>>
>> You either need to make dbo.temp_workflow_usage a UDF that returns XML or
>> insert the results of the proc into a temp table with an xml column and
>> work
>> from there...
>>
>> Also:
>>
>> PRINT '@xml = ' + CAST (@xml AS VARCHAR(MAX))
>>
>>
>> --
>> Peter DeBetta, MVP - SQL Server
>>
http://sqlblog.com
>> --
>> "Michael Morse"
discussions.microsoft.com> wrote in message
>> news:3DF4FEE7-47A8-465F-BE35-E5CDBFAA34EC@microsoft.com...>>> Hi,
>>>
>>> I'm trying to get the xml result from a proc using that uses xml
>>> explicit
>>> and it seems like i'm getting an int back? Why wouldn't I be getting
>>> back
>>> an
>>> xml type? Also also get this error.
>>>
>>> declare @xml xml
>>> exec @xml = dbo.temp_workflow_usage
>>> print '@xml =' + @xml
>>>
>>> Msg 402, Level 16, State 1, Line 3
>>> The data types varchar and xml are incompatible in the add operator.
>>>
>>> Thanks for any help
>>>
>>>
>>
>>
>>
