Returning HTML/XML on SQL7?
-
Is it possible to return data as HTML or XML (using HTML tags) in SQL 7? I've looked at the web assistant, and it's close. The problem is that it requires that the data be written to a file. I want to return it in a data stream. Is that possible? I want to run a proc and return one large string with all the data.
TIA
-
You can fake it some by just including the tags as strings in your select. Not pretty, but will get you close. Beyond that there was a web release for SQL7 that would let you do some XML, been a long time since I looked at it - try the MS site for that. More work, you could use the sp_oa procs to open an ADO recordset, use the XML DOM to build whatever string you want (or just do string processing). Not a great plan, but for small output is probably doable.
Andy
-
Hmm, since that was too easy, now let me complicate it
I want to generate a view (not hard) combining a regular query and a stored procedure. I need the procedure because I'm creating a giant string with the HTML content (returning it with a simple print).
So, how can I do "SELECT colname, EXEC strdprc"? What would be a better way?
-
quote:
Hmm, since that was too easy, now let me complicate itI want to generate a view (not hard) combining a regular query and a stored procedure. I need the procedure because I'm creating a giant string with the HTML content (returning it with a simple print).
So, how can I do "SELECT colname, EXEC strdprc"? What would be a better way?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Sorry for the blank post. You could use sp_executesql to execute two commands like so:
declare @cmd nvarchar(4000)
set @cmd = 'select colname' + char(13) + 'exec strdprc'
exec sp_executesql @cmd
Is this what you where asking?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Right, but that would execute one command, and then the other. I actually want the data that comes back from the stored proc to be included as a column in the query. More like
select colname, (exec strdprc) as col2
The issue is, I'm trying to make data available to an news-letter-email thing. It takes a simple query of, say, name/email/address/etc and sends a message to each one. So I only have use of the fields of one query (or view). My challenge is to include some dynamic content in the newsletter, hence the html table. I can generate the table, but to get it back as a single field, I have to append each row to a variable and return the variable. Then, do that for each row of the outer query. Sort of a subquery-blob.
And, I don't know how to in SQL. If I didn't have to do it in this program, that'd be a different story...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply