SQL-сервер для XML EXPLICIT

Question

Я хочу присвоить результат оператора SELECT FOR XML EXPLICIT такой переменной XML, как

CREATE PROCEDURE BILLING_RESPONSE 
AS   DECLARE @Data AS  XML

SET @Data = (SELECT

                1 AS Tag,
                NULL AS Parent,
                NULL AS 'CallTransactions!1!',
                NULL AS 'TCALTRS!2!TRS_DAT_TE!cdata',
                NULL AS 'TCALTRS!2!TRS_CRT_DT!Element'
            UNION ALL
            SELECT
                2 AS Tag,
                1 AS Parent, 
                NULL, 
                TRS_DAT_TE,
                TRS_CRT_DT  
             FROM TCALTRS 
             WHERE TRS_CRT_DT between CONVERT(date,GETDATE()-1) and CONVERT(date,getdate()) and 
             TRS_DAT_TE like '%(Submit Response)%'  
             FOR XML EXPLICIT           
            )

SELECT @DATA
GO

Когда я выполняю этот запрос, получаю следующую ошибку Msg 1086, Уровень 15, Состояние 1, Процедура BILLING_RESPONSE, Строка 22 Предложение FOR XML недопустимо в представлениях, встроенных функциях, производных таблицах и подзапросах, когда они содержат оператор набора. Чтобы обойти это, оберните SELECT, содержащий оператор set, используя производный синтаксис таблицы, и примените FOR XML поверх него.

Answer 1

Если это так, вам не нужна переменная @Data. Пусть ваш sp вернет результат запроса напрямую, и все готово.

CREATE PROCEDURE BILLING_RESPONSE AS
SELECT

        1 AS Tag,
        NULL AS Parent,
        NULL AS 'CallTransactions!1!',
        NULL AS 'TCALTRS!2!TRS_DAT_TE!cdata',
        NULL AS 'TCALTRS!2!TRS_CRT_DT!Element'
    UNION ALL
    SELECT
        2 AS Tag,
        1 AS Parent, 
        NULL, 
        TRS_DAT_TE,
        TRS_CRT_DT  
     FROM TCALTRS 
     WHERE TRS_CRT_DT between CONVERT(date,GETDATE()-1) and CONVERT(date,getdate()) and 
     TRS_DAT_TE like '%(Submit Response)%'  
     FOR XML EXPLICIT

Answer 2

Ошибка не совсем ясна, но она говорит о том, что вы не можете использовать FOR XML предложение во встроенном подзапросе, потому что он содержит UNION (тип оператора set)

Предлагаемый обходной путь - заключить подзапрос во что-то еще и вызвать его отдельно, например:

CREATE PROCEDURE BILLING_RESPONSE 
AS   DECLARE @Data AS  XML

;WITH DATA AS(
               SELECT
                1 AS Tag,
                NULL AS Parent,
                NULL AS 'CallTransactions!1!',
                NULL AS 'TCALTRS!2!TRS_DAT_TE!cdata',
                NULL AS 'TCALTRS!2!TRS_CRT_DT!Element'
            UNION ALL
            SELECT
                2 AS Tag,
                1 AS Parent, 
                NULL, 
                TRS_DAT_TE,
                TRS_CRT_DT  
             FROM TCALTRS 
             WHERE TRS_CRT_DT between CONVERT(date,GETDATE()-1) and CONVERT(date,getdate()) and 
             TRS_DAT_TE like '%(Submit Response)%'  
             FOR XML EXPLICIT           
            )
SELECT @Data = (SELECT * FROM DATA FOR XML EXPLICIT)
SELECT @DATA
GO