Geek asks and answers

SQL Server "FOR XML" gets value into the "row" node element

This is another "for xml" question, but I'm not sure if this can be done without explicit mode. If this fails, then I just have to live with him.

Below is my select statement:

    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 is not bad, but I would really like to get the value of the name element as the value of the file element . This is such a simple task, I assume that it can be done without an explicit mode, but I often made mistakes in such things.

I tried using the '/' token, but this does not seem to have any effect (e.g. Update XML node (in an XML column) in SQL Server 2005 with a different column value on the same row ).

xml :

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

.

+5
3

name as '*'

,

+3

, - , . , :

SELECT name + ''
FROM TestXML
FOR XML PATH('file')

SQL Fiddle.

:

http://msdn.microsoft.com/en-us/library/bb510469.aspx

:

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] + '',
    dbo.udf_GetChildren(path_locator)
FROM @Merged
WHERE path_locator.GetLevel() = 1
FOR XML PATH'file'), ROOT('files'), TYPE

.

+2

file node :

SELECT [stream_id] AS 'file/@stream_id'
   , [is_system] AS 'file/@is_system'
   , [is_temporary] AS 'file/@is_temporary'
   ...
   , [name] AS 'file'
FROM Merged
FOR XML PATH('files'), TYPE;
+1

More articles:


All Articles