FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
...
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
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!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!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],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!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],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| 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.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return 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