FOR XML AUTO, ELEMENTS result into a varchar or nText

• George Thomas-241216

  SSC Enthusiast

  Points: 121

  More actions

  June 27, 2005 at 10:32 pm

  #164995

  Dear All

  I have a query something like this in a SP

  SELECT

  @p_USER_ID AS USER_ID,

  PROCESS_LINK_ID,

  @p_PROJECT_NAME AS PROJECT_NAME,

  REFER_ITEM,

  OCCURRED_TIME,

  PROCESSED_FLAG

  FROM

  TBL_ALERTS AS ALERT

  WHERE

  ALERT_ID =1

  FOR XML AUTO, ELEMENTS

  I am using SQL Server 2000

  Is there anyway by which I could get the result of it in a varchar or nText variable....

  Any help is welcome.I am really in a perplexed situation. Pls HELP !!!!

  I am looking for something equal to :

  Declare @XML XML

  SET @XML = ( Select * from pubs..authors Authors FOR XML AUTO,ELEMENTS )

  Select @XML

  which is said to be available in SQL SERVER 2005

  Thanking in advance

  George

• philcart

  SSC-Forever

  Points: 47795

  More actions

  June 28, 2005 at 1:24 am

  #568831

  You can use OPENROWSET to get the xml data into a variable.

  EG:

  DECLARE @var Varchar(8000) 

  SELECT @var = CAST (a.[XML_F52E2B61-18A1-11d1-B105-00805F49916B] as VARCHAR(8000)) 
  FROM OPENROWSET('MSDASQL', 
     'DRIVER={SQL Server};SERVER=<YOUR_SERVER>;UID=<USERID>;PWD=<PASSWORD>', 
     'Select * from pubs..authors Authors FOR XML AUTO, ELEMENTS') AS a 

  PRINT @var 

  The only problem is that you're still limited to 8000 chars until SQL Server 2005 comes out

   

  --------------------
  Colt 45 - the original point and click interface

• DanKennedy

  Hall of Fame

  Points: 3736

  More actions

  June 29, 2005 at 2:45 am

  #569239

  I like phillcart's answer a lot. Very neat, however see my reply (certainly no where near as neat) to your other identical post (try not to cross-post) if you really need more than 8000 characters returned.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=194568

  ---

  Dan
  www.firstcs.co.uk