February 9, 2006 at 2:48 am
Does anyone know how to load the output from a 'for xml' query into a varchar or text variable?
February 10, 2006 at 1:42 am
It's not pretty but here's a stored proc that executes a specified statement and returns the resulting FOR XML as a proper recordset which can then be inserted, queried etc.
I take no credit for it as I simply tweaked code I downloaded, unfortunately I can't remember who or where from. Perhaps someone else in the forum can pass on the credit.
Anyway, hope it helps
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
February 10, 2006 at 2:10 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply