is there an optimal way to serialize a query as xml and to get the rows count, both using only one statement?

Question

Writing a SQL Server database object, I was faced with the problem of to serialize some data to XML and to get the count of rows being serialized.

I wrote something like this using a CTE:

-- Way III
begin    
    declare @TmpUno as table (
        uno int, dos varchar(max)
    )
    declare @RC int 
    declare @XmlSerializedData xml

    insert into @TmpUno (uno, dos)
    values (1, 'uno'), (2, 'dos')

    -- Way III :
    -- * Serialization data is stored in a local variable
    -- * Rows count is stored in a local variable
    ;with c1 as 
    (
        select uno, dos
        from @TmpUno 
    )
    select 
        @XmlSerializedData = (select * from c1 for xml path('row')),
        @RC = (select count(1) from @TmpUno)
    -- But is this the optimal way?

    select 'Way III:' as way, 
           @XmlSerializedData as [@XmlSerializedData], 
           @RC as [@RC] 
end

For a small table and low number of requests, that would be enough to solve the problem, but consider now to evaluate if this is this the optimal way. Execution plan look like this

Showing that, in effect two table scans are performed (circled blue)

For optimization, my question: ¿Is there a way to serialize and to count the rows being serialized, and store both results (the xml, and the rouwcount) into script local variables using only one statement, or performing only one table scan?

Answer 1

Simply use @@rowcount. eg

select top 1000 * 
from sales.Customer
for xml path 

select @@rowcount rows

This will send an XML stream to the client followed by a single-row single-column resultset.

Answer 2

Don't use @@ROWCOUNT if you are storing the data in a variable, or if you are using the ,TYPE clause. It won't work, you will just get 1.

Instead, you could count the nodes inside the XML

declare @RC int, @XmlSerializedData xml;

set @XmlSerializedData =
(
    select uno, dos
    from @TmpUno
    for xml path('row'), type
);

set @RC = @XmlSerializedData.value('count(row)', 'int');

A more efficient version is to assign each XML node a row-number, then just select the last node's number.

declare @RC int, @XmlSerializedData xml;

set @XmlSerializedData =
(
    select
      uno,
      dos,
      RowNumber = ROW_NUMBER() OVER (ORDER BY SomeColumn)
    from @TmpUno
    for xml path('row'), type
);

set @RC = @XmlSerializedData.value('(row[last()]/RowNumber/text())[1]', 'int');

You could even modify this second option to get the row-number in the XML just once, and not within any <row> node.

select @XmlSerializedData =
(
    select
      uno as [row/uno],
      dos as [row/dos],
      RowNumber =
             CASE WHEN
                  LEAD(0) OVER (ORDER BY SomeColumn) IS NULL  -- no later row
             THEN ROW_NUMBER() OVER (ORDER BY SomeColumn) END
    from @TmpUno
    for xml path(''), type
);

db<>fiddle

Answer 3

For the top level FOR XML statement rows get output to a top level XML SELECT operator and @@ROWCOUNT is set accordingly.

When you assign it to a variable they get collapsed down to 1 by the UDX operator and @@ROWCOUNT doesn't return what you need.

I can't think of any sensible possibility to get these rows counted along the way in the yellow highlighted portion of the plan and surface this up without altering the XML.

A non sensible method is below though for "fun" (depends on elevated permissions and brittle execution plan details as well as lightweight query profiling infastructure being enabled)

A way that technically works but which I do not recommend!

;with
 c1 as (select uno, dos
          from @TmpUno )
  SELECT @XmlSerializedData = XmlSerializedData, 
         @RC = row_count
  FROM (VALUES((select * from c1 for xml path('row'), type))) V1(XmlSerializedData)
  LEFT JOIN (VALUES ((SELECT row_count FROM sys.dm_exec_query_profiles WHERE session_id = @@SPID  and physical_operator_name = 'Table Scan'))) rc(row_count) ON 1 = 1
  OPTION (FORCE ORDER)