This is driving me insane. I have a perfectly happy FOR XML EXPLICIT query that works beautifully... except it returns the results in wrong order. The project is a personals type site, and the intent here is to return an XML doc with all of the countries (and states, if applicable) in a region, ordered by countries with the most profiles first.The whole thing works fine with the default (ASC) ordering for [country!2!personals_count!element], but putting in DESC breaks it with the error "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."The code:select 1 as Tag, NULL as Parent, regions.i as [region!1!region_id], regions.name as [region!1!region_name!element], regions.personals_count as [region!1!personals_count!element], NULL as [country!2!country_id], NULL as [country!2!country_name!element], NULL as [country!2!personals_count!element], NULL as [state!3!state_id], NULL as [state!3!state_name!element], NULL as [state!3!personals_count!element]from regionswhere regions.[name]='Europe / UK'union allselect 2 as Tag, 1 as Parent, regions.i as [region!1!region_id], regions.name as [region!1!region_name!element], regions.personals_count as [region!1!personals_count!element], countries.i as [country!2!country_id], countries.name as [country!2!country_name!element], countries.personals_count as [country!2!personals_count!element], NULL as [state!3!state_id], NULL as [state!3!state_name!element], NULL as [state!3!personals_count!element]from countries join regions_countries on regions_countries.i_countries=countries.i join regions on regions_countries.i_regions=regions.iwhere regions.name='Europe / UK'union allselect 3 as Tag, 2 as Parent, regions.i as [region!1!region_id], regions.name as [region!1!region_name!element], regions.personals_count as [region!1!personals_count!element], countries.i as [country!2!country_id], countries.name as [country!2!country_name!element], countries.personals_count as [country!2!personals_count!element], states.i as [state!3!state_id], states.[name] as [state!3!state_name!element], states.personals_count as [state!3!personals_count!element]from states join countries_states on countries_states.i_states=states.i join countries on countries_states.i_countries=countries.i join regions_countries on regions_countries.i_countries=countries.i join regions on regions_countries.i_regions=regions.iwhere regions.name='Europe / UK'order by [region!1!region_id],[country!2!personals_count!element],[state!3!state_id]FOR XML EXPLICIT
That will work; changing the order by to read "order by [region!1!region_id],[country!2!personals_count!element] desc,[state!3!state_id]" will break it.Heeeeeeelp!Thanks-b
for xml explicit: DESC ordering breaks it?
Posted - 2005-11-10 : 14:20:57
