Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. Expand / Collapse
Author
Message
Posted Friday, April 29, 2011 9:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 10:12 AM
Points: 99, Visits: 295
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!
Post #1100915
Posted Friday, April 29, 2011 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1100967
Posted Friday, April 29, 2011 12:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 10:12 AM
Points: 99, Visits: 295
Great, thanks! :)
Post #1101004
Posted Wednesday, November 28, 2012 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 30, 2014 7:56 PM
Points: 29, Visits: 86
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.

Post #1389617
Posted Tuesday, March 19, 2013 1:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 4:28 AM
Points: 53, Visits: 169
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.
Post #1432507
Posted Friday, June 27, 2014 3:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 143, Visits: 525
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!
Post #1586755
Posted Wednesday, August 13, 2014 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:18 AM
Points: 1, Visits: 5
This solution rocks - I was able to get the output I needed by applying this solution to a scalar function I am creating
Post #1602699
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse