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 @.outputXML as XML

SET @.outputXML = (
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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
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

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)

|||

Thanks... This modified query from yr logic 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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
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.|||

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, ParentId

FROM @.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

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

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

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 @.outputXML as XML

SET @.outputXML = (
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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
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

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)

|||

Thanks... This modified query from yr logic 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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
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.|||

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, ParentId

FROM @.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

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

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

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 @.outputXML as XML

SET @.outputXML = (
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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
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

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)

|||

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 @.outputXML

Thanks,
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.|||

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, ParentId

FROM @.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)

|||

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

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 @.outputXML as XML

SET @.outputXML = (
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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
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

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)

|||

Thanks... This modified query from yr logic 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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
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.|||

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, ParentId

FROM @.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

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

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

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 @.outputXML as XML

SET @.outputXML = (
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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
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

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)

|||

Thanks... This modified query from yr logic 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
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
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.|||

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, ParentId

FROM @.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

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

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

need xsi:nil="true" in xml output using SQL 2000

Hi,
I have a stored Procedure that returns XML using FOR XML Explicit clause.
The xml is then validated (by appending header etc. in a c# application)
using a XSD schema.
The problem I am facing is that if I leave date and numeric fields as NULL
the schema validation fails.
How can I set the output to contain the xsi:nil="true" attribute?
Somewhere on this forum I saw that this can be achieved using FOR XML
ELEMENTS XSINIL, but on sql 2005.
How can I achieve this on sQL 2000?
Two ways:
1. add the necessary columns to your FOR XML EXPLICIT query to generate the
xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
the element.
E.g.,
select 1 as tag, 0 as parent,
CustomerID as "Customer!1!id",
'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
NULL as "Region!2!xsi:nil",
NULL as "Region!2!"
from Customers
union all
select 2 as tag, 1 as parent,
CustomerID,
NULL,
CASE WHEN Region is NULL THEN
'true'
ELSE
'false'
END,
Region
from Customers
order by "Customer!1!id"
for xml explicit
or (of course recommended ;-))
2. upgrade to SQL Server 2005.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
> Hi,
> I have a stored Procedure that returns XML using FOR XML Explicit clause.
> The xml is then validated (by appending header etc. in a c# application)
> using a XSD schema.
> The problem I am facing is that if I leave date and numeric fields as NULL
> the schema validation fails.
> How can I set the output to contain the xsi:nil="true" attribute?
> Somewhere on this forum I saw that this can be achieved using FOR XML
> ELEMENTS XSINIL, but on sql 2005.
> How can I achieve this on sQL 2000?
>
|||Is there an easier way to do this in the C# code once i have the XML result
from the stored procedure?
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate the
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>
|||Depends on what you consider easy. You would have to scan through the XML
and identify missing elements or elements with an empty or specially marked
content and change them... you could do it using an XSLT transform or some
C# code. I would consider that more complex, but then I am used to a
declarative way of generating data.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:26970D6F-D704-4DD9-A43F-39A8CCE77FBA@.microsoft.com...[vbcol=seagreen]
> Is there an easier way to do this in the C# code once i have the XML
> result
> from the stored procedure?
> "Michael Rys [MSFT]" wrote:
|||Michael,
I have been at this thing for hours but can't figure out how to Query the
following table, [Customers] to result in the required XML (below):
[Customers]
ID RegionName
CustomerName
__________________________________________________ ____________
1 North
Nabeel
1 NULL
Nabeel
2 North
NULL
3 NULL
NULL
3 South
Nabeel
[XML OUTPUT]
<root>
<Customer>
<Id>1</Id>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>North</RegionName>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName xsi:nil="true"/>
</Region>
</Customer>
<Customer>
<Id>2</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName>North</RegionName>
</Region>
</Customer>
<Customer>
<Id>3</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName xsi:nil="true"/>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>South</RegionName>
</Region>
</Customer>
</root>
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate the
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>
|||Hi Nabeel, sorry for the late reply... I hope this is still useful.
Here is the explicit mode solution. Note that you need a uniquefier for the
Region ID so you get different region elements.
select 1 as tag, NULL as parent,
1 as "root!1!id!hide",
'http://www.w3.org/2001/XMLSchema-instance' as
"root!1!xmlns:xsi",
NULL as "Customer!2!Id!element",
NULL as "Region!3!dummy!hide",
NULL as "CustomerName!4!",
NULL as "CustomerName!4!xsi:nil",
NULL as "RegionName!5!",
NULL as "RegionName!5!xsi:nil"
union all
select 2 as tag, 1 as parent,
1, NULL, /*root*/
c1.ID, /*Customer*/
NULL, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from (select distinct ID from Customers) c1
union all
select 3 as tag, 2 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 4 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
CustomerName,
CASE WHEN CustomerName is NULL
THEN 'true'
ELSE NULL END, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 5 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
RegionName,
CASE WHEN RegionName is NULL
THEN 'true'
ELSE NULL END /*RegionName*/
from Customers
order by "root!1!id!hide", "Customer!2!Id!element", "Region!3!dummy!hide",
tag
for xml explicit
and here for people using SQL Server 2005, the much simpler FOR XML PATH.
select c1.ID as "Id",
(select CustomerName, RegionName
from Customers c2
where c2.ID=c1.ID
for xml path('Region'), type, elements xsinil)
from (select distinct ID from Customers) c1
for xml path('Customer'), root
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:A189B17C-814E-4BD1-8A73-B5BA304E2985@.microsoft.com...[vbcol=seagreen]
> Michael,
> I have been at this thing for hours but can't figure out how to Query the
> following table, [Customers] to result in the required XML (below):
> [Customers]
> ID RegionName
> CustomerName
> __________________________________________________ ____________
> 1 North
> Nabeel
> 1 NULL
> Nabeel
> 2 North
> NULL
> 3 NULL
> NULL
> 3 South
> Nabeel
>
> [XML OUTPUT]
>
> <root>
> <Customer>
> <Id>1</Id>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>North</RegionName>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName xsi:nil="true"/>
> </Region>
> </Customer>
> <Customer>
> <Id>2</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName>North</RegionName>
> </Region>
> </Customer>
> <Customer>
> <Id>3</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName xsi:nil="true"/>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>South</RegionName>
> </Region>
> </Customer>
> </root>
> "Michael Rys [MSFT]" wrote:

need xsi:nil="true" in xml output using SQL 2000

Hi,
I have a stored Procedure that returns XML using FOR XML Explicit clause.
The xml is then validated (by appending header etc. in a c# application)
using a XSD schema.
The problem I am facing is that if I leave date and numeric fields as NULL
the schema validation fails.
How can I set the output to contain the xsi:nil="true" attribute?
Somewhere on this forum I saw that this can be achieved using FOR XML
ELEMENTS XSINIL, but on sql 2005.
How can I achieve this on sQL 2000?Two ways:
1. add the necessary columns to your FOR XML EXPLICIT query to generate the
xmlns:xsi namespace declaration and the xsi:nil = true or false attribute on
the element.
E.g.,
select 1 as tag, 0 as parent,
CustomerID as "Customer!1!id",
'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
NULL as "Region!2!xsi:nil",
NULL as "Region!2!"
from Customers
union all
select 2 as tag, 1 as parent,
CustomerID,
NULL,
CASE WHEN Region is NULL THEN
'true'
ELSE
'false'
END,
Region
from Customers
order by "Customer!1!id"
for xml explicit
or (of course recommended ;-))
2. upgrade to SQL Server 2005.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
> Hi,
> I have a stored Procedure that returns XML using FOR XML Explicit clause.
> The xml is then validated (by appending header etc. in a c# application)
> using a XSD schema.
> The problem I am facing is that if I leave date and numeric fields as NULL
> the schema validation fails.
> How can I set the output to contain the xsi:nil="true" attribute?
> Somewhere on this forum I saw that this can be achieved using FOR XML
> ELEMENTS XSINIL, but on sql 2005.
> How can I achieve this on sQL 2000?
>|||Is there an easier way to do this in the C# code once i have the XML result
from the stored procedure?
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate th
e
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute
on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>|||Depends on what you consider easy. You would have to scan through the XML
and identify missing elements or elements with an empty or specially marked
content and change them... you could do it using an XSLT transform or some
C# code. I would consider that more complex, but then I am used to a
declarative way of generating data.
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:26970D6F-D704-4DD9-A43F-39A8CCE77FBA@.microsoft.com...
> Is there an easier way to do this in the C# code once i have the XML
> result
> from the stored procedure?
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I have been at this thing for hours but can't figure out how to Query the
following table, [Customers] to result in the required XML (below):
[Customers]
ID RegionName
CustomerName
________________________________________
______________________
1 North
Nabeel
1 NULL
Nabeel
2 North
NULL
3 NULL
NULL
3 South
Nabeel
[XML OUTPUT]
<root>
<Customer>
<Id>1</Id>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>North</RegionName>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName xsi:nil="true"/>
</Region>
</Customer>
<Customer>
<Id>2</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName>North</RegionName>
</Region>
</Customer>
<Customer>
<Id>3</Id>
<Region>
<CustomerName xsi:nil="true"/>
<RegionName xsi:nil="true"/>
</Region>
<Region>
<CustomerName>Nabeel</CustomerName>
<RegionName>South</RegionName>
</Region>
</Customer>
</root>
"Michael Rys [MSFT]" wrote:

> Two ways:
> 1. add the necessary columns to your FOR XML EXPLICIT query to generate th
e
> xmlns:xsi namespace declaration and the xsi:nil = true or false attribute
on
> the element.
> E.g.,
> select 1 as tag, 0 as parent,
> CustomerID as "Customer!1!id",
> 'http://www.w3.org/2001/XMLSchema-instance' as "Customer!1!xmlns:xsi",
> NULL as "Region!2!xsi:nil",
> NULL as "Region!2!"
> from Customers
> union all
> select 2 as tag, 1 as parent,
> CustomerID,
> NULL,
> CASE WHEN Region is NULL THEN
> 'true'
> ELSE
> 'false'
> END,
> Region
> from Customers
> order by "Customer!1!id"
> for xml explicit
>
>
> or (of course recommended ;-))
> 2. upgrade to SQL Server 2005.
> Best regards
> Michael
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:F2C7CF7F-2947-4398-AF86-952D71EBFCEF@.microsoft.com...
>
>|||Hi Nabeel, sorry for the late reply... I hope this is still useful.
Here is the explicit mode solution. Note that you need a uniquefier for the
Region ID so you get different region elements.
select 1 as tag, NULL as parent,
1 as "root!1!id!hide",
'http://www.w3.org/2001/XMLSchema-instance' as
"root!1!xmlns:xsi",
NULL as "Customer!2!Id!element",
NULL as "Region!3!dummy!hide",
NULL as "CustomerName!4!",
NULL as "CustomerName!4!xsi:nil",
NULL as "RegionName!5!",
NULL as "RegionName!5!xsi:nil"
union all
select 2 as tag, 1 as parent,
1, NULL, /*root*/
c1.ID, /*Customer*/
NULL, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from (select distinct ID from Customers) c1
union all
select 3 as tag, 2 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 4 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
CustomerName,
CASE WHEN CustomerName is NULL
THEN 'true'
ELSE NULL END, /*CustomerName*/
NULL, NULL /*RegionName*/
from Customers
union all
select 5 as tag, 3 as parent,
1, NULL, /*root*/
ID, /*Customer*/
CAST(ID as varchar(100))+
CASE WHEN RegionName IS NULL
THEN '**NULL**'
ELSE RegionName END, /*Region*/
NULL, NULL, /*CustomerName*/
RegionName,
CASE WHEN RegionName is NULL
THEN 'true'
ELSE NULL END /*RegionName*/
from Customers
order by "root!1!id!hide", "Customer!2!Id!element", "Region!3!dummy!hide",
tag
for xml explicit
and here for people using SQL Server 2005, the much simpler FOR XML PATH.
select c1.ID as "Id",
(select CustomerName, RegionName
from Customers c2
where c2.ID=c1.ID
for xml path('Region'), type, elements xsinil)
from (select distinct ID from Customers) c1
for xml path('Customer'), root
Best regards
Michael
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:A189B17C-814E-4BD1-8A73-B5BA304E2985@.microsoft.com...
> Michael,
> I have been at this thing for hours but can't figure out how to Query the
> following table, [Customers] to result in the required XML (below):
> [Customers]
> ID RegionName
> CustomerName
> ________________________________________
______________________
> 1 North
> Nabeel
> 1 NULL
> Nabeel
> 2 North
> NULL
> 3 NULL
> NULL
> 3 South
> Nabeel
>
> [XML OUTPUT]
>
> <root>
> <Customer>
> <Id>1</Id>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>North</RegionName>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName xsi:nil="true"/>
> </Region>
> </Customer>
> <Customer>
> <Id>2</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName>North</RegionName>
> </Region>
> </Customer>
> <Customer>
> <Id>3</Id>
> <Region>
> <CustomerName xsi:nil="true"/>
> <RegionName xsi:nil="true"/>
> </Region>
> <Region>
> <CustomerName>Nabeel</CustomerName>
> <RegionName>South</RegionName>
> </Region>
> </Customer>
> </root>
> "Michael Rys [MSFT]" wrote:
>