Sql-server – Can FOR XML PATH return NULL for an empty resultset

sql serverxml

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

Best Answer

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.

Related Question