Parsing larger XML file using “sp_OACreate” returns incorrect values

Article
10/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 https://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'