sp_xml_PrepareDocumentFromColumn

,

Do you want to use OPENXML with an xml document in a table, this procdure will do it for you.
Pass in the SQL that will return the column of data, it will return the document handle which can then be used with OPENXML.

Example

DROP TABLE Tab

CREATE TABLE Tab (doc ntext)

INSERT INTO Tab VALUES (''
+ REPLICATE('' + char(10), 50)
+ REPLICATE('' + char(10), 50)
+ REPLICATE('' + char(10), 50)
+ REPLICATE('' + char(10), 50)
+ REPLICATE('' + char(10), 50)
+ REPLICATE('' + char(10), 50)
+ '')
SELECT DATALENGTH (Doc) FROM TAb
DECLARE @iDoc int
exec sp_xml_PrepareDocumentFromColumn @iDoc OUTPUT, 'SELECT TOP 1 doc FROM Tab'
SELECT * FROM OPENXML (@iDoc ,'//customers')
WITH (id varchar(10))
exec sp_xml_removedocument @iDoc
GO

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

Rate

Share

Share

Rate