SQL Server FOR XML PATH:设置xml声明或处理指令"xml-stylesheet"；在上面 [英] SQL Server FOR XML PATH: Set xml-declaration or processing instruction "xml-stylesheet" on top

问题描述

我想设置一条处理指令，以在XML顶部包含一个样式表:

I want to set a processing instruction to include a stylesheet on top of an XML:

xml声明(例如<?xml version="1.0" encoding="utf-8"?>)也存在相同的问题

The same issue was with the xml-declaration (e.g. <?xml version="1.0" encoding="utf-8"?>)

所需结果:

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

我的研究使我进入了节点测试语法和processing-instruction().

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

此

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

产生此:

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

我发现的所有提示都告诉我将XML转换为VARCHAR，手动"将其连接，然后将其转换回XML.但这是-怎么说-丑陋?

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?

这显然有效:

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?

推荐答案

还有另一种方法，它将需要两个步骤，但是不需要您在过程中的任何位置将XML视为字符串:

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

Sqlfiddle Demo

传递给modify()函数的XQuery表达式告诉SQL Server在XML的根元素之前插入处理指令节点.

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

更新:

根据以下线程找到了另一种替代方法:将两个xml片段合并为一个?.我个人更喜欢这种方式:

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

Sqlfiddle Demo

这篇关于SQL Server FOR XML PATH:设置xml声明或处理指令"xml-stylesheet"；在上面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！