FOR XML AUTO, ELEMENTS result in a string

[geokumpuckal]

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

[Rawhide]

Nope. Can't be done. The XML output has to be returned tot he client as XML. Anything beyond that you may want to do with it must be done on the client.

[geokumpuckal]

After posting the query I now found a rather illogical method but still its possble as per the link:

http://www.extremeexperts.com/SQL/Yu...XMLOutput.aspx

I am loooking forward for　more options also..

Thanks
GEorge



Declare @var Varchar(8000)

SELECT @var = CAST (a.[XML_F52E2B61-18A1-11d1-B105-00805F49916B] as VARCHAR(8000))

FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=localhost;UID=sa;PWD=password',

'Select * from pubs..authors Authors FOR XML AUTO, ELEMENTS') AS a

Select @var [XMLOutput]