SQL For XML fails with UNION or UNION ALL

I ran across this quirky  feature in SQL server 2000 and up that I need to put somewhere in the off chance I need to work with it again. Its this feature called ‘for xml‘, and it essentially allows you to return the result of a query in xml format. Yay.

It works fine for the most part, there are plenty of examples out there about the different modes and syntax and how to make it work, but if you run into an error like this:

Msg 1086, Level 15, State 1, Line 8
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.

Chances are, your wrote some SQL that looked similar to this:

  1.  
  2. SELECT top 2
  3. CompanyId AS "@Id",
  4. CompanyName AS "@Name",
  5. SELECT * FROM
  6. (
  7. SELECT
  8. (SELECT top 5 * FROM Customer WHERE CustomerType = 'new'
  9. FOR xml path('Customer'), root('NewCustomers'), TYPE)
  10.  
  11. UNION ALL
  12.  
  13. SELECT
  14. (SELECT top 5 * FROM Customer WHERE CustomerType = 'old'
  15. FOR xml path('Customer'), root('OldCustomers'), TYPE)
  16. )
  17. FOR xml path(''), TYPE
  18. FROM Company
  19. FOR xml path('Company'), root('CompanyData'), TYPE
  20.  

In an attempt to produce XML that looked like this:

  1.  
  2. <CompanyData>
  3. <Company Name="ACME Soft" Id="25">
  4. <NewCustomers>
  5. <Customer>
  6. <Id>1</Id>
  7. <Name>Bob's</Name>
  8. </Customer>
  9. <Customer>
  10. <Id>3</Id>
  11. <Name>Sally's</Name>
  12. </Customer>
  13. <Customer>
  14. <Id>9</Id>
  15. <Name>Charlies</Name>
  16. </Customer>
  17. ...
  18. </NewCustomers>
  19. <OldCustomers>
  20. <Customer>
  21. <Id>66</Id>
  22. <Name>Paul's</Name>
  23. </Customer>
  24. <Customer>
  25. <Id>67</Id>
  26. <Name>Bubba</Name>
  27. </Customer>
  28. <Customer>
  29. <Id>90</Id>
  30. <Name>Bobbies</Name>
  31. </Customer>
  32. ...
  33. </OldCustomers>
  34. </Company>
  35. <Company Name="Example Ware" Id="13">
  36. ...
  37. </Company>
  38. </CompanyData>
  39.  

but unfortunately, no matter what way you slice and dice it you’ll continue to get errors and warnings and nesting won’t work correctly and adding parenthesizes (Which one would think should NOT affect the order of execution for a query) causes query execution failures.

Long and the short of it is, when your using the for XML, you don’t need to union the results of the two sub queries, for XML will figure it out, so try and rewrite your query this way without the UNION ALL:

  1.  
  2. SELECT top 2
  3. CompanyId AS "@Id",
  4. CompanyName AS "@Name",
  5. (SELECT
  6. (SELECT top 5 * FROM Customer WHERE CustomerType = 'new'
  7. FOR xml path('Customer'), root('NewCustomers'), TYPE)
  8.  
  9. (SELECT top 5 * FROM Customer WHERE CustomerType = 'old'
  10. FOR xml path('Customer'), root('OldCustomers'), TYPE)
  11.  
  12. FOR xml path(''), TYPE)
  13. FROM Company
  14. FOR xml path('Company'), root('CompanyData'), TYPE
  15.  

Bit-o Graphics work

I got hired a few days ago to do some graphic design work for an upcoming concert / rave here in downtown Salt Lake City, for those of you who have heard of Darude, he is DJ’ing live here on October 18th. Furthermore, it’s also going to be the official movie release party for Saw V, for those who like that genre of movie.

(Click to expand the images)

Paul Rohde

An example of what NOT to do

Allow me to tell you a story…

It starts with a guy working on a statistics project using the directory of students and alumni to get them to take a survey.  No problem right, people do this every now and again.  I looked at it yesterday, didn’t thing to much of it; until this morning.  I get to school, and after my first class a friend of mine asks if I’ve checked my school email yet… No, I haven’t opened outlook yet.  So I go open it up, and after about 10 minutes I have a total of 29 e-mails of people who have replied all to that original message.  Not only that, the size of the messages increases a huge amount for every reply all due to the huge list of people and e-mail addresses that are copied every time the message is re-replied to.

Later today, one of the faculty comes in and makes an announcement that sending mass e-mails is in violation of the acceptable use policy that everybody signed when coming to Neumont and that people reply-all’ing will be repremanded and/or be put on academic probation.

The spam fest stopped right after that… I wonder why…

But wait…

… it gets better.

Remember the part where the original e-mail got sent to some alumni? As in former Neumont students now working for many of the companies that may hire these up-and-coming Neumont graduates?

So I get an e-mail from the VP of Employer Relations talking about the results of this little spam-fest, he puts it best:

Just so you are aware, this reply-to-all email chain was sent to not only our current students but to a large majority of our alumni. I’ve had numerous complaints from alumni over the past few hours regarding their inboxes being filled with this reply-to-all garbage.

They have voiced their frustration with the apparent maturity level of our current students. I have also heard from several of them regarding their desires to continually hire our students and that this unprofessional and immature email chain makes them think twice about doing so. Many of you who replied to all on the chain have been black-listed with several of those employers.

Email is a form of communication that is extremely easy to misinterpret. The lack of body language and facial expressions makes the message in an email easy to misconstrue and potentially very powerful (positively or negatively). A general rule of thumb I use in all emails is to pause before hitting “send” and think about whether the tone, content and purpose of the email is what I want to convey and consider if the repercussions are the ones that I would want/expect. I encourage all of you to exercise professionalism in all forms of communication when representing yourself, your school and your peers.

Literally an “Oh Sh**” moment.

So remember, what you do, even the little things can have, well, life changing ramifications.  Think before you click.

Paul Rohde