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:

select top 2 
	CompanyId as "@Id",
	CompanyName as "@Name",
	select * from 
	(
		select
			(select top 5 * from Customer where CustomerType = 'new'
				for xml path('Customer'), root('NewCustomers'), TYPE)
		 
		UNION ALL
		 
		select
			(select top 5 * from Customer where CustomerType = 'old'
				for xml path('Customer'), root('OldCustomers'), TYPE)
	)
	for xml path(''), TYPE
from Company
for xml path('Company'), root('CompanyData'), TYPE

In an attempt to produce XML that looked like this:

<CompanyData>
	<Company Name="ACME Soft" Id="25">
		<NewCustomers>
			<Customer>
				<Id>1</Id>
				<Name>Bob's</Name>
			</Customer>
			<Customer>
				<Id>3</Id>
				<Name>Sally's</Name>
			</Customer>
			<Customer>
				<Id>9</Id>
				<Name>Charlies</Name>
			</Customer>
			...
		</NewCustomers>
		<OldCustomers>
			<Customer>
				<Id>66</Id>
				<Name>Paul's</Name>
			</Customer>
			<Customer>
				<Id>67</Id>
				<Name>Bubba</Name>
			</Customer>
			<Customer&g	t;
				<Id>90</Id>
				<Name>Bobbies</Name>
			</Customer>
			...
		</OldCustomers>
	</Company>
	<Company Name="Example Ware" Id="13">
		...
	</Company>
</CompanyData>

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:

select top 2 
	CompanyId as "@Id",
	CompanyName as "@Name",
	(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)
from Company
for xml path('Company'), root('CompanyData'), TYPE