"for xml raw, element" output format

This query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml raw('theme'), elements
gives me this output:
<theme>
<themekey>airborne geophysical survey</themekey>
</theme>
<theme>
<themekey>airborne geophysics</themekey>
</theme>
<theme>
<themekey>diamonds</themekey>
</theme>
<theme>
<themekey>drilling</themekey>
</theme>
<theme>
<themekey>geochemical survey</themekey>
</theme>
<theme>
<themekey>geology</themekey>
</theme>
How do I get the format to look as follows:
<theme>
<themekey>airborne geophysical survey</themekey>
<themekey>airborne geophysics</themekey>
<themekey>diamonds</themekey>
<themekey>drilling</themekey>
<themekey>geochemical survey</themekey>
<themekey>geology</themekey>
</theme>
Thanks,
Lee AnneYou did not mention the version of SQL Server (is it 2000 or 2005).
In SQL Server 2005 you can query:
select theme_keyword as [themekey]
from amd_theme_keywords
where metadata_id = 3000
for xml path(''), root('theme')
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Ok, that's SQL Server 2005 :-) I should look at given query more carefully
:-)
Regards
Pawel Potasinski
Uytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa w
wiadomoci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
> This query:
> select theme_keyword as [themekey]
> from amd_theme_keywords
> where metadata_id = 3000
> for xml raw('theme'), elements
> gives me this output:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> </theme>
> <theme>
> <themekey>airborne geophysics</themekey>
> </theme>
> <theme>
> <themekey>diamonds</themekey>
> </theme>
> <theme>
> <themekey>drilling</themekey>
> </theme>
> <theme>
> <themekey>geochemical survey</themekey>
> </theme>
> <theme>
> <themekey>geology</themekey>
> </theme>
> How do I get the format to look as follows:
> <theme>
> <themekey>airborne geophysical survey</themekey>
> <themekey>airborne geophysics</themekey>
> <themekey>diamonds</themekey>
> <themekey>drilling</themekey>
> <themekey>geochemical survey</themekey>
> <themekey>geology</themekey>
> </theme>
> Thanks,
> Lee Anne|||Thank you, that was exactly what I needed!!!
Lee Anne
"Pawel Potasinski" wrote:

> Ok, that's SQL Server 2005 :-) I should look at given query more carefully
> :-)
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Lee Anne" <LeeAnne@.discussions.microsoft.com> napisa3 w
> wiadomo?ci news:3329A8B1-90EF-472E-BCD5-174EE2B49390@.microsoft.com...
>
>