November 17, 2008 at 2:38 pm
I want to pass my XML result set to a variable. Let me know your possibilities please.
Here is my code how am I trying:
create procedure dbo.test1
as
select
'test1' as FirstName,
'test2' as LastName
from dbo.testtable Person with(nolock)
for xml auto, elements
return -1
*/
declare@t xml
set @t = exec dbo.test1
Thanks
March 4, 2011 at 5:59 am
create PROC [dbo].[PTH_Overall_SecUpdate_Byfurcation_DPMA_PR1]
(@OUTXML xml output)
as
begin
set @OUTXML= SELECT M.CR ,
ISNULL([Critical],0) [Critical],
ISNULL([Low],0)[Low] ,
ISNULL([Moderate],0)[Moderate] ,
ISNULL([Important],0)[Important] ,
([Critical]+[Low]+[Moderate]+[Important])as totalpatch
FROM p1 M WITH(NOLOCK)
for xml auto,root('R')
end
Im getting error in "Msg 156, Level 15, State 1, Procedure PTH_Overall_SecUpdate_Byfurcation_DPMA_PR1, Line 77
Incorrect syntax near the keyword 'set'."
Plz help me out ....
March 9, 2011 at 7:21 am
Shaikh, check this out. I added parentheses.
create PROC [dbo].[PTH_Overall_SecUpdate_Byfurcation_DPMA_PR1]
(@OUTXML xml output)
as
begin
set @OUTXML=
(
SELECT
M.CR ,
ISNULL([Critical],0) [Critical],
ISNULL([Low],0)[Low] ,
ISNULL([Moderate],0)[Moderate] ,
ISNULL([Important],0)[Important] ,
([Critical]+[Low]+[Moderate]+[Important])as totalpatch
FROM p1 M WITH(NOLOCK)
for xml auto,root('R')
)
end
- arjun
https://sqlroadie.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply