SQL Server FOR XML PATH：在顶部设置xml

SQL Server FOR XML PATH：在顶部设置xml-declaration或处理指令“xml-stylesheet”(SQL Server FOR XML PATH: Set xml-declaration or processing instruction “xml-stylesheet” on top)

我想设置一个处理指令，在XML上包含一个样式表：

同样的问题是xml声明（例如<?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() 。

这个

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。 但这是 - 怎么说 - 难看？

这很明显：

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

有机会解决这个问题吗？

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?

最满意答案

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

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

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

更新：

基于以下线程找到另一种替代方法： 将两个xml片段合并为一个？ 。 我个人更喜欢这种方式：

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

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