Column with selective aliases

Question

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

The output should be:

 <abc:OilCompany>
   <def:GIRL>BUYER</def:GIRL>
   <def:BOY>SELLER</def:BOY>
   <def:BOY>SELLER</def:BOY>
 </abc:OilCompany>

Answer 1

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 <= 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;

dbfiddle

Answer 2

Same column with a different name is invalid SQL, you need to select both conditions as separate columns:

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

Unfortunely this solution pushses the xml namespaces into every element.

The best way to avoid this, as per this anwser https://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path seems to be stepping in to the not so wonderful world of XML EXPLICIT

Leaving us with:

SELECT
    1 AS Tag
    ,NULL AS Parent
    ,'http://www.w3.org/2001/XMLSchema-instance' AS [abc:OilCompany!1!xmlns:abc]
    ,'uri' AS [abc:OilCompany!1!xmlns:def]
    ,NULL as [def:GIRL!2]
    ,NULL as [def:BOY!3]
  UNION ALL
  SELECT 
      2 as Tag
     ,1 as Parent
     ,NULL 
     ,NULL 
     ,'BUYER' as [def:GIRL!2]
     ,NULL
  FROM
    ACCOUNTS
  WHERE AGE <= 30
  UNION ALL
  SELECT
     3 as Tag
    ,1 as Parent
    ,NULL 
    ,NULL 
    ,NULL
    ,'SELLER' as [def:BOY!3]
  FROM
    ACCOUNTS
  WHERE AGE >= 40
FOR XML EXPLICIT

Both of these solutions also cause 2 scans on the same table - simplified in explicit:

SELECT
  1 AS Tag
  ,NULL AS Parent
  ,'http://www.w3.org/2001/XMLSchema-instance' AS [abc:OilCompany!1!xmlns:abc]
  ,'uri' AS [abc:OilCompany!1!xmlns:def]
  ,NULL as [def:GIRL!2]
  ,NULL as [def:BOY!3]
UNION ALL
SELECT 
  CASE WHEN AGE <= 30 THEN 2 
       WHEN AGE >= 40 THEN 3 END as Tag
  ,1 as Parent
  ,NULL 
  ,NULL 
  ,CASE WHEN AGE <= 30 THEN  'BUYER' ELSE NULL END as [def:GIRL!2]
  ,CASE WHEN AGE >= 40 THEN 'SELLER' ELSE NULL END as [def:BOY!3]
 FROM
   ACCOUNTS
 WHERE AGE <=30 OR AGE >=40 -- required if there are other rows in the source table
FOR XML EXPLICIT

Answer 3

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
cast(replace(cast(SELECT 
(
  SELECT 'BUYER'
  FROM
  ACCOUNTS a
  WHERE AGE <=30
  FOR XML PATH ('def:GIRL'),TYPE
) as varchar(1000)),'http://www.w3.org/2001/XMLSchema-instance','') as xml)   ,
( SELECT 'SELLER'
  FROM
  ACCOUNTS
  WHERE AGE  >=40
  FOR XML PATH ('def:BOY'), TYPE
)
FOR XML PATH ('abc:OilCompany'),TYPE