drop table XML_EXPORT
go

CREATE TABLE XML_EXPORT(
ID int,
XML_DATA text
)
go

drop table emp
go

CREATE TABLE EMP(
ID int,
ENAME varchar(50),
age int
)
go

insert into emp values (1, 'Igor', 23)
insert into emp values (2, 'Oleg', 34)
insert into emp values (3, 'Alex', 28)
insert into emp values (4, 'Sergei', 78)
GO

DROP PROC RaiseError
GO

CREATE PROC [dbo].[RaiseError]
  @object int,
  @hr int,
  @user_description nvarchar(4000)=NULL
AS
DECLARE @errormsg nvarchar(4000),
  @source nvarchar(4000),
  @description nvarchar(4000)

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

DROP PROC 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(4000)
AS
DECLARE @dbname sysname,
        @sqlobject int,
        @object int,
        @hr int,
        @results int

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

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

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

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

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

SET @dbname = DB_NAME()
EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbname
IF (@hr <> 0)
BEGIN
  EXEC RaiseError @sqlobject, @hr, N'Getting handler for current database'
  RETURN
END

EXEC @hr = sp_OAMethod @object, 'ExecuteWithResults', @results OUT, @query
IF (@hr <> 0)
BEGIN
  EXEC RaiseError @sqlobject, @hr, N'Executing query ExecuteWithResults'
  RETURN
END

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

DECLARE @ptrval binary(16)
DECLARE @cur_length INT

SELECT @PTRVAL = TEXTPTR(XML_DATA) FROM XML_EXPORT

DECLARE @row INT
DECLARE @out_text varchar(8000)
DECLARE @rpl_res varchar(8000)

SET @row = 1
WHILE(@row <= @row_count)
BEGIN

  EXEC @hr = sp_OAMethod @results, 'GetColumnString', @out_text OUT, @row, 1
  IF @hr <> 0
  BEGIN
    EXEC RaiseError @sqlobject, @hr, N'Retrieving query data'
    RETURN
  END

  SELECT @cur_length = DATALENGTH(XML_DATA) FROM XML_EXPORT
  SET @rpl_res = REPLACE(@out_text, '><', '>' + char(13) + '<')
  UPDATETEXT XML_EXPORT.XML_DATA @ptrval Null 0 @rpl_res

  SET @row = @row + 1

END

EXEC sp_OADestroy @sqlobject

GO

drop proc 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 + ' + '''''''')'

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

-- now the example of putting the XML data to the TEXT field
insert into XML_EXPORT (XML_DATA) values (NULL);
update XML_EXPORT set XML_DATA = '<?xml version="1.0" encoding="windows-1252"?>'+char(13)+'<root>'+char(13)
exec PutXMLtoTEXT 'select id, ename, age from emp for xml auto, elements'
DECLARE @ptrval binary(16)
SELECT @PTRVAL = TEXTPTR(XML_DATA) FROM XML_EXPORT
UPDATETEXT XML_EXPORT.XML_DATA @ptrval NULL 0 '</root>'
go

-- viewing the results
select XML_DATA from XML_EXPORT
go

-- now parse the XML data placed in TEXT field
drop proc 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

GO

exec ParseXML 'ParseEMP'