4

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>
5

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

3

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
  • Thanks for the response. I will get back to you when i'm done testing. – Raidenlee Jul 3 '17 at 18:32
  • While i was able to test the scenario i didn't want to change the query xml type. I will upvote based on the alternative method. – Raidenlee Jul 11 '17 at 18:23
-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
  • 2
    Has this been tested? Wouldn't you need a SELECT after the WITH clause, but before the CAST? – RDFozz Jul 23 '18 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.