title: Using attribute more than once in FOR XML Path T-SQL query with same element name
body: <p>I am trying to create an xml output in SQL 2008 using FOR XML Path. This is working fine:</p>
  
  <pre><code>&lt;Taxonomy&gt;
        &lt;Category Level="1"&gt;Clothing&lt;/Category&gt;
        &lt;SubCategory Level="2"&gt;Jeans&lt;/SubCategory&gt;
   &lt;/Taxonomy&gt;
  </code></pre>
  
  <p>But I would like the output to be:</p>
  
  <pre><code>&lt;Taxonomy&gt;
        &lt;Category Level="1"&gt;Clothing&lt;/Category&gt;
        &lt;Category Level="2"&gt;Jeans&lt;/Category&gt;
  &lt;/Taxonomy&gt;
  </code></pre>
  
  <p>Of course you can code as following:</p>
  
  <pre><code>1 as 'Taxonomy/Category/@Level',
  2 as 'Taxonomy/Category/@Level',
  t.MainCat as 'Taxonomy/Category', 
  t.SubCat as 'Taxonomy/Category',
  </code></pre>
  
  <p>But this gives an error message: 
  <em>Attribute-centric column 'Column name is repeated. The same attribute cannot be generated more than once on the same XML tag.</em></p>
  
  <p>What can be done to get the desired output?
  Would a subselect work or some kind of cross apply? Or perhaps a union? But how?</p>
  
  <p>---- EDIT - after several answers came up with following solution:</p>
  
  <pre><code>SELECT 
  1 as 'Category/@Level',
  t.Cat as 'Category'
  FROM table t
  
  UNION
  
  SELECT 
  2 as 'Category/@Level',
  t.SubCat as 'Category'
  FROM table t
  
  FOR XML PATH (''), ROOT('Taxonomy')
  </code></pre>
  
  <p>gives this output:</p>
  
  <pre><code>&lt;Taxonomy&gt;
    &lt;Category Level="1"&gt;Clothing&lt;/Category&gt;
    &lt;Category Level="2"&gt;Jeans&lt;/Category&gt;
  &lt;/Taxonomy&gt;
  </code></pre>
  
  <p>Still have to figure out how to put this partial coding in a much larger code with several 'nested' FOR XML's already</p>