T-SQL: использование ROW_NUMBER() для разбивки на страницы FOR XML - дочерние узлы не возвращаются

Я запускаю следующий запрос

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')

Который возвращает XML в следующем формате (я пропустил возвращенные значения атрибута - они не уместны):

<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>

Все идет нормально.

Теперь я хочу разбить результаты на страницы. Часть этого требует, чтобы вышеуказанный запрос выполнялся как подзапрос. Итак, теперь у меня есть следующий запрос:

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')

Тем не менее, это возвращает следующий 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>

Вы можете видеть, что введение подзапроса привело к тому, что предложение FOR XML больше не вкладывает дочерние результаты... но я не понимаю, почему.

Может кто-нибудь сказать мне, как я могу реализовать разбиение на страницы через ROW_NUMBER(), и все еще результаты будут отформатированы как первый блок XML выше?

Источник

2 ответа

Решение

Это не красиво, но как насчет этого:

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')
Источник

Спасибо @Justin Pihoney, вот мое решение:

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

Также, просто для полноты, "МЕЖДУ 5 И 20" будут заменены параметрами.

Источник
Другие вопросы по тегам