Return empty or null fields as <element /> from SQL Server 2008 R2 with FOR XML

Question

I am running a query from SQL Server 2008 R2 using FOR XML PATH. My only issue is that I want ALL elements to appear, even if they are NULL and I want empty (or null) elements to return as

<MyElement />

Not as

<MyElement></MyElement>

Answer 1

You can query the field in a sub-query in the filed list, using for xml, create both versions of empty element.

declare @T table
(
  ID int identity primary key,
  Name nvarchar(10)
)

insert into @T(Name)
select 'Name 1' union all
select null union all
select 'Name 2'

select ID,
       (select Name as '*' for xml path(''), type) as Name,
       (select Name as '*' for xml path('Name'), type)
from @T
for xml path('row')

Result:

<row>
  <ID>1</ID>
  <Name>Name 1</Name>
  <Name>Name 1</Name>
</row>
<row>
  <ID>2</ID>
  <Name></Name>
  <Name />
</row>
<row>
  <ID>3</ID>
  <Name>Name 2</Name>
  <Name>Name 2</Name>
</row>

Answer 2

Have look at following Example

DECLARE @t TABLE (
    id INT, Name1 VARCHAR(20), 
    Value1 VARCHAR(20), Name2 VARCHAR(20), 
    Value2 VARCHAR(20))

INSERT INTO @t (id, name1, value1, name2, value2)
SELECT 1, 'PrimaryID', NULL, 'LastName', 'Abiola' UNION ALL
SELECT 2, 'PrimaryID', '200', 'LastName', 'Aboud'


SELECT
(
    SELECT 
        name1 AS 'Parameter/Name',
        value1 AS 'Parameter/Value'
    FROM @t t2 WHERE t2.id = t.id 
    FOR XML PATH(''), ELEMENTS XSINIL, TYPE
),
(
    SELECT 
        name2 AS 'Parameter/Name',
        value2 AS 'Parameter/Value'
    FROM @t t2 WHERE t2.id = t.id 
    FOR XML PATH(''), TYPE
)
FROM @t t
FOR XML PATH('T2Method')

You will get output like following

<T2Method>
  <Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Name>PrimaryID</Name>
    <Value xsi:nil="true" />
  </Parameter>
  <Parameter>
    <Name>LastName</Name>
    <Value>Abiola</Value>
  </Parameter>
</T2Method>

<T2Method>
  <Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Name>PrimaryID</Name>
    <Value>200</Value>
  </Parameter>
  <Parameter>
    <Name>LastName</Name>
    <Value>Aboud</Value>
  </Parameter>
</T2Method>

Note the "value" element in the first "paremeter" element. The value is NULL and still an element is generated. Note the addition of a special attribute "xsi:nil" to indicate that the element is empty.