T-SQL : Using ROW_NUMBER() to paginate FOR XML - child nodes not being returned

Question

I'm running the following query

SELECT
    ROW_NUMBER() OVER(ORDER BY [TransactionValues].[ID]) AS idx,
    [Transactions].[ID] AS [id],
    [Transactions].[EncryptedAccountID] AS [encryptedAccountID],
    [Transactions].[Uploaded] AS [uploaded],
    [Transactions].[Visible] AS [visible],
    [Fields].[ID] AS [fieldId],
    [Fields].[FriendlyName] AS [friendlyName],
    [Fields].[OfficialName] AS [officialName],
    [Fields].[Order] AS [order],
    [Fields].[Visible] AS [valueVisible],
    [TransactionValues].[ID] AS [valueId],
    [TransactionValues].[FieldID] AS [valueFieldId],
    [TransactionValues].[FriendlyValue] AS [friendlyValue],
    [TransactionValues].[OfficialValue] AS [officialValue],
    [TransactionValues].[TransactionID] AS [transactionId]
FROM
    [Transactions]
    INNER JOIN [TransactionValues]
    ON [TransactionValues].[TransactionID] = [Transactions].[ID]
    INNER JOIN [Fields]
    ON [TransactionValues].[FieldID] = [Fields].[ID]
WHERE
    [Transactions].[EncryptedAccountID] = @encryptedAccountID
FOR XML AUTO, ROOT('root')

Which returns XML in the following format (I've omitted the returned attribute values - these aren't pertinent):

<root>
    <Transactions idx="" id="" encryptedAccountID="" uploaded="" visible="">
        <Fields id="" friendlyName="" officialName="" order="" visible="">
            <TransactionValues valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
        </Fields>
        <Fields id="" friendlyName="" officialName="" order="" visible="">
            <TransactionValues valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
        </Fields>
    </Transactions>
    <Transactions idx="" id="" encryptedAccountID="" uploaded="" visible="">
        <Fields id="" friendlyName="" officialName="" order="" visible="">
            <TransactionValues valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
        </Fields>
        <Fields id="" friendlyName="" officialName="" order="" visible="">
            <TransactionValues valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
        </Fields>
    </Transactions>
</root>

So far so good.

Now, I want to paginate the results. Part of this requires that the above query is run as a sub-query. So I now have the following query:

SELECT
*
FROM
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY [TransactionValues].[ID]) AS idx,
        [Transactions].[ID] AS [id],
        [Transactions].[EncryptedAccountID] AS [encryptedAccountID],
        [Transactions].[Uploaded] AS [uploaded],
        [Transactions].[Visible] AS [visible],
        [Fields].[ID] AS [fieldId],
        [Fields].[FriendlyName] AS [friendlyName],
        [Fields].[OfficialName] AS [officialName],
        [Fields].[Order] AS [order],
        [Fields].[Visible] AS [valueVisible],
        [TransactionValues].[ID] AS [valueId],
        [TransactionValues].[FieldID] AS [valueFieldId],
        [TransactionValues].[FriendlyValue] AS [friendlyValue],
        [TransactionValues].[OfficialValue] AS [officialValue],
        [TransactionValues].[TransactionID] AS [transactionId]
    FROM
        [Transactions]
        INNER JOIN [TransactionValues]
        ON [TransactionValues].[TransactionID] = [Transactions].[ID]
        INNER JOIN [Fields]
        ON [TransactionValues].[FieldID] = .[Fields].[ID]
    WHERE
        [Transactions].[EncryptedAccountID] = @encryptedAccountID
) AS [TransactionInfo]
WHERE
    idx > 5
AND
    idx <= 20
ORDER BY
    [id], [order] ASC
FOR XML AUTO, ROOT('root')

However, this returns the following XML

<root>
    <TransactionInfo idx="" id="" encryptedAccountID="" uploaded="" visible="" fieldId="" friendlyName="" officialName="" order="" valueVisible="" valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
    <TransactionInfo idx="" id="" encryptedAccountID="" uploaded="" visible="" fieldId="" friendlyName="" officialName="" order="" valueVisible="" valueId="" valueFieldId="" friendlyValue="" officialValue="" transactionId="" />
</root>

You can see that the introduction of the sub-query has caused the FOR XML clause to no longer nest the child results... but I don't understand why.

Can anyone tell me how I can implement pagination via ROW_NUMBER(), and stil have the results formatted like the first block of XML above?

Answer 1

It isnt pretty, but how about this:

SELECT
    [Transactions].[ID] AS [id],
    [Transactions].[EncryptedAccountID] AS [encryptedAccountID],
    [Transactions].[Uploaded] AS [uploaded],
    [Transactions].[Visible] AS [visible],
    [Fields].[ID] AS [fieldId],
    [Fields].[FriendlyName] AS [friendlyName],
    [Fields].[OfficialName] AS [officialName],
    [Fields].[Order] AS [order],
    [Fields].[Visible] AS [valueVisible],
    [TransactionValues].[ID] AS [valueId],
    [TransactionValues].[FieldID] AS [valueFieldId],
    [TransactionValues].[FriendlyValue] AS [friendlyValue],
    [TransactionValues].[OfficialValue] AS [officialValue],
    [TransactionValues].[TransactionID] AS [transactionId],
    [TransactionValues].idx
FROM
    [Transactions]
    INNER JOIN 
    (
        SELECT *, ROW_NUMBER() OVER(ORDER BY [TransactionValues].[ID]) AS idx
        FROM [TransactionValues]
    ) AS [TransactionValues]
        ON [TransactionValues].[TransactionID] = [Transactions].[ID]
    INNER JOIN [Fields]
        ON [TransactionValues].[FieldID] = .[Fields].[ID]
WHERE
    [Transactions].[EncryptedAccountID] = @encryptedAccountID
    AND [TransactionValues].idx BETWEEN 5 AND 20
FOR XML AUTO, ROOT('root')