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

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))

What what i understand for XML Explicit is not allowed in a UDF, I also have
been unseccesful in getting the XML into a temp table. I will work on it but
if you have a sample that would be excellent.

Thanks,

"Peter W. DeBetta" wrote:

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:

An example that will get the XML back from a proc ito an xml variable:

CREATE PROC dbo.temp_workflow_usage
AS
SELECT
(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) AS XmlReturn
GO

CREATE TABLE #temp (XmlReturn XML)

INSERT INTO #temp EXEC dbo.temp_workflow_usage

DECLARE @xml xml

SELECT TOP 1 @xml = XmlReturn FROM #temp

SELECT @xml

DROP TABLE #temp

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Michael Morse" discussions.microsoft.com> wrote in message
news:4969C5E9-0891-4A30-AC6A-2ABBA6864357@microsoft.com...

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...

Someone emailed me via my blog with the subject "DumbAss" and said in the
body of the email "and you call yourself a SQL programmer."

Apparently, this person, who was so brave as to identify himself or herself
as "To Bad" petersdumbblog.com>, couldn't actually solve
the problem and had to instead insult me, even with a viable solution that I
offered.

This person even gave a grammatically incorrect "name". "To bad" should have
been "Too Bad".

Sad, really.

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Michael Morse" discussions.microsoft.com> wrote in message
news:4969C5E9-0891-4A30-AC6A-2ABBA6864357@microsoft.com...

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...