Azure SQL - Customize output of FOR XML PATH('EmployeeDetails')

Question

Hi,

I need to customize the output of "FOR XML PATH('EmployeeDetails')" in Azure SQL database

Lets say I have a table with data as below

Now when I run the query

SELECT EmployeeID, Name, Mobile,Address, Pin
FROM TestTable FOR XML PATH('EmployeeDetails'), TYPE;

I get below output which is as expected
<EmployeeDetails>
<EmployeeID>1</EmployeeID>
<Name>Partha</Name>
<Pin>BDC12</Pin>
</EmployeeDetails>

Since Mobile and Address column has no value those are not appearing in generated XML.

My requirement is to have output like

<EmployeeDetails>
<EmployeeID>1</EmployeeID>
<Name>Partha</Name>
<Address></Address>
<Pin>BDC12</Pin>
</EmployeeDetails>

Where though "Address" has no value in the table but must come with empty tag in the output.

Is it achievable -- Please help

Answer 1

Hi @Partha Das ,

Please try the following solution.
It is using COALESCE() function to achieve what you need.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (EmployeeID INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Mobile VARCHAR(15));  
INSERT INTO @tbl (Name, Mobile) VALUES  
('Parta', NULL),  
('Martha', '13058031818');  
-- DDL and sample data population, end  
  
SELECT EmployeeID  
	, Name  
	, COALESCE(Mobile, '') AS Mobile  
FROM @tbl  
FOR XML PATH('EmployeeDetails'), TYPE, ROOT('root');  

Output

<root>  
  <EmployeeDetails>  
    <EmployeeID>1</EmployeeID>  
    <Name>Parta</Name>  
    <Mobile></Mobile>  
  </EmployeeDetails>  
  <EmployeeDetails>  
    <EmployeeID>2</EmployeeID>  
    <Name>Martha</Name>  
    <Mobile>13058031818</Mobile>  
  </EmployeeDetails>  
</root>