January 17, 2013 at 5:03 am
Hi,
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
January 17, 2013 at 6:07 am
/* If you're using SQL2005+ you could try something this */
;
WITH CTE ( Test )
AS ( SELECT Names
FROM @Test t
FOR XML PATH('Test')
)
SELECT Test ,
CAST(Test AS XML) AS Test
FROM CTE
January 17, 2013 at 6:13 am
This is the easiest way, though it may not work for what your actual query is but...
-- 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 (SELECT
Names
FROM
@Test t
FOR XML PATH ('Test') ) as Test
January 17, 2013 at 6:16 am
Worked an absolute treat! Thanks very much!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply