Dynamic TSQL Query output as XML

Question

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.

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

try using for xml msdn.microsoft.com/en-us/library/ms178107.aspx — gh9
– gh9, Commented Jul 17, 2014 at 18:09
I know that... I just dont know how to use it with this dynamic statement as I cant put it after the execute function — SBB
– SBB, Commented Jul 17, 2014 at 18:12

Solomon Rutzky · Accepted Answer · 2014-07-17 18:51:03Z

4

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

edited Jul 17, 2014 at 18:51

answered Jul 17, 2014 at 18:24

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

SBB Over a year ago

I tried this but it seems to be returning 2 datasets. The XML and then another with return value of 0 which is causing an error

Solomon Rutzky Over a year ago

@SBB: datasets? Nothing should be returned at all since the entire query / SELECT is converted to XML via FOR XML... and that scalar value is set to a variable. Can you do a PRINT @sSQL; just prior to the EXEC sp_executesql to make sure that the concatenation was done correctly. The first code block I posted above is a working example of this technique, hence it does work. Your PRINT should show something like: SET @Out = (SELECT A.[submissionID],....

SBB Over a year ago

Minus the no count piece , was testing something

Solomon Rutzky Over a year ago

@SBB: two problems: 1) you did more than I suggested. You added the SET @var = ( twice, once in the initial SET @sSQL = (which is fine) and again just before the EXEC (which is wrong / unnecessary) , and 2) the new param is in your param spec at the beginning but at the end of the actual param list (i think they need to be in the same order, or maybe not since you are referencing params by name and not position. So remove the N'SET @Results = (' + from the SET just prior to the EXEC.

SBB Over a year ago

It's gotta be close, the only error now is Incorrect syntax near the keyword 'WHERE'.

|

Collectives™ on Stack Overflow

Dynamic TSQL Query output as XML

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related