Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

Parsing larger XML file using “sp_OACreate” returns incorrect values

Parsing larger XML file using “sp_OACreate” returns incorrect values

  • Comments 1

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

Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • I am still getting this error

    code:

    USE [datamart]

    GO

    /****** Object:  StoredProcedure [dbo].[mis_sp_write_text]    Script Date: 03/27/2012 20:17:01 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    ALTER procedure [dbo].[mis_sp_write_text]

    @filename varchar(1000),--要操作的文本文件名

    @text varchar(8000) --要写入的内容

    as

    declare @err int,@src varchar(255),@desc varchar(255)

    declare @obj int

    --declare @date varchar(10)=convert(varchar,getdate(),112) 改为在sp_prog中定义

    declare @datetime varchar(20)=convert(varchar,getdate(),120)

    declare @logpath varchar(1000)='D:\MISETL-Tools\Data.Fix\'

    declare @filefullname varchar(1000)=@logpath+@filename+'.ini'         --文件路径(spname+date)

    declare @fulltext varchar(1000)=@text

    exec @err=sp_oacreate "Scripting.FileSystemObject",@obj out

    if @err<>0 goto lberr

    exec @err=sp_oamethod @obj,"OpenTextFile",@obj out,@filefullname,8,1

    if @err<>0 goto lberr

    exec @err=sp_oamethod @obj,"WriteLine",null,@fulltext

    if @err<>0 goto lberr

    --exec @err=sp_oadestroy @obj

    return

    lberr:

    exec sp_oageterrorinfo 0,@src out,@desc out

    select cast(@err as varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述

Page 1 of 1 (1 items)