October 2, 2006 at 10:42 pm
The online books for SQL Server 2005 are aplenty of examples regarding retrieving data for the database in the form of web services involving SOAP messaging.
However, I cannot find anything explaining how to retrieve data from a web browser in the form: http://servername/sql=sqlquery (eg: http://localhost/sql=select%20*%20from%20customersfor%20xml%20auto) (select * from customers for xml auto)
At least to my knowledge this was the way things were done in SQL Server 2000 through a IIS virtual directory.
Why this way and not a web service ?
Because this way I can place a sql command line on the documentsource property of the «asp:xml» control and ... voilá ! -it keeps things simple. With a couple-of-lines asp.net page and an xsl file I can have very fast results.
How such a straight-through query can be invoked from SQL Server 2005 ? I mean, if I have to build a web service for such a query it means I have to build a WSDL class and thus adds another piece of code to maintain (think of parameter definitions etc). The other way I just only need to modify the database and the sql command invoking the database, no need to modify intermediate classes and the like.
Please advice.
Thanks.
October 3, 2006 at 5:13 pm
If you have Visual Studio it is actually very easy because if you create a DataSet of your table and call the ReadXml method it generates XML of your data for you. If you don't have Visual Studio your other option is to export the data to Excel and save as XML. Try the link below to get started. If you need it run a search for the Excel XML tool box. Hope this helps.
http://www.developer.com/db/article.php/10920_3531196_1
http://msdn2.microsoft.com/en-us/library/360dye2a.aspx
Kind regards,
Gift Peddie
July 7, 2007 at 2:31 am
I think this is not the answer. I have the same problem and I cannot ubderstand why sql server 2005 disable the virtual directories that are very simple to implement. I'm searching almost a week to find a simple solution to get xml data through a http request directly in a browser and I cannot find an answer. I want to consume an xml file directly in a flash application.
July 9, 2007 at 6:45 pm
Did you try creating an http endpoint? (a.k.a. 'web service')
CREATE ENDPOINT [name] AS HTTP(PATH = 'virtual directory name', ....
This is monumentally easier than doing it in SQL 2000, can be done and managed entirely in T-SQL, and doesn't require IIS.
However, you will need to create a stored procedure that accepts whatever SQL you throw at it, and execute it, introducing the same gaping security hole provided by URL queries. SQL 2005 being 'more secure by design' means that feature is gone.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply