Using a UDF...
CREATE FUNCTION dbo.temp_workflow_usage_f()
RETURNS XML
AS
BEGIN
DECLARE @xml XML
SET @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)
RETURN @xml
END
GO
DECLARE @xml XML
SET @xml = dbo.temp_workflow_usage_f()
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
>>>
>>>
>>
>>
>>
