4

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>
1
  • It feels dirty so there is probably a better way, but as the text you want gone is constant you could search and replace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" to an empty string before stirring it anywhere. Commented Dec 15, 2014 at 11:38

1 Answer 1

5

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('')
1
  • I forgot to mention like this query is using in a dymanic sql.Like The query will be as "SELECT * FROM"+@tablename+for xml path('')
    – Jeena
    Commented Dec 15, 2014 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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