Microsoft SQL Server
Скрипты
T-SQL

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

Опубликовано: 01 янв 05
Рейтинг:

Автор: Glory (по мотивам методов Ken Henderson)
Прислал: Программизд 02

I. Данный скрипт предназначен для размещения результатов запроса с FOR XML в BLOB столбце таблицы.
Основная идея - это использование COM объекта SQLDMO для выполнения запроса сервера к самому себе и чтения его результатов

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

II. Пример извлечения из BLOB столбца xml файла и передачи его в процедуру с входным параметром типа text.
Основная идея - строим динамический запрос который
- создает локальные переменные в количестве необходимом для извлечения всего BLOB столбца
- заполняет эти переменные
- собирает внутри еще один динамический запрос из имени процедуры и локальных переменных

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

-- ***********************************************
-- PROCEDURE FOR PARSING XML FROM FIELD TEXT
-- ***********************************************
-- you can declare a procedure with the paramenter TEXT
-- but you can not assign it a value within the database
-- you can only put it from the client
-- the workaround is to dynamically make a procedure call
-- by concatenating the chunks of varchars and execute it by EXEC
create proc ParseXML 
	@ParseProc as varchar(200) --
as
declare
  @datalen int,
  @sql varchar(8000),
  @sql1 varchar(8000),
  @sql2 varchar(8000),
  @sql3 varchar(8000),
  @sql4 varchar(8000),
  @sql5 varchar(8000),
  @sql6 varchar(8000),
  @sql7 varchar(8000),
  @cnt int
DECLARE @hDoc int

SELECT @datalen = DATALENGTH (xml_data) / 8000 + 1 FROM xml_export 

IF @datalen IS NULL RETURN

SET @cnt = 1
SET @sql = 'DECLARE' + CHAR(13)
WHILE (@cnt <= @datalen)
BEGIN
SELECT @SQL = @SQL + CASE @cnt WHEN 1 THEN '' ELSE ', ' + CHAR(13) END
              + ' @str' + CONVERT(varchar(10), @cnt) + ' varchar(8000)'
SET @cnt = @cnt + 1
END

SET @sql = @sql + ',' + CHAR( 13) + ' @textptr varbinary(16)' + CHAR(13) +
	'SELECT @textptr = TEXTPTR(xml_data) FROM xml_export' 

SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
	IF LEN(@SQL) < 7900
		SELECT @SQL = @SQL + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL1) < 7900
		SELECT @SQL1 = @SQL1 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL2) < 7900
		SELECT @SQL2 = @SQL2 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL3) < 7900
		SELECT @SQL3 = @SQL3 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) +
			 ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL4) < 7900
		SELECT @SQL4 = @SQL4 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL5) < 7900
		SELECT @SQL5 = @SQL5 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) +
			 ' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE IF LEN(@SQL6) < 7900
		SELECT @SQL6 = @SQL6 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 
	ELSE
		SELECT @SQL7 = @SQL7 + CHAR (13) + 'SELECT @str' + CONVERT(varchar(10), @cnt) + 
			' = SUBSTRING (xml_data, ' + CONVERT (varchar(30),(@cnt-1) * 8000 + 1) + ', 8000) FROM xml_export' 

	SET @cnt = @cnt + 1
END

SET @SQL7 = @SQL7 + CHAR(13) + 'EXEC (''EXEC '+ @ParseProc + ' '''''' + '

SET @cnt = 1
WHILE (@cnt <= @datalen)
BEGIN
	SELECT @SQL7 = @SQL7 + CASE @cnt WHEN 1 THEN '' ELSE ' + ' END + '@str' + CONVERT(varchar(10), @cnt)
	SET @cnt = @cnt + 1
END
SET @SQL7 = @SQL7 + ' + '''''''')'

EXEC(@SQL+@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7)
GO

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

create proc [dbo].[ParseEMP] 
	@txt text as
DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @txt

 -----------------------------------------------------------------
 SELECT id, ename, age 
 FROM OPENXML(@hDoc, N'/root/emp') 
 with (id int 'id', ename varchar(30) 'ename', age int 'age') 
 -----------------------------------------------------------------

EXEC sp_xml_removedocument @hDoc
RETURN
GO

-- viewing the results
select XML_DATA from dbo.XML_EXPORT
-- now parse the XML data placed in TEXT field
exec dbo.ParseXML 'dbo.ParseEMP'

/* Results
id          ename                          age         
----------- ------------------------------ ----------- 
1           judge                          28
2           Cat2                           14
3           akuz                           67
4           hDrummer                       24

*/

Комментарии


  • В 2008 всё стало намного проще:

    declare @t nvarchar(max);
    -- или, если надо, declare @t xml;

    select @t=x from
    (select * from mytable as [row] for xml auto, ROOT('root')) AS xmldata(x);

    Вуаля!

  • спасибо огромное!!!

  • Замечу, что в данном примере закрываются не все OLE объекты. Обратите внимание на это, может привести к непонятным ошибкам

  • Спасибо!



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / Поместить результат FOR XML в поле TEXT, прочесть XML из поля TEXT