sp_xml_PrepareDocumentFromColumn

IF EXISTS (SELECT 1 FROM sysobjects WHERE name ='sp_xml_PrepareDocumentFromColumn')
  DROP PROCEDURE dbo.sp_xml_PrepareDocumentFromColumn
GO
CREATE PROCEDURE dbo.sp_xml_PrepareDocumentFromColumn
/*******************************************************************************

  Written By  : Simon Sabin
  Date        : 12 October 2002
  Description : Prepares an xml document base on the data returned from a single 
                row select statement

  History
  Date       Change
  ------------------------------------------------------------------------------
  12/10/2002 Created

*******************************************************************************/
  @iDoc int OUTPUT ,
  @SQLstatement ntext ,
  @Namespace nvarchar(4000) = ''
AS
SET NOCOUNT ON
DECLARE @iParts int
DECLARE @iPart int
DECLARE @Declare nvarchar(4000)
DECLARE @Select  nvarchar(4000)
DECLARE @Exec    nvarchar(4000)
DECLARE @Print   nvarchar(4000)
DECLARE @PartName varchar(20)

CREATE TABLE #XMLData (Document text)

EXECUTE ('INSERT INTO #XMLData ' + @SQLStatement)

SELECT @iParts = DATALENGTH(Document)/3000 + 1 FROM #XMLData
SET @Declare = ''
SET @Select = ''
SET @Exec = ''
SET @Print = ''
SET @iPart = 0
WHILE @iPart < @iParts
  BEGIN
  SET @PartName = '@XMLPart' + cast(@iPart as varchar(4))
  SET @Declare = @Declare + char(10) + ',' + @PartName + ' nvarchar(4000)'
  SET @Select = @Select + char(10) + ',' + @PartName + ' = REPLACE(SUBSTRING(Document, ' + cast(@iPart * 3000 + 1 as varchar(6)) + ',3000),'''''''','''''''''''')'
  SET @Exec = @Exec + @PartName + ' + '
  SET @Print = @Print + 'PRINT ' + @PartName + char(10)
  SET @iPart = @iPart + 1
  END
SET @Declare = 'DECLARE ' + RIGHT(@Declare, LEN(@Declare) - 2) + '
CREATE TABLE #XmlData (Document text)
INSERT INTO #XmlData 
' 

SET @Select = char(10) + 'SELECT ' + RIGHT(@Select ,LEN(@Select) -2) + ' FROM #XmlData ' + char(10)

SET @Exec = 'EXECUTE (''DECLARE @iDoc int
exec sp_xml_preparedocument @iDoc OUTPUT, '''''' + ' + @Exec + ''''''', ''''' + @namespace + '''''
INSERT INTO #id VALUES (@iDoc)'')'

PRINT @DECLARE 
PRINT @SQLStatement
PRINT @SELECT 
PRINT @PRINT
PRINT @Exec
CREATE TABLE #Id (id int)
EXECUTE (@DECLARE + @SQLStatement + @SELECT + @Print + @Exec)
SELECT @IDoc = id FROM #id
DROP TABLE #XmlData
DROP TABLE #Id
GO