Column with selective aliases I want to select an alias based on specific column criteria and output the required XML.

Say we have a table named ACCOUNTS:


Name       Age
---------  ---
John Doe   30
Mary Lane  40
Alice Bob  50


Query:

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT 
     CASE WHEN AGE &lt;= 30 THEN 'BUYER' AS [def:GIRL]
          WHEN AGE &gt;=40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE


The output should be:

 &lt;abc:OilCompany&gt;
   &lt;def:GIRL&gt;BUYER&lt;/def:GIRL&gt;
   &lt;def:BOY&gt;SELLER&lt;/def:BOY&gt;
   &lt;def:BOY&gt;SELLER&lt;/def:BOY&gt;
 &lt;/abc:OilCompany&gt;
By default the SQL Server does not create a node for NULL values so if you change your query to use two CASE statements and return NULL when there is no match you will get the XML you are looking for.

WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 
  'uri' AS def
)
SELECT CASE WHEN AGE &lt;= 30 THEN 'BUYER' END AS [def:GIRL],
       CASE WHEN AGE &gt;= 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;


dbfiddle