Sql-server – for xml missing null columns

sql serverxml

When we use for xml clause to create xml from a row like as follows

select * from Menus where MenuID=100 for xml path('')

The xml will miss the columns which have NULL as the value.

I have a found method like 

select * from Menus where MenuID=100  for xml path('') , ELEMENTS XSINIL

But the xml will create as follows only 

<MenuID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">100</MenuID>
<MenuOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1</MenuOrder>

Is there any way to create like as follows with NULL columns

<MenuID>100</MenuID>
<MenuOrder>1</MenuOrder>

Best Answer

If you don't want to use xsi:nil you have to decide what value you want to have in the XML when the value in the table is null. For strings it might be appropriate with an empty string. For integers you might want to use 0 or -1 or perhaps convert the column data to a string first and use an empty string for integers as well. You have to decide what value you want and use isnull and cast to get the output you want.

Example:

declare @T table
(
  MenuID int,
  MenuOrder int,
  MenuName varchar(10)
)

insert into @T default values

select -- 0 instead of null
       isnull(MenuID, 0) as MenuID, 
       -- empty string instead of null
       isnull(cast(MenuOrder as varchar(11)), '') as MenuOrder,  
       -- The text value NULL instead of null
       isnull(MenuName, 'NULL') as MenuName  
from @T as T
for xml path('')

Result:

<MenuID>0</MenuID>
<MenuOrder></MenuOrder>
<MenuName>NULL</MenuName>

If you want to do this dynamically where you have the table name as parameter and you decide that empty string is the way to go regardless of data type you can do something like this.

-- Table parameter
declare @TableName varchar(128) = 'Menus';
-- Column to use in where clause
declare @ColumnName varchar(128) = 'MenuID';
-- Value to filter by
declare @ColumnValue int = 100;

declare @S nvarchar(max);

set @S = '
select '+
  stuff((
        select ', isnull(cast('+quotename(C.name)+' as nvarchar(max)), '''') as '+quotename(C.name)
        from sys.columns as C 
        where C.object_id = object_id(@TableName)
        for xml path('')
        ), 1, 2, '')+'
from '+quotename(@TableName)+'
where '+quotename(@ColumnName)+' = @ColumnValue
for xml path('''')';

exec sp_executesql @S, N'@ColumnValue int', @ColumnValue;

You obviously need to parameterize what column and value you want to use as well if the table name is a parameter.

The dynamically generated query:

select isnull(cast([MenuID] as nvarchar(max)), '') as [MenuID], isnull(cast([MenuOrder] as nvarchar(max)), '') as [MenuOrder]
from [Menus]
where [MenuID] = @ColumnValue
for xml path('')
Related Question