SQL UNION FOR XML and FOR JSON clauses are invalid in views and subqueries when they contain a set operator

When I am trying to generate an JSON output from SQL and need to use a UNION statement and also name the output column.

CREATE PROCEDURE [dbo].[Users.GetList]
(
 @SchoolCode NVARCHAR(200)=NULL,
 @ActionType VARCHAR(20)=NULL
)
AS
BEGIN
DECLARE @Result  NVARCHAR(MAX)
SET @Result=(
  SELECT 
  ST.FirstName 
 ,ST.LastName
 ,A.UserName
 ,ASP.Name
 ,ST.Who
FROM AspNetUserRoles AS AR
JOIN AspNetUsers AS A
ON AR.UserId = A.Id
JOIN AspNetRoles AS ASP
ON AR.RoleId = ASP.Id
JOIN StaffInfo AS ST
ON A.UserId = ST.UserId
WHERE ST.DeletedDate IS NULL
AND [email protected] 
UNION
SELECT 
  ST.FirstName 
 ,ST.LastName
 ,A.UserName
 ,ASP.Name
 ,'Student'AS Who
FROM AspNetUserRoles AS AR
JOIN AspNetUsers AS A
ON AR.UserId = A.Id
JOIN AspNetRoles AS ASP
ON AR.RoleId = ASP.Id
JOIN StaffInfo AS ST
ON A.UserId = ST.UserId
WHERE ST.DeletedDate IS NULL
AND [email protected] 
FOR JSON AUTO);
SELECT @Result
END

When execute the query this type of error has been occured

Msg 1086, Level 15, State 1, Procedure Users.GetList, Line 49 [Batch Start Line 0],The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.

Answer

Option 1.

ALTER PROCEDURE [dbo].[Users.GetList]
(
 @SchoolCode NVARCHAR(200)=NULL,
 @ActionType VARCHAR(20)=NULL
)
AS
BEGIN
DECLARE @Result  NVARCHAR(MAX)
SET @Result=(
 SELECT * FROM(
  SELECT 
  ST.FirstName 
 ,ST.LastName
 ,A.UserName
 ,ASP.Name
 ,ST.Who
FROM AspNetUserRoles AS AR
JOIN AspNetUsers AS A
ON AR.UserId = A.Id
JOIN AspNetRoles AS ASP
ON AR.RoleId = ASP.Id
JOIN StaffInfo AS ST
ON A.UserId = ST.UserId
WHERE ST.DeletedDate IS NULL
AND [email protected] 
UNION
SELECT 
  ST.FirstName 
 ,ST.LastName
 ,A.UserName
 ,ASP.Name
 ,'Student'AS Who
FROM AspNetUserRoles AS AR
JOIN AspNetUsers AS A
ON AR.UserId = A.Id
JOIN AspNetRoles AS ASP
ON AR.RoleId = ASP.Id
JOIN StudentInfo AS ST
ON A.UserId = ST.UserId
WHERE ST.DeletedDate IS NULL
AND [email protected] 
)A
FOR JSON PATH,ROOT('List'));
SELECT @Result
END

Option 2.

Wrap your 2 selects on a single one like so:

select (
    SELECT Fname, Lname from (
        SELECT Fname, Lname
        FROM MyTable
        UNION
        SELECT Fname, Lname 
        FROM MyTable
    ) A
   FOR JSON PATH,ROOT('List'));
) As JsonOutPut

Option 3.

DECLARE @Result NVARCHAR(MAX) SELECT @Result = 
(
    SELECT * FROM
    (
        SELECT  'MyFirstName' AS FirstName, 'MyLastName' AS LastName
        UNION
        SELECT  'MyFirstName' AS FirstName, 'MyLastName' AS LastName
    ) A
 FOR JSON PATH,ROOT('MenuList'))
SELECT @Result

Option 4

The UNION, FOR XML, and FOR JSON clauses in SQL are not supported in views and subqueries when they contain a set operator. A set operator is a clause that combines the results of two SELECT statements into a single result set (such as UNION, INTERSECT, or EXCEPT).

Here are some examples of invalid queries that use the UNION, FOR XML, and FOR JSON clauses in views and subqueries:

-- Invalid view

CREATE VIEW vw_invalid AS
SELECT * FROM table1
UNION
SELECT * FROM table2
FOR XML PATH
-- Invalid subquery
SELECT * FROM table1
WHERE column1 IN (
    SELECT column2 FROM table2
    UNION
    SELECT column3 FROM table3
    FOR JSON PATH
)

To use the UNION, FOR XML, and FOR JSON clauses in views and subqueries, you will need to remove the set operator or use a derived table or common table expression (CTE) instead.

Here is an example of how you might rewrite the invalid view from the above example using a derived table:

CREATE VIEW vw_valid AS
SELECT * FROM (
    SELECT * FROM table1
    UNION
    SELECT * FROM table2
) t
FOR XML PATH

And here is an example of how you might rewrite the invalid subquery using a CTE:

WITH cte AS (
    SELECT column2 FROM table2
    UNION
    SELECT column3 FROM table3
)
SELECT * FROM table1
WHERE column1 IN (
    SELECT * FROM cte
    FOR JSON PATH
)

I hope this helps! Let me know if you have any other questions about using the UNION, FOR XML, and FOR JSON clauses in SQL.

Option 5.

Yes, that is correct. The FOR XML and FOR JSON clauses are not allowed in views or subqueries that contain a set operator like UNION, UNION ALL, INTERSECT, or EXCEPT. These clauses can only be used in a SELECT statement that is not part of a view or subquery.

Here is an example of a SELECT statement that includes the FOR XML clause and is not part of a view or subquery:

SELECT col1, col2, col3
FROM table1
FOR XML RAW, ELEMENTS

You could use this SELECT statement as a standalone query, or you could include it in a stored procedure or function. However, you would not be able to use this SELECT statement as part of a view or subquery that contains a set operator.