CODEWITHSUNDEEP
sqlsql-servert-sql
Simon Osborne
Simon Osborne

Reputation: 113

Aliasing a FOR XML PATH result

I want to alias the output of the following:

-- Test table with some rubbish data
DECLARE @Test TABLE
(
    Names [varchar](20)
)
INSERT INTO @Test
SELECT 'Simon'

-- Query returns but with XML_<GUID> alias
SELECT 
    Names
FROM 
    @Test t 
FOR XML PATH ('Test')

So rather than a column header of XML_GUID I want to give it an alias of say 'Test' for sake of argument. I can't seem to get it. Anyone know how? I tried following an example from here: http://social.msdn.microsoft.com/Forums/nl/sqlxml/thread/1605c722-6388-40ff-9ab5-a3817a1db81f but I can't seem to get it to return. I always run into the error that says the is no name for column 1.

Any help appreciated.

Thanks,

Simon

Upvotes: 11

Views: 9501

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Make it a subquery:

select (
SELECT 
    Names
FROM 
    @Test t 
FOR XML PATH ('Test'),TYPE) as Test

Subqueries produce values but never provide a name for a column. I also specified ,TYPE because otherwise it forces a conversion to varchar(max) on the result, whereas you presumably want to keep it as xml.

Upvotes: 17

Related Questions