Skip to main content
3 of 3
Extra Example
Jeff Sacksteder
  • 1.3k
  • 2
  • 19
  • 30

Can FOR XML PATH return NULL for an empty resultset?

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
Jeff Sacksteder
  • 1.3k
  • 2
  • 19
  • 30