title: Generating XML file from SQL query
body: <p>I'm trying to generate a XML file from SQL query and I have a table that contains following columns:</p>
  <pre><code>ItemNumber, Price, DateFrom, DateTo
  </code></pre>
  <p>The code I use to generate the XML is:</p>
  <pre><code>SELECT
      ItemNumber AS '@ItemNumber',
      Price AS '@Price',
      DateFrom AS 'DateFrom',
      DateTo AS 'DateTo'
  FROM 
      #tempXML
  FOR XML PATH('Item')
  </code></pre>
  <p>what I expect to get is something like this:</p>
  <pre><code>&lt;Item id=&quot;111&quot;&gt;
      &lt;ItemNumber&gt;111&lt;/ItemNumber&gt;
          &lt;Price value=&quot;3000&quot;&gt;
              &lt;DateFrom&gt;2018-01-02&lt;/DateFrom&gt;
              &lt;DateTo&gt;2018-01-30&lt;/DateTo&gt;
          &lt;/Price&gt;
          &lt;Price value=&quot;2500&quot;&gt;
              &lt;DateFrom&gt;2018-01-31&lt;/DateFrom&gt;
              &lt;DateTo&gt;2018-11-22&lt;/DateTo&gt;
          &lt;/Price&gt;
   &lt;/Item&gt;
   &lt;Item&gt;
      &lt;ItemNumber&gt;120&lt;/ItemNumber&gt;
          &lt;Price value=&quot;4000&quot;&gt;
              &lt;DateFrom&gt;2018-01-12&lt;/DateFrom&gt;
              &lt;DateTo&gt;2018-11-22&lt;/DateTo&gt;
          &lt;/Price&gt;
   &lt;/Item&gt;
  </code></pre>
  <p>but instead I get something more like this:</p>
  <pre><code>&lt;Item ItemNumber=&quot;111&quot; Price=&quot;3000&quot;&gt;
      &lt;DateFrom&gt;2018-01-02&lt;/DateFrom&gt;
      &lt;DateTo&gt;2018-01-30&lt;/DateTo&gt;
  &lt;/Item&gt;
  &lt;Item ItemNumber=&quot;111&quot; Price=&quot;2500&quot;&gt;
      &lt;DateFrom&gt;2018-01-31&lt;/DateFrom&gt;
      &lt;DateTo&gt;2018-11-22&lt;/DateTo&gt;
  &lt;/Item&gt;
  &lt;Item ItemNumber=&quot;120&quot; Price=&quot;4000&quot;&gt;
      &lt;DateFrom&gt;2018-01-12&lt;/DateFrom&gt;
      &lt;DateTo&gt;2018-11-22&lt;/DateTo&gt;
  &lt;/Item&gt;
  </code></pre>
  <p>Any kind of help will be greatly appreciated.</p>
  <p><a href="https://i.stack.imgur.com/o64x8.png" rel="nofollow noreferrer">Sample data from the table I use</a></p>
  <pre><code>CREATE TABLE #tempXML
  (
      ItemNumber INT,
      Price INT,
      DateFrom DATE,
      DateTo DATE
  )
  
  INSERT INTO #tempXML
  VALUES
      (111, 3000, '2018-01-02', '2018-01-30'),
      (111, 2500, '2018-01-31', '2018-11-22'),
      (120, 4000, '2018-01-12', '2018-11-22')
  </code></pre>
  <p><strong>SQL Fiddle</strong>: <a href="http://sqlfiddle.com/#!18/a0602/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/a0602/1</a></p>