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.  

10 thoughts on “SQL For XML fails with UNION or UNION ALL

  1. Any ideas when it comes to simply trying to do a union between two selects that contain columns of an XML data type? I’ve got an “active” table on the production server and an “archive” table on the archive server. I’ve linked the servers and periodically move records from the active table to the archive table using a job. Occasionally I need to run a query to find a record or records, any of which could reside in either table, and I’d like to create a single view I can query against. Here’s the goal;

    select XMLField from [PRecords].dbo.[PTable]
    union
    select XMLField from [Archive].[PRecords].dbo.[PTable]

    When I try to create the view, I get an error stating:

    Xml data type is not supported in distributed queries. Remote object ‘[Archive].[PRecords].dbo.[PTable’ has xml column(s).

    Any ideas, or am I just simply out of luck?

  2. Thanks for your response Paul. I went through the openquery phase with this but found I still couldn’t insert or delete records to/from a table with one or more XML column(s) on a linked remote server. I finally realized that views in SQL are updatable and set up views on both servers that use CAST to convert the xml columns to varchar(max), then I was able to set up normal queries (without using openquery) and also successfully delete records from the view rather than directly from the table. This allowed me to set up a pull job on the archive server that queried records from the live server, insert them into the archive table (converting the varchar(max) back to xml during the insert), and then finally deleting the records from the live server by deleting them from the view.

  3. I think there is comma missing between 2 select statements.
    After
    (SELECT top 5 * FROM Customer WHERE CustomerType = ‘new’
    FOR xml path(‘Customer’), root(‘NewCustomers’), TYPE)
    statement; there should be a comma. Something like:

    (SELECT
    (SELECT top 5 * FROM Customer WHERE CustomerType = ‘new’
    FOR xml path(‘Customer’), root(‘NewCustomers’), TYPE) ,

    (SELECT top 5 * FROM Customer WHERE CustomerType = ‘old’
    FOR xml path(‘Customer’), root(‘OldCustomers’), TYPE)

    FOR xml path(”), TYPE)

Leave a Reply

Your email address will not be published. Required fields are marked *