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

Burhan Khalid thelist at meidomus.com
Fri Nov 7 12:57:38 CST 2003


Joshua Olson wrote:

> 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.

fwiw, MySQL 4.0 will also produce query results as XML :)

-- 
Burhan Khalid
thelist[at]meidomus[dot]com
http://www.meidomus.com
-----------------------
"Documentation is like sex: when it is good,
  it is very, very good; and when it is bad,
  it is better than nothing."



More information about the thelist mailing list