SQL appending <?xml version="1.0" encoding="UTF-8"?> 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

Possible duplicate of SQL Server FOR XML PATH: Set processing instruction "xml-stylesheet" on top — Gottfried Lesigang
– Gottfried Lesigang, Commented May 25, 2016 at 14:12
I doubt this is correctly tagged. Does MySQL support FOR XML PATH ???. The given syntax and the bcp points to SQL Server... — Gottfried Lesigang
– Gottfried Lesigang, Commented May 25, 2016 at 14:29

Gottfried Lesigang · Accepted Answer · 2016-05-31 11:46:44Z

2

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

edited May 31, 2016 at 11:46

answered May 25, 2016 at 14:25

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

JJonson Over a year ago

Wouldn't your example add the encodig to every element ? I only want it appended in the beginning. How would it look in the code i pasted?

Gottfried Lesigang Over a year ago

@user6381438 did you read the link I posted below your question as possible duplicate?

JJonson Over a year ago

Yes i looked at the link but i'm not sure how to go about it with the code i already put together?

JJonson Over a year ago

First of all, no need to apologize for the late response and the hint. I really appreciate the time you've spent just trying to be helpful :) @Shnugo Thanks Shnugo ! that seemed to do the trick.. almost :) The hyphens are not carried over in the output. so instead of <?xml version="1.0" encoding="UTF-8"?> output is <?xml version=1.0 encoding=UTF-8?>

Gottfried Lesigang Over a year ago

Hi @user6381438, according to this BCP wants the inner double quotes doubled: <?xml version=""1.0"" encoding=""UTF-8""?>

|

Collectives™ on Stack Overflow

SQL appending <?xml version="1.0" encoding="UTF-8"?> to Query output

1 Answer 1

UPDATE

Edit: doubled quotes. See comments.

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

Edit: doubled quotes. See comments.

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related