Stored Proc results into XML

• terry999

  SSCertifiable

  Points: 5033

  More actions

  February 18, 2016 at 5:53 am

  #399741

  Hi I have an existing stored proc, that selects data only. I would like to return data as XML

  I do not want to change the SP, I'd like to treat this SP1 as a black box

  I can only think of:

  creating another SP2

  in SP2 insert results of SP1 into table variable or temp table

  Select FOR XML from #t or table variable

  Any better ideas?

  I wouldn't mind this approach however I like to dynamically create the #t in SP2 so if SP1 changes the columns returned, SP2 is insulated from changes. Unlike views/functions I do not know of a way to

  SELECT * INTO #t FROM myview/myfunc

  with SPs

  Thanks

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  February 18, 2016 at 6:15 am

  #1859535

  If it is truly a black box then using any table(s) will not work because if you know the what the output will be then it is not a black box.

  The only way I can see is

  SELECT * FROM OPENQUERY([server],'EXECUTE [database].[owner].[procname]') FOR XML PATH('');

  This has the hallmarks of future problems and bad practice all over it.

  Far away is close at hand in the images of elsewhere.
  Anon.

• terry999

  SSCertifiable

  Points: 5033

  More actions

  February 18, 2016 at 8:45 am

  #1859601

  Thank you

  I will try this out.