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?

Community · Accepted Answer · 2017-05-23 12:31:35Z

3

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

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Nov 21, 2015 at 7:59

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Great input, thank you! Until they find something like top-processing-instruction() this is a very good work around!

Gottfried Lesigang Over a year ago

Hi har07, I just had to deal with adding the xml-declaration and thought, this could be done similarly, but it did not. I added an answer with the (pretty obvious) solution...

Gottfried Lesigang · Accepted Answer · 2020-03-02 09:05:06Z

0

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(@ExistingXml AS 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.

edited Mar 2, 2020 at 9:05

answered May 27, 2016 at 7:53

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related