T-SQL: exporting table to XML using stored proc - how to format?

Question

I'm using SELECT ... FOR XML to generate XML and it creates exactly what I want to see - in SSMS. But...

The problem I have is, the exported XML file has the entire XML body on a single line. It's valid XML, but how can I format the output? (one element per line, terminated with \r\n and preferably indented) The file should be human readable in notepad.

Also, is there a better way to tack on the XML header?

Of course I could write a CLR stored proc to do the export, seems like overkill though. (Why is this even hard?)

ALTER PROCEDURE [dbo].[ExportConfigTablesToXML] 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--EXEC sp_configure 'show advanced options', 1
--RECONFIGURE
--EXEC sp_configure 'xp_cmdshell', 1
--RECONFIGURE
--EXEC sp_configure 'show advanced options', 0
--RECONFIGURE

DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd   VARCHAR(500)
DECLARE @CreateXmlHeader varchar(500)
DECLARE @AppendXmlBody varchar(500)
DECLARE @DeleteTempFile varchar(500)

SELECT @FileName = 'C:\Temp\SampleXMLOutput.xml'
SELECT @SQLCmd = 'bcp ' + 
    '"SELECT Name AS [@Name], ServiceName, MachineName,' +
    '    CASE PollInterval WHEN 10 THEN NULL ELSE PollInterval END AS PollInterval,' +
    '    CASE Alerts WHEN 1 THEN NULL ELSE ''false'' END AS Alerts, ' +
    '    CASE Pages WHEN 1 THEN NULL ELSE ''false'' END AS Pages' +
    ' FROM Watchdog.dbo.[Config.Services] [Service]' +
    ' FOR XML PATH(''Service''), ROOT(''Services'')"' +
    ' queryout ' +
    @FileName + '.tmp' +
    ' -S' + @@SERVERNAME +
    ' -T -c -r -t'

SET @CreateXmlHeader = 'ECHO ^<?xml version="1.0" ?^> > ' + @FileName
SET @AppendXmlBody = 'TYPE ' + @FileName + '.tmp >> ' + @FileName
SET @DeleteTempFile = 'DEL ' + @FileName + '.tmp'

EXECUTE master..xp_cmdshell @SQLCmd
EXECUTE master..xp_cmdshell @CreateXmlHeader
EXECUTE master..xp_cmdshell @AppendXmlBody
EXECUTE master..xp_cmdshell @DeleteTempFile

END

Answer 1

Since you're running commands anyway, you could perhaps:

xmllint --format

From http://stackoverflow.com/questions/193728/free-xml-formatting-tool/193745#193745

Answer 2

You could include the addition of the header by putting the XML extract into a sub-query. So your BCP command becomes:

SELECT @SQLCmd = 'bcp ' +  
    '"SELECT ''<?xml version="1.0"?>'' + ' +
    '(SELECT Name AS [@Name], ServiceName, MachineName,' + 
    '    CASE PollInterval WHEN 10 THEN NULL ELSE PollInterval END AS PollInterval,' + 
    '    CASE Alerts WHEN 1 THEN NULL ELSE ''false'' END AS Alerts, ' + 
    '    CASE Pages WHEN 1 THEN NULL ELSE ''false'' END AS Pages' + 
    ' FROM Watchdog.dbo.[Config.Services] [Service]' + 
    ' FOR XML PATH(''Service''), ROOT(''Services''))"' + 
    ' queryout ' + 
    @FileName + '.tmp' + 
    ' -S' + @@SERVERNAME + 
    ' -T -c -r -t' 

Answer 3

To close the loop, I did find a solution that writes the XML one tag per line with no indenting. It's a disgusting hack but it's acceptable for my purposes, let's see if it passes code review. If someone wants to look at the file with indenting they can open it in IE (by double clicking it).

Of course the real solution would be a CLR stored proc, but for now I prefer it this way to keep the deployment simple, i.e. just run a single SQL script to deploy the DB.

ALTER PROCEDURE [dbo].[ExportConfigTablesToXML] 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--EXEC sp_configure 'show advanced options', 1
--RECONFIGURE
--EXEC sp_configure 'xp_cmdshell', 1
--RECONFIGURE
--EXEC sp_configure 'show advanced options', 0
--RECONFIGURE

DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd   VARCHAR(1000)

SELECT @FileName = 'C:\Temp\SampleXMLOutput.xml'
SELECT @SQLCmd = 'bcp ' + 
    '"DECLARE @xml xml; ' +
    'DECLARE @text varchar(MAX); ' +

    'SET @xml = (SELECT Name AS [@Name], ServiceName, MachineName, ' +
    '   CASE PollInterval WHEN 10 THEN NULL ELSE PollInterval END AS PollInterval, ' +
    '   CASE Alerts WHEN 1 THEN NULL ELSE ''false'' END AS Alerts, ' +
    '   CASE Pages WHEN 1 THEN NULL ELSE ''false'' END AS Pages ' +
    'FROM Watchdog.dbo.[Config.Services] [Service] ' +
    'FOR XML PATH(''Service''), ROOT(''Services''), TYPE); ' +

    'SET @text = ''<?xml version=""1.0"" ?>'' + CHAR(13) + CHAR(10); ' +
    'SET @text = @text + REPLACE(CAST(@xml AS varchar(MAX)), ''><'', ''>'' + CHAR(13) + CHAR(10) + ''<''); ' +
    'SELECT @text" ' +

    ' queryout ' +
    @FileName + 
    ' -S' + @@SERVERNAME +
    ' -T -c -r -t'

EXECUTE master..xp_cmdshell @SQLCmd

END