I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.
ie:
declare @.par2 datetime;
set @.par2=getdate();
exec spTest 'test',@.par2
create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)
as
...do some job
i would like to obtain:
<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>
or, btw the same result i could get querying with the forxml clause.
I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.
Thank you very much
Maybe something like one of these?
Code Snippet
declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()
select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult
/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/
select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')
/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/
No comments:
Post a Comment