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

19 comments:

sasi said...

The blog you shared is very good. I expect more information from you like this blog. Thankyou.
Python Training in bangalore
Python Course in Bangalore
Angularjs course Bangalore
Angularjs Training in Bangalore
Web Designing Course in bangalore
Web Development courses in bangalore
Salesforce Course in Bangalore
salesforce training in bangalore
Big Data Training in Bangalore
Hadoop Training in Bangalore

sathishkumar said...

python training in bangalore | python online training
aws training in bangalore | aws online training
artificial intelligence training in bangalore | artificial intelligence online training
machine learning training in bangalore | machine learning online training
blockchain training in bangalore | blockchain online training
uipath training in bangalore | uipath online training

harshni said...

I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
Artificial Intelligence Training in Chennai

Ai Training in Chennai

Artificial Intelligence training in Bangalore

Ai Training in Bangalore

Artificial Intelligence Training in Hyderabad | Certification | ai training in hyderabad

Artificial Intelligence Online Training

Ai Online Training

Blue Prism Training in Chennai

sathya said...

The data that you provided in the blog is informative and effective.I am happy to visit and read useful articles here. I hope you continue to do the sharing through the post to the reader. Read more about

selenium training in chennai

selenium training in chennai

selenium online training in chennai

selenium training in bangalore

selenium training in hyderabad

selenium training in coimbatore

selenium online training

lavanya said...

I must thank you for the efforts you have put in penning this site. I am hoping to check out the same high-grade content by you later on as well. Keep up the good work
I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!Java training in Chennai

Java Online training in Chennai

Java Course in Chennai

Best JAVA Training Institutes in Chennai

Java training in Bangalore

Java training in Hyderabad

Java Training in Coimbatore

Java Training

Java Online Training

surya said...

Great Article… I love to read your articles because your writing style is too good
angular js training in chennai

angular training in chennai

angular js online training in chennai

angular js training in bangalore

angular js training in hyderabad

angular js training in coimbatore

angular js training

angular js online training

anand said...

nice post
Software Testing Training in Chennai | Certification | Online
Courses



Software Testing Training in Chennai

Software Testing Online Training in Chennai

Software Testing Courses in Chennai

Software Testing Training in Bangalore

Software Testing Training in Hyderabad

Software Testing Training in Coimbatore

Software Testing Training

Software Testing Online Training

ramesh said...


Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge

Azure Training in Chennai

Azure Training in Bangalore

Azure Training in Hyderabad

Azure Training in Pune

Azure Training | microsoft azure certification | Azure Online Training Course

Azure Online Training

meritstep Technology said...

Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
blockchain online training
best blockchain online training
top blockchain online training

Ramya said...

Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here.
DevOps Training in Chennai

DevOps Online Training in Chennai

DevOps Training in Bangalore

DevOps Training in Hyderabad

DevOps Training in Coimbatore

DevOps Training

DevOps Online Training

dhinesh said...

Excellent Blog! I would Thanks for sharing this wonderful content.its very useful to us.This is incredible,I feel really happy to have seen your webpage.I gained many unknown information, the way you have clearly explained is really fantastic.keep posting such useful information.
Full Stack Training in Chennai | Certification | Online Training Course
Full Stack Training in Bangalore | Certification | Online Training Course

Full Stack Training in Hyderabad | Certification | Online Training Course
Full Stack Developer Training in Chennai | Mean Stack Developer Training in Chennai
Full Stack Training

Full Stack Online Training


Rashika said...

Every day I always visit sites to obtain the best information for materials research I was doing.......

Digital Marketing Training in Chennai

Digital Marketing Course in Chennai

SEO Training in Chennai

Digital Marketing Training in Bangalore

Digital Marketing Training in Hyderabad

Digital Marketing Training in Coimbatore

Digital Marketing Training

Digital Marketing Course

Digital Marketing Online Training


devi said...

Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work. Thanks for sharing your informative post on development.
Data Science Training In Chennai

Data Science Online Training In Chennai

Data Science Training In Bangalore

Data Science Training In Hyderabad

Data Science Training In Coimbatore

Data Science Training

Data Science Online Training

swaroop said...

Thanks for your interesting ideas.the information's in this blog is very much useful for me to improve my knowledge.

Web Designing Training in Chennai

Web Designing Course in Chennai

Web Designing Training in Bangalore

Web Designing Course in Bangalore

Web Designing Training in Hyderabad

Web Designing Course in Hyderabad

Web Designing Training in Coimbatore

Web Designing Training

Web Designing Online Training


Revathi said...

I feel really happy to have seen your webpage.I am feeling grateful to read this.you gave a nice information for us.please updating more stuff content...keep up!!

Android Training in Chennai

Android Online Training in Chennai

Android Training in Bangalore

Android Training in Hyderabad

Android Training in Coimbatore

Android Training

Android Online Training

prabhu said...

Keep up the great work, I read few blog posts on this site and I believe that your website is really interesting and has loads of good info.
IELTS Coaching in chennai

German Classes in Chennai

GRE Coaching Classes in Chennai

TOEFL Coaching in Chennai

spoken english classes in chennai | Communication training



prabhu said...


Keep up the great work, I read few blog posts on this site and I believe that your website is really interesting and has loads of good info.
IELTS Coaching in chennai

German Classes in Chennai

GRE Coaching Classes in Chennai

TOEFL Coaching in Chennai

spoken english classes in chennai | Communication training


vivekvedha said...

The data that you provided in the blog is informative and effective.I am happy to visit and read useful articles here.
acte chennai

acte complaints

acte reviews

acte trainer complaints

acte trainer reviews

acte velachery reviews complaints

acte tambaram reviews complaints

acte anna nagar reviews complaints

acte porur reviews complaints

acte omr reviews complaints

radhika said...

The blog you shared is very good. I expect more information from you like this blog. Thankyou.


AWS Course in Bangalore

AWS Course in Hyderabad

AWS Course in Coimbatore

AWS Course

AWS Certification Course

AWS Certification Training

AWS Online Training

AWS Training