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

Transact-SQL (2000)

FOR XML EXPLICIT

Author

Topic

Wim
Starting Member

3 Posts

Posted - 2009-03-13 : 07:04:15

Hi guys,
This is a problem that I can't get fixed.

I managed to get this FOR XML EXPLICIT code nearly do what I want it to do.[CODE]SELECT 1 as Tag,
NULL as Parent,
'http://somewhere.com' as [Trainings!1!xmlns],
NULL as [Id!3!IdOwner],
NULL as [Id!3!IdValue!element],
NULL as [Training!2!Status!element],
NULL as [Training!2!Organism!element],
NULL as [Training!2!Label!element],

UNION ALL

SELECT 2 as Tag,
1 as Parent,
NULL as [Trainings!1!xmlns],
NULL as [Id!3!IdOwner],
NULL as [Id!3!IdValue!element],
CASE WHEN GetDate() < MyTable.d_first THEN 'Active' ELSE 'Inactive' END as [Training!2!Status!element],
MyTable.centre as [Training!2!Organism!element],
MyTable.desciption as [Training!2!Label!element],
FROM MyTable

UNION ALL

SELECT 3 as Tag,
2 as Parent,
NULL as [Trainings!1!xmlns],
'ourcompany' as [Id!3!IdOwner],
MyTable.code as [Id!3!IdValue!element],
NULL as [Training!2!Status!element],
NULL as [Training!2!Organism!element],
NULL as [Training!2!Label!element],
FROM MyTable

ORDER BY [Training!2!IdValue!element], tag
FOR XML EXPLICIT[/CODE]

This is what I get now:[CODE]<Trainings xmlns="http://somewhere.around">
<Training>
<Status>Active</Status>
<Organism>our organisation</Organism>
<Label>Hygiene procedures</Label>
<Id IdOwner="ourcompany">
<IdValue>521</IdValue>
</Id>
</Training>
...
</Trainings>[/CODE]
But it should be:
[CODE]<Trainings xmlns="http://somewhere.around">
<Training>
<Id IdOwner="ourcompany">
<IdValue>521</IdValue>
</Id>
<Status>Active</Status>
<Organism><![CDATA[our organisation]]></Organism>
<Label><![CDATA[Hygiene procedures]]></Label>
</Training>
...
</Trainings>[/CODE]
The things that should be different are:
- All text fields should be put between <![CDATA[ ...]]>
- and the <Id IdOwner="ourcompany"> element should come as the first element within the <Training> element.

I tried using [Training!2!Organism!cdata] but the result I got then is not what it should be.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 07:06:13

Hard to tell without proper sample data to try on.

E 12°55'05.63"
N 56°04'39.26"

Wim
Starting Member

3 Posts

Posted - 2009-03-13 : 11:39:53

Hope this helps.

create table MyTable(
	id integer	identity (1,1) not null,
	code varchar(10) not null,
	d_first	datetime not null,
	centre varchar(50) not null,
	desciption varchar(1000) not null,
	constraint pk_myTable primary key (id)
)

INSERT INTO MyTable (code, d_first, centre, desciption) VALUES('521', '2010-01-01', 'our organisation', 'Hygiene procedures')

select * from mytable

SELECT	1 as Tag,
	NULL as Parent,
	'http://somewhere.com' as [Trainings!1!xmlns],
	NULL as [Id!3!IdOwner],
	NULL as [Id!3!IdValue!element],
	NULL as [Training!2!Status!element],
	NULL as [Training!2!Organism!element],
	NULL as [Training!2!Label!element]

UNION ALL

SELECT	2 as Tag,
	1 as Parent,
	NULL as [Trainings!1!xmlns],
	NULL as [Id!3!IdOwner],
	NULL as [Id!3!IdValue!element],
	CASE WHEN GetDate() < MyTable.d_first THEN 'Active' ELSE 'Inactive' END as [Training!2!Status!element],
	MyTable.centre as [Training!2!Organism!element],
	MyTable.desciption as [Training!2!Label!element]
FROM	MyTable

UNION ALL

SELECT	3 as Tag,
	2 as Parent,
	NULL as [Trainings!1!xmlns],
	'ourcompany' as [Id!3!IdOwner],
	MyTable.code as [Id!3!IdValue!element],
	NULL as [Training!2!Status!element],
	NULL as [Training!2!Organism!element],
	NULL as [Training!2!Label!element]
FROM	MyTable

ORDER BY tag
FOR XML EXPLICIT

DROP table MyTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources