Could HTTP Soap / T-SQL endpoint be replaced by Reporting Services XML Reports?

I have been working on a POC using SQL Server 2008 and Google Maps.  The POC was to use Geo Spatial technology to illustrate our Geo Spatial data easily.   I will talk about this in more detail in another blog post as I would like talk about a problem I faced while creating this POC. 

The problem I faced was how could I get my Geo Spatial data, which was stored within SQL Server 2008 using the new geography data type, into  "static" HTML?

So I thought that SQL Server HTTP Endpoints for SQL would be great thing to use to solve this problem.   As all I was after was xml document to stream over HTTP.   There was no business logic needed so all the .Net would be is just another layer acting as go between.  Also I didn’t what to waste time on what code which wouldn’t have added any value.

So I created my stored procedure to produce an XML document that I found usable by using the For XML Path.  Then I started to look at how to create a HTTP endpoint for SOAP. After working through this I was presented with this message from my SQL Server 2008: "Creating and altering SOAP endpoints will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it."

It was nice to see that Microsoft are giving people plenty or warning about breaking changes for next version of SQL server.  It’s a surprise that a feature is being dropped so quick, second only to Notification Services since I have known the product, but I can understand the decision as a lot of questions on how, why  &  should it be used were ask by some of our SQL Server developers.   So I took notice of Microsoft warning and tried to see if there was another method I could get my data into an XML document and streamed over HTTP without using .Net code and web services.

All I was doing with this data was just reading for display via another component.  The data was in a structured format.   Then I thought about Reporting Services, as you can write a report and get the reporting server to render to XML.   So I quickly wrote a report to get the outputted xml into the format that I wanted.  Then in my static html I changed my http request to point to my report server with added option to render to XML.

This approach did work and was great, as I managed to produce a page that could request data XML data and render data without any need to write .Net code.  The static html page would always remain up to date.  Another bonus was I had the report ready to show the data in a table from without having to re-write or reduplicating SQL code.  I was also then able to extend this to accept parameters so I could order the data base upon some user data from the HTML input control.

Now this approach is not always going to work for every situation.  One reason is that reporting services security will not allow anonymous access.  So when using a windows security the report server would need to be on the same server.  A method to overcome this would be to create some forms authentication method on the report server and get your site to create the form cookie automatically.

Another potential problem is way that Reporting Services sends the XML data other HTTP.  The XML data is actually sent as an attachment file on the HTTP response.  Now some applications, like the side bar gadgets, might not be allowed to, or can’t, handle http attachments.