Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aliasing a FOR XML PATH result Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 5:03 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:34 AM
Points: 703, Visits: 2,182
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1408346
Posted Thursday, January 17, 2013 6:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 65, Visits: 1,295
/* 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

Post #1408374
Posted Thursday, January 17, 2013 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:48 PM
Points: 6, Visits: 336
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




Post #1408379
Posted Thursday, January 17, 2013 6:16 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:34 AM
Points: 703, Visits: 2,182
Worked an absolute treat! Thanks very much!!



MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1408381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse