OPENXML Not enough storage errors

I am trying to work the a large (65MB) hierarchical data structure stored in XML that describes phylogenetic relationships in biology. I would like to return a set of species belonging to a particular phylogenetic class (e.g. "Mammalia") which corresponds to returning all the terminal children of this node in the XML tree.

The query

select sp
from openxml(@.docHandle, N'//node[rank/text()="species"]/names/name[@.class="scientific name"]') with (
sp varchar(50) './text()'
)

runs and returns all 287000 species in the document, but if I try to restrict to children of the node "Mammalia" using the query

select sp
from openxml(@.docHandle, N'//node[names/name/text()="Mammalia"]//node[rank/text()="species"]/names/name[@.class="scientific name"]') with (
sp varchar(50) './text()'
)

I get an error:

XML parsing error: Not enough storage is available to complete this operation.

Seems like the latter query should use less memory, not more, because it is restricting the return set to a small fraction of the total.

Any suggestions?

Do you really need this complex path expression with two //node? That is going to cost you.

What about using the following expression:

//node[rank/text()="species"]/names/name[@.class="scientific name" and text()="Mammalia"]

Also, if you are using SQL Server 2005, have you looked at using the nodes() method and XQuery?

Best regards

Michael

|||

Unfortunately, reordering the XPath will not give the desired result set. Will take a look at XQuery and nodes().

Thanks,

David