Merging FOR XML PATH Result

Question

I want to output the following result:

-- 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') 

Therefore, and not the XML_GUID column heading, I want to give it the alias "Test" for the argument. I don't seem to understand. Does anyone know how? I tried the following example: http://social.msdn.microsoft.com/Forums/nl/sqlxml/thread/1605c722-6388-40ff-9ab5-a3817a1db81f , but I can not get it to return. I always run into an error that says there is no name for column 1.

Any help was appreciated.

Thanks,

Simon

Answer 1

Make a subquery:

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

Subqueries produce values, but never provide a name for the column. I also pointed out ,TYPE , because otherwise it will convert to varchar(max) result, whereas you apparently want to save it as xml .