For xml path returns null instead of nothing

Question

I thought that following query suppose to return nothing, but, instead, it returns one record with a column containing null:

select *
from ( select 1 as "data"
       where 0 = 1
       for xml path('row') ) as fxpr(xmlcol)

If you run just the subquery - nothing is returned, but when this subquery has an outer query, performing a select on it, null is returned.

Why is that happening?

Answer 1

SQL Server will try to predict the type. Look at this

SELECT tbl.[IsThereAType?] + '_test'
      ,tbl.ThisIsINT       + 100
FROM
(
    SELECT NULL AS [IsThereAType?]
          ,3 AS ThisIsINT
    UNION ALL 
    SELECT 'abc'
          ,NULL
    --UNION ALL 
    --SELECT 1  
    --    ,NULL
) AS tbl;

The first column will be predicted as string type, while the second is taken as INT. That's why the + operator on top works. Try to add a number to the first or a string to the second. This will fail.

Try to uncomment the last block and it will fail too.

The prediction is done at a very early stage. Look at this, where I did include the third UNION ALL (invalid query, breaking the type):

EXEC sp_describe_first_result_set 
N'SELECT *
FROM
(
    SELECT NULL AS [IsThereAType?]
          ,3 AS ThisIsINT
    UNION ALL 
    SELECT ''abc''
          ,NULL
    UNION ALL 
    SELECT 1
          ,NULL

) AS tbl';

The result returns "IsThereAType?" as INT! (I'm pretty sure this is rather random and might be different on your system.)
Btw: Without this last block the type is VARCHAR(3)...

Now to your question

A naked XML is taken as NTEXT (altough this is deprecated!) and needs ,TYPE to be predicted as XML:

EXEC sp_describe_first_result_set N'SELECT ''blah'' FOR XML PATH(''blub'')';
EXEC sp_describe_first_result_set N'SELECT ''blah'' FOR XML PATH(''blub''),TYPE';

The same wrapped within a sub-select returns as NVARCHAR(MAX) resp. XML

EXEC sp_describe_first_result_set N'SELECT * FROM(SELECT ''blah'' FOR XML PATH(''blub'')) AS x(y)';
EXEC sp_describe_first_result_set N'SELECT * FROM(SELECT ''blah'' FOR XML PATH(''blub''),TYPE) AS x(y)';

Well, this is a bit weird actually... An XML is a scalar value taken as NTEXT, NVARCHAR(MAX) or XML (depending on the way you are calling it). But it is not allowed to place a naked scalar in a sub-select:

SELECT * FROM('blah') AS x(y) --fails

While this is okay

SELECT * FROM(SELECT 'blah') AS x(y)    

Conclusio:

The query parser seems to be slightly inconsistent in your special case:

Although a sub-select cannot consist of one scalar value only, the SELECT ... FOR XML (which returs a scalar actually) is not rejected. The engine seems to interpret this as a SELECT returning a scalar value. And this is perfectly okay.

This is usefull with nested sub-selects as a column (correlated sub-queries) to nest XML:

SELECT TOP 5 t.TABLE_NAME
            ,(
              SELECT COLUMN_NAME,DATA_TYPE 
              FROM INFORMATION_SCHEMA.COLUMNS AS c
              WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA
                AND c.TABLE_NAME=t.TABLE_NAME
              FOR XML PATH('Column'),ROOT('Columns'),TYPE
             ) AS AllTablesColumns
FROM INFORMATION_SCHEMA.TABLES AS t;

Without the FOR XML clause this would fail (...more than one value... / ...Only one column...)

Pass a generic SELECT as a parameter?

Some would say this is not possible, but you can try this:

CREATE FUNCTION dbo.TestType(@x XML)
RETURNS TABLE
AS
RETURN
SELECT @x AS BringMeBack;
GO

--The SELECT must be wrapped in paranthesis!
SELECT *
FROM dbo.TestType((SELECT TOP 5 * FROM sys.objects FOR XML PATH('x'),ROOT('y')));
GO

DROP FUNCTION dbo.TestType;