我想设置一个处理指令,在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
更多推荐
发布评论