nesting limit exceeded...but I don't understand why.

I have written a recursive function that generates an XML hierarchy. I have gone through the data being selected and I have verified that the hierarchy is only 19 levels at its deepest. (there are several thousand records involved, and 1 level may have several hundred records). But, I am receiving this error:
"Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the function I have written:

ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(SELECT s.ID As "@.id",
s.TITLE as "@.title",
s.CLASS_ID as "@.clsID",
CASE WHEN s.PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(s.ID)
END
FROM SCENE s
WHERE s.PARENT_ID = @.SceneID
FOR XML PATH('Scene'), TYPE)
END

Does anyone have any suggestions as to why I may be getting this error, or how to debug for it or work around it?

Thank you for any advice you can give.

Do you have any records in which ID = PARENT_ID?

Run SELECT ID, PARENT_ID FROM SCENE WHERE ID = PARENT_ID and see.

If so, you might want to consider adding a check constraint to the table that would forbid such a circumstance. Another alternative might be to exit the function if the function detects ID = PARENT_ID.

|||

When I mockup with:

create table dbo.scene
( id integer,
parent_id integer,
class_id integer,
title varchar(20)
)
go

insert into dbo.scene values (1, null, 1, 'This is a test')
insert into dbo.scene values (2, 1, 1, 'Record #2')
insert into dbo.scene values (3, 2, 1, 'Record #3')

and run:

select dbo.fn_WPMTREE (1) as [the Scene]

I get:

-- the Scene
-- -
-- <Scene id="2" title="Record #2" clsID="1"><Scene id="3" title="Record #3" clsID="1" /></Scene>

Is this what you expect?

|||Absolutely brilliant.

I had a scene that had a PARENT_ID = ID. And, that caused the infinite loop. I thought it was a loop being created somewhere, but I thought it was of the type Scene1.Parent_ID = Scene2.ID; Scene2.Parent_ID = Scene1.ID.
But, it was even more direct than that.

Thanks so much!
Sincerely.
roger

Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
Tom
What are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom
|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags are
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly to
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper elements.
> Unless you need to provide group specific properties, I find these elements
> useless and they actually make processing of the documents more expensive in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>
|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:

Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
TomWhat are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags ar
e
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly t
o
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information
.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper element
s.
> Unless you need to provide group specific properties, I find these element
s
> useless and they actually make processing of the documents more expensive
in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:
>

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

nested XML output with several JOIN statements

First, thanks to the guys for helping me earlier just to get my XML output
into element form. I'm new to XML, so I appreciate all the help and hope I'm
learning.
My next question has to do with nesting the data that comes out as a result
of several JOIN's
Here is my code:
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
[Order].ID,
[Agent].TextID,
[Buyer].TextID,
[Broker].TextID
FROM
@.t AS Root
JOIN psOrder [Order] ON Root.id = 1
LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
WHERE [Order].ID = 12345
FOR XML AUTO, ELEMENTS
The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
However, the output I'm getting is nesting the data.
The output I get (incorrectly) is like this:
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
This is not correct. The Agent, Buyer, and Broker are all children of the
Order, and should not be nested within each other.
The desired output is like this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
I'd would very appreciate some help with this. Perhaps my SQL is not written
properly so that it comes out as desired.
ScottFor each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to get
the correct nesting. For example:
=====
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
authors.au_id, authors.au_lname, authors.au_fname,
titles.title_id, titles.title
FROM
@.t AS Root
JOIN authors ON Root.id = 1
JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
FOR XML AUTO, ELEMENTS
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> First, thanks to the guys for helping me earlier just to get my XML output
> into element form. I'm new to XML, so I appreciate all the help and hope
> I'm
> learning.
> My next question has to do with nesting the data that comes out as a
> result
> of several JOIN's
> Here is my code:
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> [Order].ID,
> [Agent].TextID,
> [Buyer].TextID,
> [Broker].TextID
> FROM
> @.t AS Root
> JOIN psOrder [Order] ON Root.id = 1
> LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
> LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
> LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
> WHERE [Order].ID = 12345
> FOR XML AUTO, ELEMENTS
> The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
> However, the output I'm getting is nesting the data.
> The output I get (incorrectly) is like this:
> <Order>
> <Agent>
> <Buyer>
> <Broker>
> </Broker>
> </Buyer>
> </Agent>
> </Order>
> This is not correct. The Agent, Buyer, and Broker are all children of the
> Order, and should not be nested within each other.
> The desired output is like this:
> <Order>
> <Agent>
> </Agent>
> <Buyer>
> </Buyer>
> <Broker>
> </Broker>
> </Order>
> I'd would very appreciate some help with this. Perhaps my SQL is not
> written
> properly so that it comes out as desired.
> Scott
>|||Thanks for the reply SriSamp. I've added the " AND Root.id = 1" at the end
of each LEFT JOIN, but I'm still getting the nesting problem.
The data is still coming out nested like this (incorrectly):
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
Just to confirm, I do not want the child table data to nest one within each
other.
The desired output is this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OWdNAvA8FHA.1000@.tk2msftngp13.phx.gbl...
> For each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to
get
> the correct nesting. For example:
> =====
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> authors.au_id, authors.au_lname, authors.au_fname,
> titles.title_id, titles.title
> FROM
> @.t AS Root
> JOIN authors ON Root.id = 1
> JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
> JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
> FOR XML AUTO, ELEMENTS
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
output
the
>

nested set model

Hi,

I am storing some hierarchical information using the nested set model. I would like to transform the data into XML to bind them to a TreeView object. Did someone do something similar before. Any input would be very much appreciated. Many thanks.

Christian

SQL Server 2000 or 2005?

Also you'll need to post some DDL to get any sort of answer

|||

-- An example for SQL Server 2005 only, may be helpful

CREATE TABLE MyTable(Name VARCHAR(10),lft INT,rgt INT)

INSERT INTO MyTable(Name,lft,rgt)
SELECT 'Top' , 1 , 12 UNION ALL
SELECT 'Level1A' ,2 , 3 UNION ALL
SELECT 'Level1B' ,4 , 11 UNION ALL
SELECT 'Level2A' ,5 , 6 UNION ALL
SELECT 'Level2B' ,7 , 8 UNION ALL
SELECT 'Level2C' ,9 , 10

GO
CREATE VIEW Adjacency
-- Converts the nested set to an adjacency list
AS
SELECT P.Name AS ParentName,
N.Name
FROM MyTable AS N
LEFT OUTER JOIN MyTable AS P ON P.lft = (SELECT MAX(S.lft)
FROM MyTable AS S
WHERE N.lft > S.lft
AND N.lft < S.rgt)
GO
CREATE FUNCTION dbo.SubTree(@.Name VARCHAR(10))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName=@.Name
ORDER BY Name
FOR XML PATH('Node'),TYPE)
END

GO

SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName IS NULL
ORDER BY Name
FOR XML PATH('Node'),ROOT('Nodes'),TYPE

|||

Thanks Mark. This helped. I got it all working fine.

Chris

nested set model

Hi,

I am storing some hierarchical information using the nested set model. I would like to transform the data into XML to bind them to a TreeView object. Did someone do something similar before. Any input would be very much appreciated. Many thanks.

Christian

SQL Server 2000 or 2005?

Also you'll need to post some DDL to get any sort of answer

|||

-- An example for SQL Server 2005 only, may be helpful

CREATE TABLE MyTable(Name VARCHAR(10),lft INT,rgt INT)

INSERT INTO MyTable(Name,lft,rgt)
SELECT 'Top' , 1 , 12 UNION ALL
SELECT 'Level1A' ,2 , 3 UNION ALL
SELECT 'Level1B' ,4 , 11 UNION ALL
SELECT 'Level2A' ,5 , 6 UNION ALL
SELECT 'Level2B' ,7 , 8 UNION ALL
SELECT 'Level2C' ,9 , 10

GO
CREATE VIEW Adjacency
-- Converts the nested set to an adjacency list
AS
SELECT P.Name AS ParentName,
N.Name
FROM MyTable AS N
LEFT OUTER JOIN MyTable AS P ON P.lft = (SELECT MAX(S.lft)
FROM MyTable AS S
WHERE N.lft > S.lft
AND N.lft < S.rgt)
GO
CREATE FUNCTION dbo.SubTree(@.Name VARCHAR(10))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName=@.Name
ORDER BY Name
FOR XML PATH('Node'),TYPE)
END

GO

SELECT Name as "@.Name",
dbo.SubTree(Name)
FROM Adjacency
WHERE ParentName IS NULL
ORDER BY Name
FOR XML PATH('Node'),ROOT('Nodes'),TYPE

|||

Thanks Mark. This helped. I got it all working fine.

Chris

Nested SELECT ... FOR XML PATH Return ESCAPED <>s

Hi,
I'm trying to build a complicated web service request using the (much better than FOR XML EXPLICIT) PATH mode, and it's great and all except that when I nest them I am getting &lt, &gt for the nested nodes. Here's a snippit:

BEGIN

SET NOCOUNT ON;

SELECT
'P' AS "Item/DataBlkInd",
'A' AS "Item/PhoneQual/EditTypeInd",
'CITYCODE' AS "Item/PhoneQual/AddPhoneQual/City",
...
(SELECT DISTINCT
'R' AS "DataBlkInd",
'A' AS "EmailQual/EditTypeInd",
'1' AS "EmailQual/LineNum",
'T' AS "EmailQual/Type",
bpe.EmailAddress AS "EmailQual/EmailData"
FROM dbo.BPEmail bpe WHERE bpe.BusPartyId = @.CustomerId
FOR XML PATH('Item')) AS "node()",
...
FOR XML PATH('ItemAry'), ROOT('PNRBFSecondaryBldChgMods')
END

Any idea why the nested FOR XML PATH would be escaped, and how to return it as XML instead of "&lt Item &gt &lt ..."?

Many thanks!
Andy

Hi Andy

FOR XML queries per default return the resulting XML as a string value for backwards-compatibility reasons (regardless of the mode). So you should say

FOR XML PATH('Item'), TYPE

if you need the result to be XML. Also, I think you then will not need the AS "node()". Just leave the column alias away.

Best regards
Michael

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

DECLARE @.x xml
BEGIN
WITH XMLNAMESPACES('one' AS ns1, 'two' AS ns2)
SELECT @.x = (
SELECT -1 "@.id",
'invalid' "@.status",
(SELECT 'false' "@.flag",
'etc' "@.comment"
FOR XML PATH ('ns2:inner'), TYPE)
FOR XML PATH('ns1:0uter'), TYPE);
SELECT @.x
END;

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

Is there an alternate way to declare a namespace in a nested query (in this case, move the namespace declaration for 'two AS ns2' out of the outer query into the nested select)? If not, then are there alternative ways to remove this extraneous stuff? This is a simple example, but real-world instances involving several levels of nesting these declarations are too much.

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

DECLARE @.x xml
BEGIN
WITH XMLNAMESPACES('one' AS ns1, 'two' AS ns2)
SELECT @.x = (
SELECT -1 "@.id",
'invalid' "@.status",
(SELECT 'false' "@.flag",
'etc' "@.comment"
FOR XML PATH ('ns2:inner'), TYPE)
FOR XML PATH('ns1:0uter'), TYPE);
SELECT @.x
END;

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

Is there an alternate way to declare a namespace in a nested query (in this case, move the namespace declaration for 'two AS ns2' out of the outer query into the nested select)? If not, then are there alternative ways to remove this extraneous stuff? This is a simple example, but real-world instances involving several levels of nesting these declarations are too much.

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

nested FOR XML queries / with namespaces

I want to assign the result of a FOR XML query containing nested queries to an xml variable. The problem is, the namespace declarations propagate down to every nested element generated in the query.

DECLARE @.x xml
BEGIN
WITH XMLNAMESPACES('one' AS ns1, 'two' AS ns2)
SELECT @.x = (
SELECT -1 "@.id",
'invalid' "@.status",
(SELECT 'false' "@.flag",
'etc' "@.comment"
FOR XML PATH ('ns2:inner'), TYPE)
FOR XML PATH('ns1:0uter'), TYPE);
SELECT @.x
END;

This is the XML that is generated:

<ns1:0uter xmlns:ns2="two" xmlns:ns1="one" id="-1" status="invalid">
<ns2:inner xmlns:ns2="two" xmlns:ns1="one" flag="false" comment="etc" />
</ns1:0uter>

Is there an alternate way to declare a namespace in a nested query (in this case, move the namespace declaration for 'two AS ns2' out of the outer query into the nested select)? If not, then are there alternative ways to remove this extraneous stuff? This is a simple example, but real-world instances involving several levels of nesting these declarations are too much.

I have the same question. I simply need to add

xmlns="urn:xxxx.yyyyy.zzzzz"

in the header of the XML only.

I review the MDSN details: http://msdn2.microsoft.com/en-us/library/ms177400.aspx but don't see how to accomplish this simple thing.

I can't even fake it out:

SELECT

'en-US' AS "MessageLanguage",

'2007-05-08T18:13:51.0Z' AS "IssueDate",

'urn:xxxx.yyyyy.zzzzz' AS "@.xmlns",

The select returns error:

'xmlns' is invalid in XML tag name in FOR XML PATH, or when WITH XMLNAMESPACES is used with FOR XML.

|||

I have the same problem with this query:

Code Snippet

declare @.category_name as varchar(512);
set @.category_name = 'Bolt';

with xmlnamespaces ('http://services.ihs.com/schemas/structured_content' as sc)

select id as [@.sc:id], 'ADD' as [@.sc:action], cat_id as [@.sc:categoryId],

(select attribute_id as [@.sc:attributeId], value as [data()]

from Item_Detail (nolock)

where item_id = Item.id

for xml path('sc:VALUE'), type)

from Item (nolock)

where cat_id = (select id from Category (nolock) where name = @.category_name)

for xml path('sc:ITEM'), root('sc:ITEMS')
;

Each of the 22 million nested sc:VALUE result elements unnecessarily rebind the namespace prefix:

Code Snippet

<sc:ITEMS xmlns:sc="http://services.ihs.com/schemas/structured_content">

<sc:ITEM sc:id="ABE9639D-3C7B-4E66-BF32-00001A294577" sc:action="ADD" sc:categoryId="68541B13-9C60-4D90-B694-68C28F346832">

<sc:VALUE xmlns:sc="http://services.ihs.com/schemas/structured_content" sc:attributeId="E9BD274C-34C2-44A1-8F54-6E5218C71A9A">STEEL ALLOY</sc:VALUE>

...

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

need to write from asp.net to XML type in SQL Server 2005

Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.

I have never used XML as a type in SQL Server 2005. How do we write xml into xml type.

For example the structure of XML is something like:

<application>

<applicationID = "value"></applicationID>

<customerName="value></customerName>

</application>

I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.

Kindly suggest. Thanks a lot.

You will need to look into several things to help you achieve this.

Look into the System.Xml.XmlTextWriter to help you construct XML valid strings

Also look into System.IO.StreamWriter to write the stream of data

You can then use methods of the XmlTextWriter to write the elements, attributes based on your XML structure; WriteStartDocument(), WriteStartElement(), WriteElementString();