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.

@MikaelEriksson -- see my answer -- * should work too though... — sgeddes
– sgeddes, Commented Feb 1, 2013 at 20:39
@sgeddes yep, make the column name go away and the data end up in the "current" instead of creating a new sub node. — Mikael Eriksson
– Mikael Eriksson, Commented Feb 1, 2013 at 20:47

Mikael Eriksson · Accepted Answer · 2013-02-01 20:52:18Z

3

Use name as '*'

Columns with a Name Specified as a Wildcard Character

answered Feb 1, 2013 at 20:52

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mikael Eriksson Over a year ago

@Adam there are many ways to skin a cat. All answers here will do the job so +1 to all of you :)

sgeddes · Accepted Answer · 2013-02-01 20:43:15Z

I think you have to remove the Column Name from the field -- I was able to do this with concatonating a blank space to the end. Here is a condensed version that you should be able to get to work with your above example:

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

And here is the SQL Fiddle.

And a good article about it:

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

Here it is with your above query:

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

Good luck.

Adam Wenger · Accepted Answer · 2013-02-01 20:40:56Z

1

I moved the file node to each column:

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;

answered Feb 1, 2013 at 20:40

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related