querying xml data/ out of memory exception

• lothar-735100

  Valued Member

  Points: 51

  More actions

  May 5, 2008 at 4:11 pm

  #186102

  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.

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  May 6, 2008 at 5:41 am

  #811114

  Hi lothar,

  Can you tell what your expected result when you executing select statement?

  and try with the below example.It may useful for you:

  select xml_data.query('(authors/author/first-name)')

  from XmlImportTest

  ---