Nested XML with XML Explicit
I am using SQL Server 2005.
I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as
DECLARE
@outputXMLasXMLSET
@outputXML=(
SELECT 1as TAG,NULLas PARENT, BatchIDas [Batch!1!id],NULLas [Sequence!2!id],NULLas [Step!3!id],NULLas [Device!4!DeviceName]FROM BatchDeviceMappingWHERE BatchID= 32
UNION
SELECT 2as Tag, 1as Parent, BatchID, SequenceID,NULL,NULLFROM BatchDeviceMappingWHERE batchID= 32
UNION
SELECT 3as Tag, 2as Parent, BatchID, SequenceID, StepID,NULLFROM BatchDeviceMappingWHERE batchID= 32
UNION
SELECT 4as Tag, 3as Parent, BatchID, SequenceID, StepID, Device.DeviceNameFROM BatchDeviceMapping
JOIN Device Deviceon BatchDeviceMapping.Deviceid= device.deviceidWHERE batchID= 32Orderby 3,4,5,6FORXMLEXPLICIT)
Its always erroring with the following information
"Msg 1086, Level 15, State 1, Line 16
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."Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.
Any help would be appreciated.
Thanks,
Loonysan
[4720 byte] By [
loonysan] at [2007-12-25]
See if this works
DECLARE @outputXML as XML
set @outputXML =
(
select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from
(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping) as A
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
VJ8 at 2007-10-8 >
Thanks... This modified query from yr logic works..
DECLARE @outputXML asXML
set @outputXML =
(
select
TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from
(
SELECT
1 as
TAG,NULLas
PARENT, BatchID as
[Batch!1!id],NULLas
[Sequence!2!id],NULLas
[Step!3!id],NULLas
[Device!4!DeviceName]FROM
BatchDeviceMapping WHERE
BatchID = 32
UNION
SELECT
2 as
Tag, 1 as
Parent, BatchID, SequenceID,NULL,NULLFROM
BatchDeviceMapping WHERE
batchID = 32
UNION
SELECT
3 as
Tag, 2 as
Parent, BatchID, SequenceID, StepID,NULLFROM
BatchDeviceMapping WHERE
batchID = 32
UNION
SELECT
4 as
Tag, 3 as
Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM
BatchDeviceMapping
JOIN Device Device on
BatchDeviceMapping.Deviceid = device.deviceid where
batchid = 32)as
A
Orderby
3,4,5,6 FORXMLEXPLICIT
)
SELECT
@outputXMLThanks,
Loonysan
Hi,
I'm looking for a similar solution but for SQL Server 2000. Any idea ?
(The error in SQL2000 is "Incorrect syntax near 'XML'.")
Thanks
Sylvain
Can you give more details? The problem was not a specific one to SQL 2K5.
VJ8 at 2007-10-8 >
I am using SQL 2005.
I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:
SELECT
@auditXML = (SELECT ObjectId, ObjectType, ParentIdFROM @PermissibleChildren AS Children WHERE ObjectType = 2
UNION
SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId
FROM @PermissibleChildren AS Children
GROUP BY ObjectType, ParentId
HAVING ObjectType = 3
FOR XML AUTO, TYPE)
It gives me this error
Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312
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 have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.
Can somebody please help? I am stuck mid-way.
Thankyou,
Umaima
Try this:
SELECT @auditXML =(select objectid,ObjectType,ParentId from(SELECT ObjectId, ObjectType, ParentId
FROM @PermissibleChildren AS Children WHERE ObjectType = 2
UNION
SELECT [ObjectId] =MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId
FROM @PermissibleChildren AS Children
GROUPBY ObjectType, ParentId
HAVING ObjectType = 3
)
as
A FORXMLAUTO
,TYPE
) VJ8 at 2007-10-8 >
This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement
Thanks
I had the exact same error. I solved it in the same way as you did , but my problem is that the query doesnt always run as i expect it to. Every so often Sql server throughs me back this error.
The original query worked without the error. All I did was wrap it in another select, exactly is done in the solution on this thread
6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
Does anybody know why that would be the case.
Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.
VJ8 at 2007-10-8 >
You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.
