SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they...


The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator.

Author
Message
petervdkerk
petervdkerk
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
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!
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23457 Visits: 9730
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
petervdkerk
petervdkerk
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
Great, thanks! Smile
ShineBoy
ShineBoy
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 155
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.
Patibandla
Patibandla
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 178
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.
simon.letts
simon.letts
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 606
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!
michael.evenson-916214
michael.evenson-916214
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 9
This solution rocks - I was able to get the output I needed by applying this solution to a scalar function I am creating
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search