Поместить результат FOR XML в поле TEXT, прочесть XML из поля TEXT

05 августа 2011, 14:45

use tempdb
go

/*Create test table*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[emp]
go

CREATE TABLE [dbo].[emp](
	[ID] int,
	ENAME varchar(50),
	age int
)
go

/*fill test table with some values*/
insert into [dbo].[emp] values (1, 'judge', 28)
insert into [dbo].[emp] values (2, 'Cat2', 14)
insert into [dbo].[emp] values (3, 'akuz', 67)
insert into [dbo].[emp] values (4, 'hDrummer', 24)
GO

/*Procedure to display error for OLE object*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RaiseError]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[RaiseError]
GO

CREATE PROC [dbo].[RaiseError]
  @object int, -- OLE object token
  @user_description nvarchar(4000)=NULL -- User error message text 
AS
DECLARE @hr int, -- procedure return code
	@errormsg nvarchar(4000), -- error message text to display
	@source nvarchar(4000), -- OLE object error message source
	@description nvarchar(4000) -- OLE object error message description

-- try to get OLE object error 
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
  SET @errormsg = ''
  IF @user_description IS NOT NULL SET @errormsg = @user_description + char(13) + char(10)

  SET @errormsg = @errormsg + N'Source: ' + @source + char(13) + char(10)
  SET @errormsg = @errormsg + N'Description: ' + @description
  RAISERROR(@errormsg, 16, 1)
  RETURN
END
ELSE
BEGIN
  RAISERROR('Detailed error information can not be retrieved', 16, 1)
  RETURN
END

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PutXMLtoTEXT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[PutXMLtoTEXT]
GO

-- ***********************************************
-- PROCEDURE FOR PUTTING XML TO FIELD TEXT
-- ***********************************************
-- it is not possible to put the result of FOR XML to TEXT field
-- the trick is using COM that is the server acts  
-- as a COM client of itself to read the data of FOR XML 
CREATE PROC [dbo].[PutXMLtoTEXT] 
	@query varchar(8000), -- query text
	@dbname sysname = NULL -- database name
AS
DECLARE @sqlobject int, -- OLE object token for SQL Server
        @dbobject int, -- OLE object token for Database
        @hr int, -- procedure return value
        @results int, -- OLE object token for query results
	@row_count int -- query results row count

-- If database was not passed then using current database
IF @dbname IS NULL SET @dbname = DB_NAME()

-- create COM object SQLDMO.SQLServer
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sqlobject OUT
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Creating SQLDMO.SQLServer object'
  RETURN
END

-- Always using trusted connection
EXEC @hr = sp_OASetProperty @sqlobject, 'LoginSecure', 1
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Switch on trusted connection mode'
  RETURN
END

-- Switching off ODBC prefix
EXEC @hr = sp_OASetProperty @sqlobject, 'ODBCPrefix', 0
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Switching off ODBCPrefix'
  RETURN
END

-- Try to connect to SQLServer
EXEC @hr = sp_OAMethod @sqlobject, 'Connect', NULL, @@SERVERNAME
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Connecting to SQL Server'
  RETURN
END

-- Getting Database collection handler
EXEC @hr = sp_OAGetProperty @sqlobject, 'Databases', @dbobject OUT
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting Database collection handler'
  RETURN
END

-- Getting handler for choosen database
EXEC @hr = sp_OAMethod @dbobject, 'Item', @dbobject OUT, @dbname
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting handler for choosen database'
  RETURN
END

-- Executing query using ExecuteWithResults Method
EXEC @hr = sp_OAMethod @dbobject, 'ExecuteWithResults', @results OUT, @query
IF (@hr <> 0)
BEGIN
  EXEC dbo.RaiseError @sqlobject,  N'Executing query using ExecuteWithResults Method'
  RETURN
END

-- Getting query results row count
EXEC @hr = sp_OAMethod @results, 'Rows', @row_count OUT
IF @hr <> 0
BEGIN
  EXEC dbo.RaiseError @sqlobject, N'Getting query results row count'
  RETURN
END

DECLARE @ptrval binary(16),-- BLOB column handler
	@row int, -- current row number
	@out_text varchar(8000) -- current row value


-- Getting BLOB column handler
SELECT @ptrval = TEXTPTR(XML_DATA) FROM dbo.XML_EXPORT 
-- Starting with result 1st row
SET @row = 1

-- Cycle for all result rows
WHILE(@row <= @row_count)
BEGIN
	-- Retrivie next result row
	EXEC @hr = sp_OAMethod @results, 'GetColumnString', @out_text OUT, @row, 1
	IF @hr <> 0
	BEGIN
		EXEC dbo.RaiseError @sqlobject, N'Retrieving query data'
		RETURN
	END
	-- Adding row into our BLOB column
	UPDATETEXT XML_EXPORT.XML_DATA @ptrval Null 0 @out_text
	
	-- Choosing next row
	SET @row = @row + 1
END

-- Destroy OLE object
EXEC sp_OADestroy @sqlobject
IF @hr <> 0
BEGIN
	EXEC dbo.RaiseError @sqlobject, N'Destroying OLE object'
	RETURN
END
GO


-- now the example of putting the XML data to the TEXT field
/*It's our table to store results*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XML_EXPORT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[XML_EXPORT]
go

CREATE TABLE [dbo].[XML_EXPORT](
	[ID] int,
	[XML_DATA] text
)
go
-- We have to insert at least one record with empty value
insert into dbo.XML_EXPORT (XML_DATA) values ('');
-- By default query with FOR XML does not generate <root> tags
-- So we have to do this
update XML_EXPORT set XML_DATA = '<?xml version="1.0" encoding="windows-1252"?>'+char(13)+'<root>'+char(13) 

-- Call procedure to put xml into BLOB column
exec dbo.PutXMLtoTEXT 'select id, ename, age from emp for xml auto, elements'

-- And do not forget to add <root> tag at end
DECLARE @ptrval binary(16)
SELECT @PTRVAL = TEXTPTR(XML_DATA) FROM dbo.XML_EXPORT 
UPDATETEXT XML_EXPORT.XML_DATA @ptrval NULL 0 '</root>'

-- Checking results
select * from dbo.XML_EXPORT
go

FAQ

Поместить результат FOR XML в поле TEXT, прочесть XML из поля TEXT