Sql Appending To Query Output

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

Solution 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(@ExistingXmlAS 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