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.