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

  • 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!

  • 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

  • Great, thanks! 🙂

  • 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.

  • 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.

  • 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!

  • 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