title: Add whitespace formatting to TSQL FOR XML data
body: <p>I'm exporting data from joined SQL tables as XML then inserting it into a script that will be used to import it.</p>
  
  <p>I would like the XML to be split across lines and indented in the output script. How do I add whitespace formatting to the XML generated with FOR XML</p>
  
  <p>Here's an example that uses AdventureWorks2014 sample database:</p>
  
  <pre><code>DECLARE @XmlData xml = (
      SELECT e.BusinessEntityID, e.HireDate, e.JobTitle, c.FirstName, c.MiddleName, c.LastName
      FROM HumanResources.Employee e 
          INNER JOIN Person.Person c
         ON c.BusinessEntityID = e.BusinessEntityID
      WHERE c.FirstName = 'Rob'
      FOR XML AUTO, ROOT);
  
  DECLARE @ScriptTemplate nvarchar(max) = '
  -- This is my import script
  -- blah, blah, blah
  
  DECLARE @SolutionXml as xml = ''
  ##REPLACE_THIS##
  '';
  
  DECLARE @SolutionXmlHandle int;
  EXEC sp_xml_preparedocument @SolutionXmlHandle OUTPUT, @SolutionXml;
  
  -- blah, blah, blah
  ';
  
  DECLARE @myScript nvarchar(max) = REPLACE (@ScriptTemplate, '##REPLACE_THIS##', CONVERT(nvarchar(max), @XmlData, 1))
  
  SELECT @XmlData XmlData, @ScriptTemplate ScriptTemplate, @myScript myScript
  </code></pre>