sql-serverxmlfor-xml-pathprocessing-instructionxml-declaration

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. <?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?


Solution

  • 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