This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
First published on MSDN on Oct 23, 2009sp_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