T-SQL: экспорт таблицы в XML с использованием хранимых процедур - как отформатировать?

Question

Я использую SELECT ... FOR XML для генерации XML, и он создает именно то, что я хочу видеть - в SSMS. Но...

У меня проблема в том, что экспортированный XML-файл содержит все тело XML в одной строке. Это действительный XML, но как я могу отформатировать вывод? (один элемент в строке, оканчивающийся на \r\n и предпочтительно с отступом) Файл должен быть удобен для чтения в блокноте.

Кроме того, есть ли лучший способ использовать заголовок XML?

Конечно, я мог бы написать хранимую процедуру CLR для экспорта, хотя это кажется излишним. (Почему это даже сложно?)

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

Вы можете включить добавление заголовка, поместив извлечение XML в подзапрос. Таким образом, ваша команда BCP становится:

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 2

Чтобы замкнуть цикл, я нашел решение, которое записывает один тег XML в строку без отступа. Это отвратительный хак, но он подходит для моих целей, посмотрим, пройдет ли он проверку кода. Если кто-то хочет посмотреть на файл с отступом, он может открыть его в IE (дважды щелкнув по нему).

Конечно, реальным решением был бы хранимый CLR-процесс, но сейчас я предпочитаю этот способ, чтобы сохранить развертывание простым, то есть просто запустить один сценарий SQL для развертывания БД.

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

Answer 3

Так как вы в любом случае запускаете команды, вы можете:

xmllint --format

Из бесплатного инструмента XML Formatting