Sep 22, 2013

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

No comments:

Post a Comment