sp_xml_PrepareDocumentFromColumn

Total article views: 365 | Views in the last 30 days: 1

 Rate this |   Join the discussion |  Briefcase

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

Total article views: 365 | Views in the last 30 days: 1