Unable to add condition in FOR XML part

2019-09-20 01:32发布

问题:

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],
                    '','

Then the generated XML is not correct

回答1:

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>

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],
       ''

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.