<data>
    <registrants>
       <registrant>...</registrant>
       <registrant>...</registrant>
    </registrants>
    <pageviews>
        <total>99999</total>
    </pageviews>
</data>

SELECT registrant_id 
FROM registrant
FOR XML PATH('registrants'), TYPE

SELECT COUNT(pageview_id) AS total 
FROM pageviews
FOR XML PATH('pageviews'), TYPE

TYPE,GROUP,VALUE
Books,Hardback,56
Books,Softcover,34
CDs,Singles,45
CDS,Multis,78
The output style I need is:

<data>
  <variable name="TYPE">
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
  </variable>
 <variable name="TYPE">
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
   <row>
     <column>GROUP</column>
     <column>VALUE</column>
   </row>
  </variable>
</data>
Edit: As far as I can tell I require the multiple values. I'm generating XML for use with Xcelsius (Linking XML and Xcelsius) so have no control over in the formatting of the XML. I can generate the XML using ASP as per the linked tutorial, but I was hoping to get it straight from SQL Server.

Edit 2: I was hoping for something elegant and tidy... but Godeke's example got the closest. Some fiddling with the SQL and I've come up with:

select
   "type" as '@name', 
   "group" as 'row/column',
   null as 'row/tmp', 
   "value" as 'row/column'
from tableName
for xml path('variable'), root('data')

original post by 
http://stackoverflow.com/questions/251957/ms-sql-2005-table-to-xml