Parsing larger XML file using “sp_OACreate” returns incorrect values

Published Jan 15 2019 12:27 PM 958 Views
First published on MSDN on Oct 23, 2009

sp_OACreate is a way to instantiate OLE / COM objects inside of SQL Server in the MemToLeave area of the server’s address space, or out of process in dllhost.exe. Whenever you instantiate a COM object using sp_OACreate method you need to destroy the objects  created else it might run out of memory causing the application to fail because SQLMemToLeave is a small workspace i,e (384MB on SQL Server 2000). You might experience the following symptoms if you are using sp_OACreate to load large XML


Symptoms



When we try to read a large XML in SQL Server using extended stored procedures (sp_oaCreate) it shows incorrect values after certain nodes.  The Script works fine if we reduce the number of elements in the source XML.


Add the following error handling code to find the exact error why we are failing.


IF @intReturn <>0



Begin


Declare @iMessageObjId int


Declare @vcErrSource Varchar(100)


Declare @vcErrDescription varchar (100)


EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out


print 'MSGID: ' + CAST(@iMessageObjId as Char)


print 'SRC: ' + @vcErrSource


print 'DESC: ' + @vcErrDescription


Print 'HR: ' + cast(@intReturn as char)


End


We were able to find the following error in the result window


OLE Automation Error Information
HRESULT: 0x0000275d
Source: ODSOLE Extended Procedure
@hr = 10077



Cause


This happens when you do not destroy the Objecttokens returned by sp_OAMethod


Resolution



In order to resolve this we need to run sp_OADestroy on the objecttokens returned by sp_OAMethod.


SQL Server Books Online states that, sp_OADestroy is used on the objects created by sp_OACreate method. We also found that it is safe to pass the Objecttoken’s as the input parameters for the sp_OADestroy returned by sp_OAMethod.



sp_OADestroy objecttoken


(Where objecttoken is the OLE object that was created by using sp_OACreate)


Sample Script


Here is the sample script on how to read the XML file in SQL Server.


You can find the Sample XML file (Books.xml) is the below MSDN link http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx


DECLARE @chrAuthor varchar (50)


DECLARE @chrTitle varchar (50)


DECLARE @chrGenre varchar (30)


DECLARE @chrPrice varchar (10)


DECLARE @chrPublish_Date varchar (20)


DECLARE @chrDescription varchar (256)


DECLARE @chrTag varchar (256)


DECLARE @intNodeCount int


DECLARE @objXML int


DECLARE @intNodeList int


DECLARE @intNode int


DECLARE @intReturn int


DECLARE @Return int


DECLARE @NodeText varchar(1000)


declare @i int


EXECUTE @intReturn = sp_OACreate 'MSXML2.DOMDocument', @objXML OUTPUT


EXECUTE @intReturn = sp_OAMethod @objXML, 'Load', @Return OUTPUT, 'D:\MyShare\Books.xml'


EXECUTE @intReturn = sp_OAMethod @objXML, 'getElementsByTagName', @intNodeList OUTPUT, 'catalog/book'


EXECUTE @intReturn = sp_OAGetProperty @intNodeList, 'length', @intNodeCount OUTPUT


SET @i = 0


WHILE (@i < @intNodeCount)


BEGIN


-- get a pointer to each node


EXECUTE @intReturn = sp_OAMethod @intNodeList, 'nextNode', @intNode OUTPUT


EXECUTE sp_OADestroy @intNodeList


-- get node properties


EXECUTE @intReturn = sp_OAGetProperty @intNode, 'Text', @nodetext OUTPUT


EXECUTE sp_OADestroy @intNode


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/author'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrAuthor OUTPUT


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/title'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrTitle OUTPUT


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/genre'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrGenre OUTPUT


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/price'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrPrice OUTPUT


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/publish_date'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrPublish_Date OUTPUT


SET @chrTag = '//catalog/book[' + RTRIM(CAST(@i as Char)) + ']/description'


EXECUTE @intReturn = sp_OAMethod @objXML, 'selectSingleNode', @Return OUTPUT, @chrTag


EXECUTE @intReturn = sp_OAGetProperty @Return, 'Text', @chrDescription OUTPUT


SELECT @chrAuthor, @chrTitle, @chrGenre, @chrPrice, @chrPublish_Date, @chrDescription


SET @i = @i + 1


END


EXECUTE @intReturn = sp_OADestroy @objXML



Note:


Use of Microsoft XML (MSMXL) inside a SQL Server stored procedure through the SQL Server OLE automation stored procedures (sp_OACreate, etc...), will result in memory leak inside of SQL Server which is discussed in this article ( http://support.microsoft.com/default.aspx?scid=kb;EN-US;303114 ). Microsoft does not recommend that you use MSXML inside of a SQL Server stored procedure via the SQL Server ole automation stored procedures .


Reading XML inside the SQL Server using extended stored procedures is the old 6.5 way of doing xml. If you are developing new projects then we need to avoid using sp_OACreate method. You could try using


SQL 2000 - sp_xml_preparedocument / sp_xml_removedocument to shred XML documents within SQL Server


SQL 2005 – new FOR XML with XPath query syntax support, XML data type


SQL 2008 – improved XML support since SQL 2005, Xquery syntax enhancements


Sample script to read xml using sp_xml_preparedocument :



DECLARE @xmlDoc NVARCHAR(400)



DECLARE @handle INT


SET @xmlDoc = N'


<book>


<au_author>409-56-7008</au_author>


<au_title>XML Developer''s Guide</au_title>


<au_genre>Computer</au_genre>


<au_price>44.95</au_price>


<au_publish_date>2000-10-01</au_publish_date>


<au_description>An in-depth look at creating applications with XML.</au_description>


</book>'


EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc


SELECT * FROM OPENXML (@handle, '/book', 2)


WITH


(


au_author VARCHAR(15),


au_title NVARCHAR(30),


au_genre NVARCHAR(20),


au_price NVARCHAR(20),


au_publish_date NVARCHAR(20),


au_description NVARCHAR(60)


)


EXEC sp_xml_removedocument @handle



Reference:



Here is a whitepaper showing the evolution of XML since SQL 2000 – SQL 2008.


http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx




Author : Praveen M (MSFT) , SQL Developer Technical Lead , Microsoft


Reviewed by : Jason Howell , SQL Escalation Services , Microsoft

Version history
Last update:
‎Jan 15 2019 12:27 PM
Updated by: