SQL Return subquery 'Total Records' to outer query

Question

This one has me a little stumped, I'm trying to get the total count in my top (root) node XML from this SQL query:

    SELECT COUNT(*) OVER() as '@totalCount', (
        SELECT COUNT(*) OVER() as totalCount, [Title], [Year], [Type], [Poster]
        FROM movies As result where CONTAINS(Title, @Title) Order by [Weight] DESC
        OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
        FETCH NEXT @RowspPage ROWS ONLY
        FOR XML AUTO, type
    ) 
    FOR XML PATH('root')

Obviously the COUNT(*) OVER() is only returning "1" because it's being executed at the top level and not within the subquery. But I only want to display it once in the root node and not repeat per each result.

Any help would be greatly appreciated.

Answer 1

EDIT: Here a working example with common data to walk along:

;with myCTE as
(
    select *
    from sys.objects
)
select (select count(*) from myCTE) as [@Counter]
    ,(
       SELECT myCTE.object_id AS id
             ,mycte.name AS name 
       from myCTE
       for xml path('object'),TYPE
      )       
for xml path('test')

You might try to shift your query into a CTE and put it to something like this:

;WITH MyQueryAsCTE AS
(
    SELECT [Title], [Year], [Type], [Poster]
    FROM movies As result where CONTAINS(Title, @Title) Order by [Weight] DESC
    OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
    FETCH NEXT @RowspPage ROWS ONLY
)
SELECT  (SELECT Count(*) FROM MyQueryAsCTE) as '@totalFound'
       ,[Title] AS [Movie/Title] 
       ,[Year] AS [Movie/Year]
       ,[Type] AS [Movie/Type]
       ,[Poster] AS [Movie/Poster]
FROM MyQueryAsCTE
FOR XML PATH('root')