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:
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Post a Comment