February 24, 2009 at 4:53 am
Hi Folks,
Apologies if this is a simple question but for the life of me I cannot work out. I have an xml query that I want to write to a variable but for some reason cannot.
-- Write out the list in XML format.
SELECT 1 as TAG
,NULL as PARENT
,NULL as [ContractRenewalList!1]
,NULL as [ContractRenewalContract!2!ContractNumber]
,NULL as [ContractRenewalContract!2!ContractID]
,NULL as [ContractRenewalContract!2!NodeTypeCode]
,NULL as [ContractRenewalContract!2!OperationsCenterName]
,NULL as [ContractRenewalContract!2!SalesLocationName]
,NULL as [ContractRenewalContract!2!BusinessName]
,NULL as [ContractRenewalContract!2!NodeStatus]
,NULL as [PO!3!PurchaseOrderID]
,NULL as [PO!3!PurchaseOrderNumber]
,NULL as [PO!3!ContractID]
,NULL as [PO!3!NodeTypeCode]
,NULL as [PO!3!status]
UNION ALL
SELECT 2 as TAG
,1 as PARENT
,NULL
,Contracts.NodeNumber
,Contracts.NodeID
,Contracts.NodeType
,Contracts.OperationsCenterName
,Contracts.SalesLocationName
,Contracts.BusinessName
,Contracts.NodeStatus
,NULL
,NULL
,NULL
,NULL
,NULL
FROM @ContractRenewalRecordList Contracts
JOIN ContractRenewalType ept WITH (NOLOCK) ON Contracts.NodeType = ept.NodeTypeCode
WHERE ept.NodeName = 'ContractID'
UNION ALL
SELECT 3 as TAG
,2 as PARENT
,NULL
,Contracts.NodeNumber
,Contracts.NodeID
,Contracts.NodeType
,Contracts.OperationsCenterName
,Contracts.SalesLocationName
,Contracts.BusinessName
,Contracts.NodeStatus
,PurchaseOrders.NodeID
,PurchaseOrders.NodeNumber
,PurchaseOrders.NodeParentID
,PurchaseOrders.NodeType
,PurchaseOrders.NodeStatus
FROM @ContractRenewalRecordList Contracts
INNER JOIN @ContractRenewalRecordList PurchaseOrders on (Contracts.NodeID = PurchaseOrders.NodeParentID)
JOIN ContractRenewalType ept WITH (NOLOCK) ON PurchaseOrders.NodeType = ept.NodeTypeCode
WHERE ept.NodeName = 'PurchaseOrderID'
ORDER BY
2,3,4 desc
FOR XML EXPLICIT
whenever I go to wrap the above in the following SELECT @XMLOutput = (... Query As above...) I get the following 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.
I've looked online for similar errors but even trying some of the fixes there did not help. any help is greatly appreciated.
Thanks in advance.
Martin
February 24, 2009 at 7:57 am
Actually got. Thanks anyway to all.
Martin
February 24, 2009 at 8:17 am
Can you post what was successful for you? This will help others if they find this thread when searching for the same or similar problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply