June 27, 2005 at 10:14 pm
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
June 27, 2005 at 11:01 pm
Sorry for posting the query in the below link its written that : In the present SQL Server 2000 world if we were to take the output of a FOR XML into a variable, it is next to impossible.
http://www.extremeexperts.com/SQL/Yukon/FORXMLOutput.aspx
If anybody has faced the same problem please share the experience(scenario) and how you get rid of it.
June 29, 2005 at 2:36 am
Here's a stored procedure I use to return the FOR XML output as a resultset. I can't take any credit for this as I found it on some forum about a year ago. It's since been posted on a number of different message boards so I think the original author has been lost somewhere. Thumbs up to him(or her) wherever they are though.
Hope it helps
EXAMPLE USAGE
EXEC usp_ForXmlToText 'SELECT TOP 10 * FROM sysobjects FOR XML AUTO'
CREATE PROCEDURE [dbo].[usp_ForXmlToText]
(
@sqlcommand ntext
)
AS
SET NOCOUNT ON
DECLARE
@rc int, -- the status code returned from calls to sp_OA
@objStream int, -- the ADO stream object
@objCommand int, -- the ADO command object
@errsrc varchar(4000),
@errdesc varchar(4000),
@size int,
@objProperty int, -- stream properties handle
@objOutputStream int, -- command output stream object
@xml nvarchar(4000),
@connstr varchar(1000),
@pos int,
@BUFF_SIZE int,
@ptrval binary(16)
SELECT @connstr = 'Data Source=' + @@SERVERNAME + '; Provider=SQLOLEDB; Initial Catalog=' + DB_NAME() + '; Integrated Security=SSPI;' ,
@pos = 0,
@BUFF_SIZE = 4000
CREATE TABLE #ret (XMLOutput ntext)
EXEC @rc = sp_OACreate 'ADODB.Stream', @objStream OUTPUT
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OACreate ADODB.Stream'
GOTO ERRORED
END
EXEC @rc = sp_OACreate 'ADODB.Command', @objCommand OUTPUT
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OACreate ADODB.Command'
GOTO ERRORED
END
EXEC @rc = sp_OASetProperty @objCommand, 'ActiveConnection', @connstr
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OASetProperty Command.ActiveConnection'
GOTO ERRORED
END
EXEC @rc = sp_OASetProperty @objCommand, 'CommandText', @sqlcommand
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OASetProperty Command.CommandText'
GOTO ERRORED
END
EXEC @rc = sp_OAMethod @objStream, 'Open'
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAMethod Stream.Open'
GOTO ERRORED
END
--Get a pointer to the properties collection
EXEC @rc = sp_OAGetProperty @objCommand, 'Properties', @objProperty OUTPUT
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAGetProperty Command.Properties'
GOTO ERRORED
END
-- Get a pointer to the "Output Stream" property
EXEC @rc = sp_OAMethod @objProperty, 'Item', @objOutputStream OUT, 'Output Stream'
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objProperty, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAMethod Properties.Item'
GOTO ERRORED
END
-- Set Property value
EXEC @rc = sp_OASetProperty @objOutputStream, 'Value', @objStream
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objOutputStream, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OASetProperty Stream.Value'
GOTO ERRORED
END
-- Invoke execute method
EXEC sp_OAMethod @objCommand, 'Execute', NULL, NULL, NULL, 1024
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objCommand, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAMethod Command.Execute'
GOTO ERRORED
END
-- Get output stream size
EXEC @rc = sp_OAGetProperty @objStream, 'Size', @size OUTPUT
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAGetProperty StreamSize'
GOTO ERRORED
END
INSERT INTO #ret VALUES ('')
SELECT @ptrval = TEXTPTR(XMLOutput) FROM #ret
WHILE @pos < @size
BEGIN
-- ReadText
EXEC @rc = sp_OAMethod @objStream, 'ReadText', @xml OUTPUT, @BUFF_SIZE
IF @rc 0
BEGIN
EXEC sp_OAGetErrorInfo @objStream, @errsrc OUTPUT, @errdesc OUTPUT
SET @xml = 'sp_OAMethod Stream.ReadText'
GOTO ERRORED
END
UPDATETEXT #ret.XMLOutput @ptrval @pos 0 @xml
SET @pos = @pos + @BUFF_SIZE
END
IF EXISTS (SELECT * FROM #ret WHERE XMLOutput LIKE '<?MSSQLError HResult%')
BEGIN
-- SQL error
SELECT @rc = -1,
@xml = 'Command.Execute',
@errsrc = 'SQL Server',
@errdesc = CAST(XMLOutput AS varchar(4000))
FROM #ret
GOTO ERRORED
END
CLEANUP:
EXEC sp_OADestroy @objStream
EXEC sp_OADestroy @objCommand
EXEC sp_OADestroy @objProperty
EXEC sp_OADestroy @objOutputStream
SELECT * FROM #ret
DROP TABLE #ret
RETURN @rc
ERRORED:
DELETE FROM #ret
SET @xml = 'Command: ' + @xml + '. Source: ' + @errsrc + '. Description: ' + @errdesc
INSERT INTO #ret VALUES (@xml)
GOTO CLEANUP
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply