title: SQL Server FOR XML PATH vary tags dynamically
body: <p>Trying to use FOR XML in SQL 2012. Need to have a result like this:</p>
  
  <pre><code>&lt;Loader xmlns:xsi="url1" xmlns="url2"&gt;
    &lt;Buy_New&gt;
      &lt;Ticker&gt;IBM&lt;/Ticker&gt;
      &lt;Acct&gt;12345&lt;/Acct&gt;
      &lt;Qty&gt;10&lt;/Qty&gt;
    &lt;/Buy_New&gt;
    &lt;Sell_New&gt;
      &lt;Ticker&gt;MSFT&lt;/Ticker&gt;
      &lt;Acct&gt;12345&lt;/Acct&gt;
      &lt;Qty&gt;15&lt;/Qty&gt;
    &lt;/Sell_New&gt;
    &lt;Buy_New&gt;
      &lt;Ticker&gt;IBM&lt;/Ticker&gt;
      &lt;Acct&gt;12345&lt;/Acct&gt;
      &lt;Qty&gt;10&lt;/Qty&gt;
    &lt;/Buy_New&gt;
  &lt;/Loader&gt;
  </code></pre>
  
  <p>After looking on here, MSDN, etc., I am not seeing a way to dynamically change the <code>&lt;Buy_New&gt;</code> and <code>&lt;Sell_New&gt;</code> (I have about 20 different types to work with) in my query, I have explored using FOR XML PATH and FOR XML EXPLICIT but both seem to require static element tags.</p>
  
  <p>Is there a way to drive the tags off the rows in the query?</p>
  
  <p>@Jon C Sure: here is what I have:</p>
  
  <pre><code>SELECT
  (
      SELECT
      Investment1 as Investment,
      EventDate1 as Date,
      Quantity1 as Quantity
      FROM #temptable e1 where e1.temp_id = e.temp_id
      FOR XML PATH(''),TYPE
  ) AS 'DYNAMICTAG'
  FROM #temptable e
  FOR XML PATH(''), ROOT('Loader')`
  </code></pre>
  
  <p>Here is a sample of the result. The DYNAMICTAG is the part that needs to change to be <code>&lt;Buy_New&gt;</code>, <code>&lt;Sell_New&gt;</code> etc.</p>
  
  <pre><code>&lt;Loader&gt;
    &lt;DYNAMICTAG&gt;
      &lt;Investment&gt;XYZ&lt;/Investment&gt;
      &lt;Date&gt;2015-05-08T00:00:00&lt;/Date&gt;
      &lt;Quantity&gt;50&lt;/Quantity&gt;
    &lt;/DYNAMICTAG&gt;
    &lt;DYNAMICTAG&gt;
      &lt;Investment&gt;ABC&lt;/Investment&gt;
      &lt;Date&gt;2015-05-08T00:00:00&lt;/Date&gt;
      &lt;Quantity&gt;10&lt;/Quantity&gt;
    &lt;/DYNAMICTAG&gt;
    &lt;DYNAMICTAG&gt;
      &lt;Investment&gt;CSCO&lt;/Investment&gt;
      &lt;Date&gt;2015-05-08T00:00:00&lt;/Date&gt;
      &lt;Quantity&gt;50&lt;/Quantity&gt;
    &lt;/DYNAMICTAG&gt;
    &lt;DYNAMICTAG&gt;
      &lt;Investment&gt;IBM&lt;/Investment&gt;
      &lt;Date&gt;2015-05-08T00:00:00&lt;/Date&gt;
      &lt;Quantity&gt;30&lt;/Quantity&gt;
    &lt;/DYNAMICTAG&gt;
  &lt;/Loader&gt;
  </code></pre>