SQL Server 'FOR XML' getting value into "row" element node

Question

This is another 'for xml' question, but I'm unsure whether this can be done without explicit mode. If it can't, then I'll just have to live with it.

The following is my select statement currently:

    SELECT
        [stream_id] as '@stream_id',
        [path_locator] as '@path_locator',
        [parent_path_locator] as '@parent_path_locatr',
        [file_type] as '@file_type',
        [cached_file_size] as '@cached_file_size',
        [creation_time] as '@creation_time',
        [last_write_time] as '@last_write_time',
        [last_access_time] as '@last_access_time',
        [is_directory] as '@is_directory',
        [is_offline] as '@is_offline',
        [is_hidden] as '@is_hidden',
        [is_readonly] as '@is_readonly',
        [is_archive] as '@is_archive',
        [is_system] as '@is_system',
        [is_temporary] as '@is_temporary',
        [name] as '/name',
        dbo.udf_GetChildren(path_locator)
    FROM @Merged
    WHERE path_locator.GetLevel() = 1
    FOR XML PATH'file'), ROOT('files'), TYPE

This outputs the following xml:

 <files>
   <file stream_id="" etc...>
      <name>NAME</name>
   </file>
 </files>

This isn't bad, but what I'd really like is to get the name element's value as the value of the file element. This is such a simple task I assume it can be done without explicit mode, but I've been wrong pretty often about such things.

I've tried using the '/' marker, but this doesn't seem to have the effect I want (for example Update XML node (in an XML column) in SQL Server 2005 with another column value in the same row).

edit: The desired xml would be simply this:

 <files>
   <file stream_id="" etc...>NAME</file>
 </files>

Many thanks for the help.

Answer 1

Use name as '*'

Columns with a Name Specified as a Wildcard Character