April 29, 2011 at 9:15 am
I have the following XML statement, where I want the union of several tables to be presented as XML.
I get this error:
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.
My SQL:
select ISNULL((
SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,3 as objecttype,am.CreateDate
FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN cameras c ON c.userid=au.userid WHERE c.indexboost=0
UNION ALL
SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,1 as objecttype,am.CreateDate
FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN locations c ON c.userid=au.userid WHERE c.indexboost=0
AND c.id NOT IN (SELECT objectid FROM emailssent WHERE category=c.objecttype AND emailid=2)
order by am.CreateDate asc
FOR XML RAW, ELEMENTS ,ROOT ('user')),0) as records
How can I fix it to get it to work?
Thanks!
April 29, 2011 at 11:24 am
The error message recommends turning it into a derived table format. Try this:
SELECT ISNULL((SELECT *
FROM (SELECT TOP 10
au.sex,
au.firstname,
au.middlename,
au.lastname,
am.email,
c.id AS objectid,
c.title AS objecttitle,
c.friendlyurl AS objecturl,
3 AS objecttype,
am.CreateDate
FROM aspnet_users au
INNER JOIN aspnet_membership am
ON am.userid = au.userid
INNER JOIN cameras c
ON c.userid = au.userid
WHERE c.indexboost = 0
UNION ALL
SELECT TOP 10
au.sex,
au.firstname,
au.middlename,
au.lastname,
am.email,
c.id AS objectid,
c.title AS objecttitle,
c.friendlyurl AS objecturl,
1 AS objecttype,
am.CreateDate
FROM aspnet_users au
INNER JOIN aspnet_membership am
ON am.userid = au.userid
INNER JOIN locations c
ON c.userid = au.userid
WHERE c.indexboost = 0
AND c.id NOT IN (
SELECT objectid
FROM emailssent
WHERE category = c.objecttype
AND emailid = 2)) Sub
ORDER BY CreateDate ASC
FOR
XML RAW,
ELEMENTS,
ROOT('user')), 0) AS records ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2011 at 12:45 pm
Great, thanks! 🙂
November 28, 2012 at 3:39 am
Hey thanks, very helpful.
in the end I had some fun trying to map what you had to what I was doing and then realised that simply creating a view of the original complex view (without the Order by and For XML fragments on it worked well.
EG:
Original query:
Select 1 as Tag, NULL As Parent,NULL AS [Invoices!1!],
NULL AS [Invoice!2!InvoiceNumber!element],NULL AS [Invoice!2!Date!element],NULL AS [Invoice!2!DueDate!element],NULL AS [Invoice!2!Status!element],
NULL AS [Invoice!2!LineAmountTypes!element],NULL AS [Invoice!2!Type!element],NULL AS [Invoice!2!SubTotal!element],NULL AS [Invoice!2!TotalTax!element],
NULL AS [Invoice!2!Total!element],NULL AS [Invoice!2!Reference!element],
NULL AS [Contact!3!],NULL AS [Contact!3!Name!element],NULL AS [Contact!3!ContactID!element],
NULL AS [LineItems!4!],
NULL AS [LineItem!5!LineNumber!hide], NULL AS [LineItem!5!Description!element],NULL AS [LineItem!5!AccountCode!element],NULL AS [LineItem!5!UnitAmount!element],
NULL AS [LineItem!5!TaxAmount!element],NULL AS [LineItem!5!Quantity!element],
NULL AS [Tracking!6!],NULL AS [TrackingCategory!7!Name!element],NULL AS [TrackingCategory!7!Option!element]
from [V_XeroInvoice] A
UNION
Select 2 AS Tag, 1 AS Parent,InvoiceNumber, B.InvoiceNumber,
B.Date,B.DueDate, B.Status, B.LineAmountTypes, B.Type, B.Total AS SubTotal, B.TotalTax, B.Total + B.TotalTax as Total, B.Reference, NULL AS Contact, NULL, NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL AS Tracking,null,null
from [V_XeroInvoice] B
UNION
Select 3 AS Tag, 2 As Parent,InvoiceNumber,C.InvoiceNumber,
null,null,null,null,null,null,null,null,null,NULL As Contact, C.Name, C.ContactID,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL AS Tracking,null,null
FROM [V_XeroContact] C
UNION
Select 4 AS Tag, 2 As Parent,InvoiceNumber,D.InvoiceNumber,
null,null,null,null,null,null,null,null,null,NULL As Contact, NULL, NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL AS Tracking,null,null
FROM dbo.V_XeroLines D
UNION
Select 5 AS Tag, 4 As Parent,InvoiceNumber,D.InvoiceNumber,
null,null,null,null,null,null,null,null,null,NULL As Contact, NULL, NULL,NULL,
D.LineNumber, D.Description, D.AccountCode, D.UnitAmount, D.TaxAmount, D.Quantity,NULL AS Tracking, NULL,null
FROM dbo.V_XeroLines D
UNION
Select 6 AS Tag, 5 As Parent,InvoiceNumber,E.InvoiceNumber,
null,null,null,null,null,null,null,null,null,NULL As Contact, NULL, NULL,NULL,
E.LineNumber, null, null, null, null, null,NULL AS Tracking, NULL,null
FROM dbo.V_XeroTracking E
UNION
Select 7 AS Tag, 6 As Parent,InvoiceNumber,F.InvoiceNumber,
null,null,null,null,null,null,null,null,null,NULL As Contact, NULL, NULL,NULL,
F.LineNumber, null, null, null, null, null, NULL AS Tracking, F.[Name], F.[Option]
FROM dbo.V_XeroTracking F
ORDER BY [Invoice!2!InvoiceNumber!element], [Contact!3!Name!element],[LineItem!5!LineNumber!hide]
for xml EXPLICIT
Simply Stripped off the last 2 lines and added a Create View dbo.V_Xml as to the top, then getting the result into the @XML parameter as easy as :
Declare @XML XML
Set @Xml =
(Select * From dbo.V_XML
ORDER BY [Invoice!2!InvoiceNumber!element], [Contact!3!Name!element],[LineItem!5!LineNumber!hide]
for xml EXPLICIT)
Select @Xml
Thanks heaps, hope the rest of you find this useful..
All written and used on MS SQL 2005.
March 19, 2013 at 1:30 am
be aware that by using an External Select Statement we are going to have an Extra tag being added in the XML File. So we have to take care of the XML Consumption Process accordingly.
Regards,
Patibandla.
June 27, 2014 at 3:23 am
Just thought I'd add my thanks for turning this problem into an easy task having had the same 'wrap' issue! Used the view technique....superb!
August 13, 2014 at 6:32 am
This solution rocks - I was able to get the output I needed by applying this solution to a scalar function I am creating
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply