query xml data/ out of memory exception - SQL Server 2005 Specific

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Scripts, Code Samples and SSMS Custom Reports » SQL Server 2005 Specific » query xml data/ out of memory exception

query xml data/ out of memory exception

Options

Previous Topic · Next Topic

lothar

Posted: Monday, May 05, 2008 5:19:18 PM

Rank: Newbie

Joined: 5/5/2008
Posts: 1
Points: 3
Location: USA

Hi everyone,

I have a task in which we get feed files in form of xml files and we have to investigate them for bugs. So, I created a sql server table XMLImportTest and inserted the file in the column xml_data.

CREATE TABLE XmlImportTest

(

xmlFileName VARCHAR(300),

xml_data xml

)

GO

DECLARE @xmlFileName VARCHAR(300)

SELECT @xmlFileName = 'C:\\XMLFiles\stats.xml'

EXEC('

INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData

FROM

(

SELECT *

FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA

) AS FileImport (XMLDATA)

')

GO

Now, I have to query this data for some specific string values.

select xml_data.query('for $so in //indicator

where $so/canonicalIndicator = "power use"

or $so/displayIndicator = "power use"

or $so/alt = "power use"

return $so')

from dbo.XmlImportTest

For a file of size 478,601 KB, this query took 7:33 min to execute. Why is this query so slow? Is there anyway to get results faster?

Also when I execute

select * from dbo.XmlImportTest

I get an error

‘An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.’

My machine is running Windows xp professional SP2 and has 2 GB of RAM.

Please let me know, what the problem is. Also, for the above type of task, is there a better way to perform it?

Thanks in advance.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.