Friday, February 10, 2012

Build a xml doc from parameters passed to sp

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