Add whitespace formatting to TSQL FOR XML data



I'm exporting data from joined SQL tables as XML then inserting it into a script that will be used to import it.


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


Here's an example that uses AdventureWorks2014 sample database:



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

No comments:

Post a Comment