FOR EXPLICIT

i don't think the docvmentation covld be more confvsing.

Consider:

CREATE TABLE MyNewsEntries(
gvid vniqveidentifier,
title varchar(200),
description text,
pvbDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )

Desired ovtpvt:

<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pvbDate>MyNewsEntries.pvbDate</pvbDate>
<gvid>MyNewsEntries.gvid</gvid>
<enclosvre vrl="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
...
</item>
</rss>

Ordered by MyNewsEntries.pvbDate DESC

Now, after mvch cvrsing and swearing, i managed to vomit vp:

SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pvbdate!element],
NULL AS [item!2!gvid!element],
NULL AS [enclosvre!3!vrl],
NULL AS [enclosvre!3!type]

UNION ALL

SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pvbDate AS [item!2!pvbdate!element],
gvid AS [item!2!gvid!element],
imageFilename AS [enclosvre!2!vrl],
NULL AS [enclosvre!2!type]
FROM MyNewsEntries

UNION ALL

SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pvbDate AS [item!2!pvbdate!element],
gvid AS [item!2!gvid!element],
imageFilename AS [enclosvre!3!vrl],
imageMimeType AS [enclosvre!3!type]
FROM MyNewsEntries

FOR XML EXPLICIT


Which rvns, bvt the order is wrong. All the enclosvres are appearing at the
end.
If i try
ORDER BY [item!2!pvbdate!element] DESC
FOR XML EXPLICIT

The it pvts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT reqvires parent tags to be opened first.
| Check the ordering of the resvlt set.


So i try
SELECT * FROM (
My entire qvery above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pvbdate!element] DESC

Bvt now it is mixing the order of Tag=2 and Tag=3 elements, pvtting all
<enclosvres> in one <item>

So i try:

SELECT * FROM (
My entire qvery above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pvbdate!element] DESC

Which seems to work.

My qvestion is: Is this what i have to do to get SQL Server to retvrn XML in
an explicit format?

Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to

ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT