[thelist] Free Tip - Getting SQL Server to build your table rows for you

Joshua Olson joshua at waetech.com
Fri Nov 7 10:31:37 CST 2003


<tip type="SQL" author="Joshua Olson">
Here is a trick if you need to dump information from an SQL table directly
into an HTML table.  Note, this works on MS SQL Server 2k:

SELECT
    id AS td
  , name AS td
  , username AS td
  , password AS td
FROM admins tr
ORDER BY 1
FOR XML AUTO, ELEMENTS

That will produce an output stream that looks roughly like this:

<tr><td>1</td><td>Administrator</td><td>administrator</td><td>password</td><
/tr><tr><td>2</td><td>Joshua Olson</td><td>joshua</td><td>12345</td></tr>

This block of code is ready to be dropped into a table on the page.

One tricky point is that the result set you get back will have one column
with the data, and the column name will be XML_[GUID].  Most scripting
languages should have tools in place to let you figure out how to get to the
data.
</tip>

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168



More information about the thelist mailing list