locked
how to give name for database field (column)? RRS feed

  • Question

  • how i give fixed name for xml output field from below query?

    SELECT
    Product_ID as ProductId,
    Product_Name as ProductName

    FROM Products as Product  FOR XML AUTO, ELEMENTS, ROOT('Products')

    Tuesday, February 7, 2012 8:52 PM

Answers

  • set it to a xml variable and then select it, or use a column alias with a derived table.

    declare @t table(ProductId int, ProductName char(1))
    insert into @t values (1,'a');
    insert into @t values (2,'b');
    
    --like this
    SELECT CAST(x.MyCol AS XML)
    FROM(
    SELECT 
    ProductID as ProductId,
    ProductName as ProductName
    FROM @t as Product  FOR XML AUTO, ELEMENTS, ROOT('Products')
    ) as x(MyCol)
    
    --or like this
    declare @xml xml
    
    set @xml =(
    SELECT 
    ProductID as ProductId,
    ProductName as ProductName
    FROM @t as Product  FOR XML AUTO, ELEMENTS, ROOT('Products'))
    
    select @xml as MyCol


    http://jahaines.blogspot.com/

    • Marked as answer by Alex Q8 Tuesday, February 7, 2012 9:46 PM
    Tuesday, February 7, 2012 9:03 PM