Aliasing a FOR XML PATH result

• s_osborne2

  SSCertifiable

  Points: 6726

  More actions

  January 17, 2013 at 5:03 am

  #287975

  Hi,

  I want to alias the output of the following:

  -- Test table with some rubbish data

  DECLARE @test-2 TABLE

  (

  Names [varchar](20)

  )

  INSERT INTO @test-2

  SELECT 'Simon'

  -- Query returns but with XML_<GUID> alias

  SELECT

  Names

  FROM

  @test-2 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

  ---

  
  MCSE: Data Platform
  MCSE: Business Intelligence
  Follow me on Twitter: @WazzTheBadger
  LinkedIn Profile: Simon Osborne

• rob_dalzell

  Old Hand

  Points: 315

  More actions

  January 17, 2013 at 6:07 am

  #1578271

  /* If you're using SQL2005+ you could try something this */

  ;

  WITH CTE ( Test )

  AS ( SELECT Names

  FROM @test-2 t

  FOR XML PATH('Test')

  )

  SELECT Test ,

  CAST(Test AS XML) AS Test

  FROM CTE

• Allen Kinsel

  SSC Veteran

  Points: 268

  More actions

  January 17, 2013 at 6:13 am

  #1578276

  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-2 TABLE

  (

  Names [varchar](20)

  )

  INSERT INTO @test-2

  SELECT 'Simon'

  -- Query returns but with XML_<GUID> alias

  SELECT (SELECT

  Names

  FROM

  @test-2 t

  FOR XML PATH ('Test') ) as Test

• s_osborne2

  SSCertifiable

  Points: 6726

  More actions

  January 17, 2013 at 6:16 am

  #1578278

  Worked an absolute treat! Thanks very much!!

  ---

  
  MCSE: Data Platform
  MCSE: Business Intelligence
  Follow me on Twitter: @WazzTheBadger
  LinkedIn Profile: Simon Osborne