title: SQL Server 2008 - Need column names as an attribute
body: <p>I need to create a T-SQL query in SQL Server 2008 which outputs data as XML. But this is a generic process that queries data with different columns, which could have spaces in the column names. Therefore, I'd like the XML to list the column name as an attribute (spaces aren't allowed in element names).</p>
  
  <p>Given a master table which links to a detail table for child records, I would want the output to look like this:</p>
  
  <pre><code>&lt;master id="123"&gt;
     &lt;detail colname="customer"&gt;John Smith&lt;/detail&gt;
     &lt;detail colname="amount"&gt;888.45&lt;/detail&gt;
     &lt;detail colname="date"&gt;01/01/01&lt;/detail&gt;
  &lt;/master&gt;
  &lt;master id="456"&gt;
     &lt;detail colname="customer"&gt;Suzie Jones&lt;/detail&gt;
     &lt;detail colname="amount"&gt;1000.25&lt;/detail&gt;
     &lt;detail colname="date"&gt;05/05/01&lt;/detail&gt;
  &lt;/master&gt;
  </code></pre>
  
  <p>The columns in the detail record can vary, so I can't hardcode them.</p>
  
  <p>I believe this may be possible using PIVOT command, but that gets really ugly when you don't know structure of data.  I feel like there's got to be a way to get a column name to appear as an attribute value!</p>
  
  <p>thanks for any advice.</p>