Can FOR XML PATH return NULL for an empty resultset?

Question

I would think this query

select null for xml path('foo')

would return NULL, but actuality

<foo/ >

is returned. Is there any way to achieve this behavior?

To clarify, the behavior I would expect to see is demonstrated in this example

SELECT
'foo' WHERE 1=0
for xml path('Outer'), type

This returns a different result and I'm not understanding why.

SELECT
(SELECT 'foo' WHERE 1=0)
for xml path('Outer'), type

My real world query is an xml subquery, but the example is valid either way.

SELECT
(SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),type)
for xml path('Outer'), type

This query works in the simplest case, but does not allow adding attributes to the root element, which is required in my case.

SELECT 'foo' WHERE 1=0 FOR XML PATH ('Inner'),root('outer'),type

Answer 1

Check out the value(method). From MSDN:

You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

Code:

DECLARE @variable_xml XML
DECLARE @variable_varchar VARCHAR(20)
SET @variable_xml = 'select NULL for xml path(''foo'')'
SET @variable_varchar = @variable_xml.value('(/Root)[1]',
                           'varchar')
SELECT  @variable_varchar

Output: NULL

EDIT:

I may have misunderstood the question. Please comment and I will update or delete my answer if its incorrect.

Answer 2

ISNULL Quick Answer

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

This means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

---

ELEMENTS XSINIL

Consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic. This may work for your need as well.

SELECT ISNULL(NULL, 'NULL')
FOR XML PATH('foo')
    ,TYPE
    ,ELEMENTS XSINIL

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

The ELEMENTS directive constructs XML in which each column value maps to an element in the XML. If the column value is NULL, no element is added. By specifying the optional XSINIL parameter on the ELEMENTS directive, you can request that an element also be created for the NULL value. In this case, an element that has the xsi:nil attribute set to TRUE is returned for each NULL column value.

Answer 3

XML treats null as an empty string. You could try WHERE 1=2 so that there are no rows.

Answer 4

Ok, you can do this way

select
(
    select
    (select 'foo' for xml path ('Inner'), type)
    , (select 'foo' for xml path ('Inner2'), type)
    for xml path (''), type
)
for xml path ('Outer')

which gives you next XML

<Outer>
  <Inner>foo</Inner>
  <Inner2>foo</Inner2>
</Outer>

and in the case with null values

select
(
    select
    (select null for xml path ('Inner'), type)
    , (select null for xml path ('Inner2'), type)
    for xml path (''), type
)
for xml path ('Outer')

it returns "unwanted" empty inner elements

<Outer>
  <Inner />
  <Inner2 />
</Outer>

OR!!! you can do this way

select
(
    select
    (select 'foo' [Inner] for xml path (''), type)
    , (select 'foo' [Inner2] for xml path (''), type)
    for xml path (''), type
)
for xml path ('Outer')

with the same results when there are non NULL values

<Outer>
  <Inner>foo</Inner>
  <Inner2>foo</Inner2>
</Outer>

BUT with no "unwanted" inner elements when the values are NULL

select
(
    select
    (select null [Inner] for xml path (''), type)
    , (select null [Inner2] for xml path (''), type)
    for xml path (''), type
)
for xml path ('Outer')

result

<Outer></Outer>