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

Answer 1

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

Answer 2

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.