Sql Server For Xml Path: Set Xml-declaration Or Processing Instruction "xml-stylesheet" On Top

I want to set a processing instruction to include a stylesheet on top of an XML: The same issue was with the xml-declaration (e.g. ) Des

Solution 1:

There is another way, which will need two steps but don't need you to treat the XML as string anywhere in the process :

declare @result XML =
(
    SELECT 
        'Test' AS Test,
        'SomeMore' AS SomeMore
    FOR XML PATH('TestPath')
)
set @result.modify('
    insert <?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>
    before /*[1]
')

Sqlfiddle Demo

The XQuery expression passed to modify() function tells SQL Server to insert the processing instruction node before the root element of the XML.

UPDATE :

Found another alternative based on the following thread : Merge the two xml fragments into one? . I personally prefer this way :

SELECT CONVERT(XML, '<?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>'),
(
    SELECT 
        'Test' AS Test,
        'SomeMore' AS SomeMore
    FOR XML PATH('TestPath')
)
FOR XML PATH('')

Sqlfiddle Demo

Solution 2:

As it came out, har07's great answer does not work with an XML-declaration. The only way I could find was this:

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

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

You must stay in the string line after this step, any conversion to real XML will either give an error (when the encoding is other then UTF-16) or will omit this xml-declaration.