title: How do I convert SQL to XML?
body: <p>I am trying to output SQL as XML to match the exact format as the following </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;ProrateImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.aldi-
  sued.com/Logistics/Shipping/ProrateImport/20151009"&gt;
    &lt;Prorates&gt;
      &lt;Prorate&gt;
        &lt;OrderTypeId&gt;1&lt;/OrderTypeId&gt;
        &lt;DeliveryDate&gt;2015-10-12T00:00:00+02:00&lt;/DeliveryDate&gt;
        &lt;DivNo&gt;632&lt;/DivNo&gt;
        &lt;ProrateUnit&gt;1&lt;/ProrateUnit&gt;
        &lt;ProrateProducts&gt;
        &lt;ProrateProduct ProductCode="8467"&gt;
            &lt;ProrateItems&gt;
              &lt;ProrateItem StoreNo="1"&gt;
                &lt;Quantity&gt;5&lt;/Quantity&gt;
              &lt;/ProrateItem&gt;
              &lt;ProrateItem StoreNo="2"&gt;
                &lt;Quantity&gt;5&lt;/Quantity&gt;
              &lt;/ProrateItem&gt;
              &lt;ProrateItem StoreNo="3"&gt;
                &lt;Quantity&gt;5&lt;/Quantity&gt;
              &lt;/ProrateItem&gt;
            &lt;/ProrateItems&gt;
          &lt;/ProrateProduct&gt;
        &lt;/ProrateProducts&gt;
      &lt;/Prorate&gt;
    &lt;/Prorates&gt;
  &lt;/ProrateImport&gt;
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT 
      OrderTypeID,
      DeliveryDate, DivNo,
      ProrateUnit,
      (SELECT  
           ProductOrder [@ProductCode],
           (SELECT 
                ProrateItem [@StoreNo],
                CAST(Quantity AS INT) [Quantity]
            FROM 
                ##Result2 T3
            WHERE
                T3.DivNo = T2.DivNo 
                AND T3.DivNo = T1.DivNo 
                AND T3.DeliveryDate = T2.DeliveryDate 
                AND T3.DeliveryDate = T1.DeliveryDate
                AND T3.ProductOrder = t2.ProductOrder
            FOR XML PATH('ProrateItem'), TYPE, ROOT('ProrateItems')
           )
       FROM 
           ##Result2 T2
       WHERE
           T2.DivNo = T1.DivNo 
           AND T2.DeliveryDate = T1.DeliveryDate
       FOR XML PATH('ProrateProduct'), TYPE, ROOT('ProrateProducts')
      )
  FROM 
      ##Result2 T1
  GROUP BY 
      OrderTypeID, DeliveryDate, DivNo, ProrateUnit
  FOR XML PATH('Prorate'), TYPE, ROOT('Prorates')
  </code></pre>
  
  <p>How do I add in the Following and have the ProrateImport/20151009" change to the current date?</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;ProrateImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.aldi-
  sued.com/Logistics/Shipping/ProrateImport/20151009"&gt;
  </code></pre>
  
  <p>This is my first time I have used XML</p>