SQL Server stamp on exporting FOR XML

Question

I am currently trying to export some data from SQL Server 2014 to a XML document. I've gotten help from here earlier on this project and I am very thankful for that.

At the moment the data is structured correctly and is as it should be, but unfortunately the server (Totalview server) that is picking up the XML document is very picky about it. SQL Server is adding a stamp on top of the document which looks like this:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

Because of this stamp in the XML document, the Totalview server cannot load the file. I have looked on google alot and Microsoft's help pages but can't find anything about this, maybe I'm using the wrong words or looking wrong places, which is why I am asking in here with you great guys.

What I want is for this stamp to be replaced by this stamp:

<?xml version="1.0"?>

It doesn't matter how it's done and I have thought of making some kind of script that will change this after SQL Server outputs the XML file but it would be nice to get SQL Server to output it correctly in the first place so there is fewer steps that could fail, but is it the only way?

Kind regards and thanks in advance, I am very sorry for any mistakes made in this question, I am still quite new to this site.

EDIT

SQL query as following:

SELECT
    [ctID],
    [forecastData/date/day] = Day(dDate),
    [forecastData/date/month] = month(dDate),
    [forecastData/date/year] = year(dDate),
    cast(([co_forecast]) as decimal(20,2)) AS [forecastData/dailyData/contactsReceived],
    cast(([AHT_Forecast]) as int) AS [forecastData/dailyData/averageAHT]
FROM 
    [ProductionForecast].[dbo].[vwForecastXMLDaily]
WHERE
    dDate BETWEEN cast(getdate() as date) AND cast(getdate()+31 as date)
GROUP BY
    [CTID], [dDate], [co_forecast], [AHT_Forecast]
FOR XML PATH ('ctForecast'), ROOT ('forecastImport')

Table structure is as following:

CTID    dDate   CO_Forecast AHT_Forecast
2   2016-01-15  167.75515   419.042510
2   2016-01-16  0.00000     0.000000
2   2016-01-17  0.00000     0.000000
2   2016-01-18  246.15381   382.407540
2   2016-01-19  238.35609   379.404340
2   2016-01-20  227.36992   444.473690
2   2016-01-21  232.43770   424.452350
2   2016-01-22  203.65597   403.429950
2   2016-01-23  0.00000     0.000000
2   2016-01-24  0.00000     0.000000

Answer 1

You could try something like this. Keeping in mind that your exported XML file needs a root element and you need permissions to execute xp_cmdshell. Thanks to How To Save XML Query Results to a File

DECLARE @xml AS XML = (SELECT * FROM Table FOR XML PATH, ROOT('MyRoot')
)

-- Cast the XML variable into a VARCHAR
DECLARE @xmlChar AS VARCHAR(max) = CAST(@xml AS VARCHAR(max))

-- Escape the < and > characters
SET @xmlChar = REPLACE(REPLACE(@xmlChar, '>', '^>'), '<', '^<')

-- Create command text to echo to file
DECLARE @command VARCHAR(8000) = 'echo ' + @xmlChar + ' > c:\test.xml'

-- Execute the command
EXEC xp_cmdshell @command

Answer 2

Up to now there is no trick to add a outsided processing instruction

I asked as similar question here: SQL Server FOR XML PATH: Set xml-declaration or processing instruction "xml-stylesheet" on top

Just play around with these examples:

--If you create XML like this, a column header is created:
SELECT TOP 5 *
FROM sys.objects
FOR XML PATH('');

--You get the same result, but with your own column name (or even without, if you leave it away)
SELECT 
    (
    SELECT TOP 5 *
    FROM sys.objects
    FOR XML PATH(''),TYPE
    ) AS abcd

--without the ",TYPE" the result is no XML anymore but simple string
SELECT 
    (
    SELECT TOP 5 *
    FROM sys.objects
    FOR XML PATH('') 
    );

--you might just add your prefix with the XML 
--In this case there is no column name generated
SELECT '<?xml version="1.0"?>'+
    (
    SELECT TOP 5 *
    FROM sys.objects
    FOR XML PATH('')
    );

Now - having got that far - your solution might be something like this:

SELECT '<?xml version="1.0"?>'+
    (
    SELECT
     [ctID]
    ,[forecastData/date/day] = Day(dDate) 
    ,[forecastData/date/month] = month(dDate)
    ,[forecastData/date/year] = year(dDate)
    ,cast(([co_forecast]) as decimal(20,2)) AS [forecastData/dailyData/contactsReceived]
    ,cast(([AHT_Forecast]) as int) AS [forecastData/dailyData/averageAHT]

    FROM [ProductionForecast].[dbo].[vwForecastXMLDaily]
    where dDate between cast(getdate() as date) and cast(getdate()+31 as date)

    group by [CTID], [dDate], [co_forecast], [AHT_Forecast]

    FOR XML PATH ('ctForecast'), ROOT ('forecastImport')
    );

Answer 3

The answer to this question, is to disable column header when exporting to a file. In my case i am using Microsoft SQL 2014, so i went in to Tools->Options->Query Results->Results to Text, then remove the tick in 'Include Column headers in the result set'

Even though the Totalview server still wont accept the output file, it did remove the stamp/column header so my question here is solved.

Thank you all for your help.