including "dummy" element

SQL 2005
I use the following query:
SELECT
(
SELECT theme_keyword AS [themekey]
FROM amd_theme_keywords
WHERE amd_theme_keywords.metadata_id = amd_identifications.metadata_id
FOR XML PATH(''), ROOT('theme'), TYPE
)
FROM amd_identifications
WHERE amd_identifications.metadata_id = 4002
FOR XML RAW('keywords'), ELEMENTS, TYPE
to generate the following xml snippet:
<keywords>
<theme>
<themekey>basement geology</themekey>
<themekey>bedrock geology</themekey>
<themekey>geology</themekey>
<themekey>gis data</themekey>
</theme>
</keywords>
I need to add <themekt>None</themekt> as an element at the same level as
<themekey>. There is no field in the database that I can query for this
information, so it will just be a static line. If I add 'None' AS [themekt]
to the select statement, it repeats multiple times, but it should only appear
once before the first <themekey>:
<keywords>
<theme>
<themekt>None</themekt>
<themekey>basement geology</themekey>
<themekt>None</themekt>
<themekey>bedrock geology</themekey>
<themekt>None</themekt>
<themekey>geology</themekey>
<themekt>None</themekt>
<themekey>gis data</themekey>
</theme>
</keywords>
I tried added it alone in its own subquery, but then the block of
<themekey>s repeated four times.
Thanks,
Lee Anne
Hello Lee,
You just need to nest it all
select name
,( SELECT 'None' themekt
, (SELECT top 10 name from syscolumns for xml raw('themekey'),
type)
for xml raw('theme'),elements,type)
from sysdatabases where dbid = 1
for xml raw('keywords')
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> SQL 2005
> I use the following query:
> SELECT
> (
> SELECT theme_keyword AS [themekey]
> FROM amd_theme_keywords
> WHERE amd_theme_keywords.metadata_id =
> amd_identifications.metadata_id
> FOR XML PATH(''), ROOT('theme'), TYPE
> )
> FROM amd_identifications
> WHERE amd_identifications.metadata_id = 4002
> FOR XML RAW('keywords'), ELEMENTS, TYPE
> to generate the following xml snippet:
> <keywords>
> <theme>
> <themekey>basement geology</themekey>
> <themekey>bedrock geology</themekey>
> <themekey>geology</themekey>
> <themekey>gis data</themekey>
> </theme>
> </keywords>
> I need to add <themekt>None</themekt> as an element at the same level
> as <themekey>. There is no field in the database that I can query for
> this information, so it will just be a static line. If I add 'None' AS
> [themekt] to the select statement, it repeats multiple times, but it
> should only appear once before the first <themekey>:
> <keywords>
> <theme>
> <themekt>None</themekt>
> <themekey>basement geology</themekey>
> <themekt>None</themekt>
> <themekey>bedrock geology</themekey>
> <themekt>None</themekt>
> <themekey>geology</themekey>
> <themekt>None</themekt>
> <themekey>gis data</themekey>
> </theme>
> </keywords>
> I tried added it alone in its own subquery, but then the block of
> <themekey>s repeated four times.
> Thanks,
> Lee Anne
|||Or another way without the extra nesting level:
SELECT
(
SELECT
N'None' AS [themekt],
theme_keyword AS [themekey]
FROM amd_theme_keywords
WHERE amd_theme_keywords.metadata_id = amd_identifications.metadata_id
FOR XML PATH(''), ROOT('theme'), TYPE
)
FROM amd_identifications
WHERE amd_identifications.metadata_id = 4002
FOR XML RAW('keywords'), ELEMENTS, TYPE
Regards,
Eugene
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:7e76d2857729c8c9985f68c19ee0@.msnews.microsoft .com...
> Hello Lee,
> You just need to nest it all
>
> select name
> ,( SELECT 'None' themekt
> , (SELECT top 10 name from syscolumns for xml
> raw('themekey'), type)
> for xml raw('theme'),elements,type)
> from sysdatabases where dbid = 1
> for xml raw('keywords')
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>