Dynamic TSQL Query output as XML

I have a dynamic TSQL query that is working perfectly. However, before I had it dynamic like this I was returning it as an XML output.

How can I do the same with my output now? I need the results in an XML format.

  • SQL for including rows based on list in input variable or all rows if variable is null
  • sql server 2008 management studio not checking the syntax of my query
  • How do I store the result of a query inside a temporary table in a stored procedure?
  • Need Help Speeding Up a SQL Server Query
  • SQL - Row sum with condition from 2 tables
  • Recover Updated Data - SQL Server 2005
  • ALTER PROCEDURE [dbo].[empowermentFetchSubmissions2]
    @category INT=NULL, @department INT=NULL, @startDate DATE=NULL, @endDate DATE=NULL, @empID VARCHAR (60)=NULL, @submissionID INT=NULL, @inVoting INT=NULL, @pastWinners INT=NULL
    AS
    DECLARE @sSQL AS NVARCHAR (3000), 
    @Where AS NVARCHAR (1000) = ' (1=1) ';
    BEGIN
        SET NOCOUNT ON;
        BEGIN
            SET @sSQL = 'SELECT  A.[submissionID],
                                 A.[subEmpID],
                                 A.[nomineeEmpID],
                                 A.[nomineeDepartment],
                                 CONVERT (VARCHAR (10), A.[submissionDate], 101) AS submissionDate,
                                 A.[situation],
                                 A.[task],
                                 A.[action],
                                 A.[result],
                                 A.[timestamp],
                                 A.[statusID],
                                 A.[approver],
                                 A.[approvalDate],
                                 B.[FirstName] + '' '' + B.[LastName] AS nomineeName,
                                 B.[ntid] AS nomineeNTID,
                                 B.[qid] AS nomineeQID,
                                 C.[FirstName] + '' '' + C.[LastName] AS submitName,
                                 C.[ntid] AS submitNTID,
                                 D.[categoryName],
                                 (SELECT CAST 
                                 (CASE WHEN EXISTS (SELECT TOP (1) submissionID
                                        FROM   empowermentEntries
                                        WHERE  sessionID = (SELECT TOP (1) sessionID
                                                                                FROM   empowermentSessions
                                                                                WHERE  status = 1 
                                                                                AND CAST(GETDATE() as date) >= startDate 
                                                                                AND CAST(GETDATE() as date) <= endDate ) AND submissionID = A.[submissionID]) 
                                        THEN ''true''
                                        ELSE ''false''
                                END AS XML) AS inVoting)
                        FROM     empowermentSubmissions AS A
                                 INNER JOIN
                                 empTable AS B
                                 ON A.[nomineeEmpID] = B.[empID]
                                 INNER JOIN
                                 empTable AS C
                                 ON A.[subEmpID] = C.[empID]
                                 INNER JOIN
                                 empowermentCategories AS D
                                 ON A.[categoryID] = D.[catID]';
            IF @category IS NOT NULL
                SET @Where = @Where + ' AND A.[categoryID] = @_category';
            IF @department IS NOT NULL
                SET @Where = @Where + ' AND A.[nomineeDepartment] = @_department';
            IF @startDate IS NOT NULL
                SET @Where = @Where + ' AND A.[submissionDate] >= @_startDate';
            IF @endDate IS NOT NULL
                SET @Where = @Where + ' AND A.[submissionDate] <= @_endDate';
            IF @empID IS NOT NULL
                SET @Where = @Where + ' AND A.[nomineeEmpID] = @_empID';
            IF @submissionID IS NOT NULL
                SET @Where = @Where + ' AND A.[submissionID] = @_submissionID';
            IF @inVoting IS NOT NULL
                SET @Where = @Where + ' AND A.[submissionID] IN (SELECT submissionID
                                                           FROM   empowermentEntries
                                                           WHERE  sessionID = (SELECT TOP (1) sessionID
                                                                                FROM   empowermentSessions
                                                                                WHERE  status = 1 
                                                                                AND CAST(GETDATE() as date) >= startDate 
                                                                                AND CAST(GETDATE() as date) <= endDate )
                                                                  AND submissionID = A.[submissionID])';
            IF @pastWinners IS NOT NULL
                SET @Where = @Where + ' AND A.[submissionID] IN (SELECT E.[submissionID]
                                        FROM   empowermentEntries as E
                                        JOIN   empowermentWinners as F
                                        ON E.[entryID] = F.[entryID]
                                        WHERE  submissionID = A.[submissionID])';
            IF LEN(@Where) > 0
                SET @sSQL = @sSQL + ' WHERE ' + @Where;
            EXECUTE sp_executesql @sSQL, N'@_category INT, @_department INT, @_startDate DATE, @_endDate DATE, @_empID VARCHAR(60), @_submissionID INT, @_inVoting INT, @_pastWinners INT', @_category = @category, @_department = @department, @_startDate = @startDate, @_endDate = @endDate, @_empID = @empID, @_submissionID = @submissionID, @_inVoting = @inVoting, @_pastWinners = @pastWinners;
        END
    END
    

    One Solution collect form web for “Dynamic TSQL Query output as XML”

    You need to take your existing Dynamic SQL, added the FOR XML... to the end, wrap that in parenthesis, and set that value to a variable which is used as OUTPUT for the sp_executesql. For example:

    DECLARE @SQL NVARCHAR(MAX),
            @Results XML;
    
    SET @SQL = N'
    SET @Out = (SELECT * FROM sys.objects FOR XML AUTO);
    ';
    
    EXEC sp_executesql @SQL, N'@Out XML OUTPUT', @Out = @Results OUTPUT;
    SELECT @Results;
    

    So declare a new variable at the top for:

    DECLARE @Results XML;
    

    Then just before your EXECUTE sp_executesql @sSQL... line, do the following:

    SET @sSQL = N'SET @Results = (' + @sSQL + N' FOR XML {xml options});';
    

    Then update your param spec for sp_executesql to include, at the end:

    N'@_category INT, ..., @Results XML OUTPUT'
    

    And add the following to the end of the sp_executesql:

    @_category = @category, ..., @Out = @Results OUTPUT
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.