SQL Server FOR XML PATH: Set xml-declaration or processing instruction "xml-stylesheet" on top

Question

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. <?xml version="1.0" encoding="utf-8"?>)

Desired result:

<?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>
<TestPath>
  <Test>Test</Test>
  <SomeMore>SomeMore</SomeMore>
</TestPath>

My research brought me to node test syntax and processing-instruction().

This

SELECT 'type="text/xsl" href="stylesheet.xsl"' AS [processing-instruction(xml-stylesheet)]
      ,'Test' AS Test
      ,'SomeMore' AS SomeMore
FOR XML PATH('TestPath')

produces this:

<TestPath>
  <?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>
  <Test>Test</Test>
  <SomeMore>SomeMore</SomeMore>
</TestPath>

All hints I found tell me to convert the XML to VARCHAR, concatenate it "manually" and convert it back to XML. But this is - how to say - ugly?

This works obviously:

SELECT CAST(
'<?xml-stylesheet type="text/xsl" href="stylesheet.xsl"?>
<TestPath>
  <Test>Test</Test>
  <SomeMore>SomeMore</SomeMore>
</TestPath>' AS XML);

Is there a chance to solve this?

Answer 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