The FOR XML clause is 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 syntax and apply FOR XML on top of it.

I am creating two tables in the current database in sql server:

CREATE TABLE LocalEmployee(

    Id BIGINT IDENTITY PRIMARY KEY,

    Name VARCHAR(50),

    Location VARCHAR(20)

)

CREATE TABLE ForeignEmployee(

    Id BIGINT IDENTITY PRIMARY KEY,

    Name VARCHAR(50),

    Location VARCHAR(20)

)

Now if we will execute following sql queries in sql server:

1.  Creating a view

CREATE VIEW vwEmployee

AS(

    SELECT Id,Name

    FROM LocalEmployee

   

    UNION ALL

   

    SELECT Id,Name

    FROM ForeignEmployee

    FOR XML AUTO

)

2. Query using common table expression:

WITH cteEmployee

AS(

    SELECT Id,Name

    FROM LocalEmployee

   

    EXCEPT

   

    SELECT Id,Name

    FROM ForeignEmployee

    FOR XML AUTO

)SELECT * FROM cteEmployee

We will get error message like:

The FOR XML clause is 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 syntax and apply FOR XML on top of it.

Cause:  We cannot use FOR XML clause in view, cte etc if it contains set operator like UNION, EXCEPT, INTERSECT etc.

Solution: If we want to use FOR XML clause we have to use derived table. For example:

1.

CREATE VIEW vwEmployee

AS(

    SELECT * FROM(

    SELECT Id,Name

    FROM LocalEmployee

   

    UNION ALL

   

    SELECT Id,Name

    FROM ForeignEmployee

    ) DT

    FOR XML AUTO

)

2.

WITH cteEmployee(Col)

AS(

    SELECT * FROM(

    SELECT Id,Name

    FROM LocalEmployee

   

    EXCEPT

   

    SELECT Id,Name

    FROM ForeignEmployee

    ) AS DT

    FOR XML AUTO

   

)SELECT * FROM cteEmployee