I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)
Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:
Code Snippet
exec spGetUserDetails @.ID = 1234 for xml raw
Thanks.I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:
Move the select statement from the stored proc into a user--defined function that returns TABLE:
Code Snippet
-- Example from Books Online
CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );
Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:
Code Snippet
-- Inside the stored proc comment the select statement and call the UDF
-- SELECT * from Products where StoedID = @.storeid -- No longer needed
select * from fn_SalesByStore (@.storeid);
Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.
Code Snippet
select * from fn_SalesByStore (@.storeid) for xml raw
Yazan