FOR XML Explicit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

FOR XML Explicit

Author

Topic

Torch
Starting Member

20 Posts

Posted - 2005-07-21 : 18:08:16

I have the following FOR XML EXPLICIT statement:

1 as Tag, null as Parent,
[StageID] as [Stage!1!StageID],
[Stage] as [Stage!1!Stage],
[Update] as [Stage!1!Update]
FROM Stage
FOR XML EXPLICIT

Which gives me results in the form:
<Stage StageID="1" Stage="Stage1" Update="True"/><Stage ......../>

However I want the results in the form:
<Stages><Stage StageID="1" Stage="Stage1" Update="True"/><Stage ......../></Stages>

i.e. with the extra "<Stages></Stages>" at the beginning and end of the xml. Im not sure if this is even possible?

Any help in this matter would be much appreciated.

Torch.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-22 : 05:02:09

Try this:

SELECT
	1 AS Tag, 
	NULL AS Parent, 
	NULL AS [Stage!1!StageID],
	NULL AS [Stage!1!Stage],
	NULL AS [Stage!1!Update]
UNION 
SELECT
	2 as Tag, 
	1 as Parent,
	[StageID] as [Stage!2!StageID],
	[Stage] as [Stage!2!Stage],
	[Update] as [Stage!2!Update]
FROM Stage
FOR XML EXPLICIT

Mark

Bunce
Starting Member

13 Posts

Posted - 2005-08-01 : 07:19:49

Hi Mark,

So where have you specified the <stages> tag?

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 08:46:58

Please see my suggestion in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53050

Kristen

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-01 : 11:06:30

Sorry, that lost something in translation from my data to Torch's data (and ended up as complete nonsense!) Kristen is absolutely right, it was meant to be:


SELECT 
	1 AS tag, 
	NULL AS parent, 
	NULL AS [Stages!1], 
	NULL AS [Stage!2!StageID],
	NULL AS [Stage!2!Stage],
	NULL AS [Stage!2!Update]

UNION 
SELECT
	2 AS tag, 
	1 AS parent,
	NULL AS [Stages!1], 
	s.[StageID] AS [Stage!2!StageID],
	s.[Stage] AS [Stage!2!Stage],
	s.[Update] AS [Stage!2!Update]
FROM 
	Stages AS s

FOR XML EXPLICIT

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources