Converting SQL to XML with stored procedure

Question

I'm currently building a task scheduler that emails various types of data reports to recipients.

I've started building a stored procedure in SQL Server to output the Data, but realised that each different report type returns completely different data sets. My idea to solve this problem would be to output the data in XML, removing the problem of different data types being returned.

I would proceed with this, but I've read in various places that this solution is not good in terms of maintainability.

The simplest approach to this would be to have a different stored procedure for each report type, which there are about 12 of, making it possibly very time consuming (especially as I'm relatively new to stored procedures).

The question is, is it practical to follow the XML output approach, or is there a simpler, more elegant way to do it?

Thanks in advance.

Edit: The problem stems from the fact the stored procedure will then be processed by Entity Framework, which I'm guessing wouldn't know how to convert it into class/objects if it had multiple return types.

Germann Arlington · Accepted Answer · 2012-08-09 12:53:18Z

1

Are you familiar with https://www.google.co.uk/search?q=SQL+for+xml
Though I am not sure that you need to convert your output to XML in a first place, well written reporting tool can easily process variable output formats.

answered Aug 9, 2012 at 12:53

Germann Arlington

3,3532 gold badges19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Over a year ago

The Stored Procedure will then be passed to Entity Framework, so I assumed having multiple output formats would mess that up?

Germann Arlington Over a year ago

Generally it is the same output format (resultSet), just different fields within the resultSet itself. It can be handled by any Framework correctly (if it is configured correctly).

Tom · Accepted Answer · 2012-08-10 11:27:04Z

0

Why not just build the report in SSRS, and email the output of that? That way you'd be done by the end of next week

edited Aug 10, 2012 at 11:27

Tom

1,3741 gold badge18 silver badges53 bronze badges

answered Aug 9, 2012 at 12:46

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

3 Comments

podiluska Over a year ago

Why not just build the report in SSRS, and email the output of that? That way you'd be done by the end of next week.

Tom Over a year ago

Sounds like a possibility. Would there be a way to build the reports and set up subscriptions via an SQL query? The site I'm currently working on's objective is to allow customers using our software the ability to set up their own reports via our own interface/website.

Tom Over a year ago

Thanks for the tip on SSRS, this is the route I will pursue.

Collectives™ on Stack Overflow

Converting SQL to XML with stored procedure

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related