title: SQL Server FOR XML PATH carriage return after each root node
body: <p>I am using FOR XML PATH in SQL Server 2014 to generate an XML file to send to one of our vendors.  Their system requires that each root node be separated by a carriage return / line break.  Here is the T-SQL code I'm using to generate it:</p>
  
  <pre><code>Declare @xmldata xml
  
  set @xmldata = 
  (SELECT a.StatementDate AS [stmt_date]
    ,a.CustomerID AS [student_id]
   ,'Upon Receipt' AS [due_date]
   ,a.TotalDue AS [curr_bal]
    ,a.TotalDue AS [total_due]
   ,a.AlternateID AS [alternate_id]
    ,a.FullName AS [student_name]
   ,a.Email AS [student_email]
   ,a.Addr1
   ,a.Addr2
    ,a.Msg AS [message]
    ,(
      SELECT b.StatementDate AS [activity_date]
            ,b.ActivityDesc AS [activity_desc]
            ,b.TermBalance AS [charge]
      FROM #ActivityXML AS b
      WHERE a.CustomerID = b.CustomerID
      ORDER BY a.StatementDate
      FOR XML PATH('activity'),TYPE
     ) 
  FROM #BillingStatement AS a
  FOR XML PATH('Billing'))
  
  select @xmldata as returnXml    
  </code></pre>
  
  <p>This works great, but returns one long string with no separation between nodes at all.  (I would post an example but it would just look like a jumbled up mess in here.)</p>
  
  <p>Anyhow, what we need is to generate a file where each <code>&lt;Billing&gt;</code> tag and contents within is placed on a new line after a closing <code>&lt;/Billing&gt;</code> tag.  I would guess there's a simple solution, such as inserting <code>char(13)+char(10)</code> somewhere in the code, but I've been unable to get that working.  Is it possible or will I need to do it in another system?</p>