patternsqlMinor

Column with selective aliases

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withaliasesselectivecolumn

Problem

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 =40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE

The output should be:


   BUYER
   SELLER
   SELLER

Solution

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 = 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;

dbfiddle

Code Snippets

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

Context

StackExchange Database Administrators Q#177716, answer score: 5

Revisions (0)

No revisions yet.