无法在FOR XML部分中添加条件 [英] Unable to add condition in FOR XML part

问题描述

在此帮助下，我已经生成了FOR XML 链接. 在解决方案中有一个条件

I have generated a FOR XML with the help from this Link. In the solution there is a condition

SELECT   
                    'String' as [Cell/Data/@ss:Type],
                    [Col1] as [Cell/Data],
                    '',
                    'String' as [Cell/Data/@ss:Type],
                    [Col2] as [Cell/Data],
                    '',

我想在此部分添加一个条件 其中

I want to add a condition to this part which says

SELECT      
                CASE 
                    WHEN COL1 LIKE '%SUP%'
                    THEN
                    'String' as [Cell/ss:Data/@ss:Type]
                    ELSE 
                    'String' as [Cell/Data/@ss:Type]
                    END ,
                [Col1] as [Cell/Data],
                '',

但是我遇到了问题. 请帮助我.

But i am getting issues. Kindly help me with this.

上面的此基本代码显示语法错误. 如果我添加

This basic code above showing the Syntax error. If i add

SELECT      
                    CASE 
                        WHEN COL1 LIKE '%SUP%'
                        THEN
                        (SELECT 'String' as [Cell/ss:Data/@ss:Type])
                        ELSE 
                        (SELECT 'String' as [Cell/Data/@ss:Type])
                        END ,
                    [Col1] as [Cell/Data],
                    '','

然后生成的XML不正确

Then the generated XML is not correct

推荐答案

如果我理解您的意思，那么如果Col1包含字符串"SUP"，则需要在最终XML中使用此标记:

If I understand what you're saying, then if Col1 contains the string "SUP", you want this tag in your final XML:

<ss:Data ss:Type="String">SomethingContainingSup</ss:Data>

否则，您需要此表达式:

Otherwise, you want this expression:

<Data ss:Type="String">SomethingElse</Data>

我认为您可以通过以下SQL片段来实现:

I think you can get that with this SQL fragment:

SELECT CASE WHEN COL1 LIKE '%SUP%' THEN 'String' END  as [Cell/ss:Data/@ss:Type],
       CASE WHEN Col1 Like '%SUP%' THEN Col1 END as [Cell/ss:Data], 
       CASE WHEN COL1 NOT LIKE '%SUP%' THEN 'String' END  as [Cell/Data/@ss:Type],                    
       CASE WHEN Col1 NOT Like '%SUP%' THEN Col1  END as [Cell/Data],
       ''

为什么不起作用:您不能将列(或XML元素)的名称设置为条件名称.您可以获得的最接近的结果是使一个表达式或另一个表达式的计算结果为null，在这种情况下，它将不会在最终的XML中呈现.

Why yours didn't work: you can't make the name of a column (or of an XML element) conditional. The closest you can get is to make one or the other expression evaluate to null, in which case it won't be rendered in the final XML.

这篇关于无法在FOR XML部分中添加条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！