none
how to select more than one column using FOR XML?

    Question

  • im trying get comma separate list using the following function. it works if i select column but im trying to select two columns and it is not working.

    any idea how do i select more than one column?

    SELECT
    			T.ID, 			
    			SUBSTRING(l.LegalEntityNames,1,len(LegalEntityNames)-1)	AS 'LegalEntityNames',			
    			SUBSTRING(l.PropertyNames,1,len(PropertyNames)-1)	AS 'PropertyNames',						
    		FROM @Temp T
    		OUTER APPLY(
    			SELECT 
    				T1.TMPClientName + ',',				
    				T1.TMPPropertyName + ',',				
    			FROM @Temp T1 
    			WHERE T1.ID = T.ID
    			FOR XML PATH('')) AS l(LegalEntityNames)
    		GROUP BY l.LegalEntityNames

    Monday, March 18, 2013 9:25 PM

Answers

  • You will need two separate OUTER APPLY and FOR XML PATH('') statements for each of the columns separately.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by lax4u Friday, March 22, 2013 6:56 PM
    Monday, March 18, 2013 9:49 PM
    Moderator

All replies

  • You will need two separate OUTER APPLY and FOR XML PATH('') statements for each of the columns separately.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by lax4u Friday, March 22, 2013 6:56 PM
    Monday, March 18, 2013 9:49 PM
    Moderator
  • This is not possible within a single FOR XML query. You may use a second OUTER APPLY query and ORDER BY TMPClientName ASC, TMPPropertyName ASC on both sub-queries.

    Monday, March 18, 2013 9:54 PM