title: Converting rows to XML format in SQL Server
body: <p>I have requirement like below.</p>
  
  <p><a href="https://i.stack.imgur.com/5F3W0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5F3W0.png" alt="enter image description here"></a>     </p>
  
  <p>And ddl and dml script for above image is </p>
  
  <pre><code>CREATE TABLE #example
      ([CCP_DETAILS_SID] int, [ACCOUNT_GROWTH] int, [PRODUCT_GROWTH] int, [PROJECTION_SALES] numeric(22,6), [PROJECTION_UNITS] numeric(22,6), [PERIOD_SID] int)
  ;
  
  INSERT INTO #example
      ([CCP_DETAILS_SID], [ACCOUNT_GROWTH], [PRODUCT_GROWTH], [PROJECTION_SALES], [PROJECTION_UNITS], [PERIOD_SID])
  VALUES
      (30001, 0, 0, 1505384.695, 18487.25251, 1801),
      (30001, 0, 0, 1552809.983, 18695.75536, 1802),
      (30001, 0, 0, 1595642.121, 18834.75725, 1803),
      (30002, 0, 0, 10000.32, 18834.75725, 1801),
      (30002, 0, 0, 1659124.98, 18834.75725, 1802),
      (30002, 0, 0, 465859546.6, 18834.75725, 1803)
  ;
  </code></pre>
  
  <p>And i have to convert above results to xml format like below (Output).</p>
  
  <pre><code>ccp_details_sid           xml_format_string
  
  30001                      &lt;period&gt;  
                             &lt;period_sid period_sid=1801&gt;
                             &lt;PROJECTION_SALES&gt;1505384.695&lt;/PROJECTION_SALES&gt;
                             &lt;PROJECTION_UNITS&gt;18487.25251&lt;PROJECTION_UNITS&gt;
                             &lt;ACCOUNT_GROWTH&gt;0&lt;/ACCOUNT_GROWTH&gt;
                             &lt;PRODUCT_GROWTH&gt;0&lt;/PRODUCT_GROWTH&gt;
                             &lt;/period_sid&gt;
                              &lt;period_sid period_sid=1802&gt;
                             &lt;PROJECTION_SALES&gt;1552809.983&lt;/PROJECTION_SALES&gt;
                             &lt;PROJECTION_UNITS&gt;18695.75536&lt;PROJECTION_UNITS&gt;
                             &lt;ACCOUNT_GROWTH&gt;0&lt;/ACCOUNT_GROWTH&gt;
                             &lt;PRODUCT_GROWTH&gt;0&lt;/PRODUCT_GROWTH&gt;
                             &lt;/period_sid&gt;
                             &lt;period_sid period_sid=1802&gt;
                             &lt;PROJECTION_SALES&gt;1595642.121&lt;/PROJECTION_SALES&gt;
                             &lt;PROJECTION_UNITS&gt;18834.75725&lt;PROJECTION_UNITS&gt;
                             &lt;ACCOUNT_GROWTH&gt;0&lt;/ACCOUNT_GROWTH&gt;
                             &lt;PRODUCT_GROWTH&gt;0&lt;/PRODUCT_GROWTH&gt;
                             &lt;/period_sid&gt;
                             &lt;/period&gt;
  
  30002                      Same like above      
  </code></pre>
  
  <p>I am new to <code>XML</code> so couldn't able to do it quickly. I have used <a href="https://stackoverflow.com/questions/1564541/how-to-convert-records-in-a-table-to-xml-format-using-t-sql?rq=1">Marc_s</a> solution with <code>cross apply</code> but can't able to achieve it.</p>
  
  <p>Note: my major goal is, in above image if we see there are three records for single ccp_details_sid so I want to convert it as one row by using <code>XML</code> (mentioned above).</p>