SQL appending to Query output

Question

So i'm having a bit of trouble to figure out how i could make this work so that i can append the xml encoding to my query.

this is what i hacked together so far:

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

SELECT  @FileName = 'C:\SampleXMLOutput.xml'

-- in this command, we are making sure there is only one ROOT node


SELECT @SQLCmd = + 'bcp ' + 
                         '"SELECT Id, Initials, firstname, lastname, email ' +
                         ' FROM Employees.dbo.IDCards ' +
                         ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees''), TYPE "' +
                         ' queryout '  +
                   @FileName +
                   ' -w -T -S' + @@SERVERNAME



-- display command, for visual  check
SELECT @SQLCmd AS 'Command to execute'

-- create the XML file
EXECUTE master..xp_cmdshell @SQLCmd

Also i need to get some data from another table within the same query output.

Any help appreciated

Answer 1

I assume - due to the syntax - that you are using SQL Server...

With FOR XML PATH it is possible to create a processing instruction like this

SELECT 'test' AS OneNormalElement
      ,'version="1.0" encoding="UTF-8"' AS [processing-instruction(abc)]
FOR XML PATH('Test')

The result

<Test>
  <OneNormalElement>test</OneNormalElement>
  <?abc version="1.0" encoding="UTF-8"?>
</Test>

But you are not allowed to use AS [processing-instruction(xml)]. You get the error:

Error: 6879, Severity: 16, ‘xml’ is an invalid XML processing instruction target. Possible attempt to construct XML declaration using XML processing instruction constructor. XML declaration construction with FOR XML is not supported.

It is actually not supported to create any PI outside of your XML...

The question I've linked as "duplicate" shows some workarounds how to add this anyhow...

UPDATE

I must apologize for the late response as well as for the wrong hint. The xml-declaration is handled differently, so the linked answer did not help actually. I updated this also...

The only way I found to add an XML-declaration is string concatenation:

DECLARE @ExistingXML XML=
(
    SELECT 
        'Test' AS Test,
        'SomeMore' AS SomeMore
    FOR XML PATH('TestPath'),TYPE
);

DECLARE @XmlWithDeclaration NVARCHAR(MAX)=
(
    SELECT '<?xml version="1.0" encoding="UTF-8"?>'
           +
           CAST(@ExistingXml AS NVARCHAR(MAX))
);
SELECT @XmlWithDeclaration;

And if you want to do it in one single call, you must do this without ,TPYE

DECLARE @XmlWithDeclaration NVARCHAR(MAX)=
(
    SELECT '<?xml version="1.0" encoding="UTF-8"?>'
           +
            (
            SELECT 
                'Test' AS Test,
                'SomeMore' AS SomeMore
            FOR XML PATH('TestPath')
            )
);
SELECT @XmlWithDeclaration;

So - finally - I hope this is your solution:

Edit: doubled quotes. See comments.

SELECT @SQLCmd = + 'bcp ' + 
                     '"SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 
                     ' (SELECT Id, Initials, firstname, lastname, email ' +
                     '  FROM Employees.dbo.IDCards ' +
                     '  FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                     ' queryout '  +
               @FileName +
               ' -w -T -S' + @@SERVERNAME