CODEWITHSUNDEEP
sqlsql-serverxmlalias
Sean_myers
Sean_myers

Reputation: 137

Name column alias from xml value stored in table

I am trying to name an alias based on an xml value in another table. The value is defined by the user, so therefore cannot be named statically.

XML from table (table1) and column (TimeBuckets):

<buckets>
  <bucket id="1" name="Overnight to 3 Mths">
    <lower>0</lower>
    <upper>3</upper>
  </bucket>
</buckets>

I want to set the alias of a column in a select statement based on the 'name' in table1.

I can select the name value using xml path as below:

table1.TimeBucket.value( '(buckets/bucket/@name)[1]', 'nvarchar(255)' )

I currently have the following SQL query, however it will not allow me to define the alias this way:

select 'test' as (table1.TimeBucket.value( '(buckets/bucket/@name)[1]', 'nvarchar(255)' )
from table1

Upvotes: 1

Views: 567

Answers (2)

Ruslan K.
Ruslan K.

Reputation: 1981

Because column names are resolved at compile time not at run time for the SQL statement, only dynamic sql can do this:

declare @columnName as varchar(255);
SELECT @columnName = TimeBucket.value( '(buckets/bucket/@name)[1]', 'nvarchar(255)' )
    FROM table1
    WHERE ID = 1; -- if you have more than one record in table1

declare @sql nvarchar(max);
set @sql = 'select ''test'' as [' + replace(@columnName, '''', '''''') + '] from table1;'

exec sp_executesql @sql;

Upvotes: 1

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

Two ways to do this:

declare @table1 table (TimeBucket xml)
insert into @table1
values('<buckets>
  <bucket id="1" name="Overnight to 3 Mths">
    <lower>0</lower>
    <upper>3</upper>
  </bucket>
</buckets>')

select test = t.TimeBucket.value( '(buckets/bucket/@name)[1]', 'nvarchar(255)' ),
              t.TimeBucket.value( '(buckets/bucket/@name)[1]', 'nvarchar(255)' ) as test
from @table1 t

Upvotes: 0

Related Questions