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

OPENXML Namespace

I am new to using XML. I have been given the following structure and
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa-msg
xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)
Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:

> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa-msg
> xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>
|||Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
[vbcol=seagreen]
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
>)
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bbb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
>)
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:

OPENXML Namespace

I am new to using XML. I have been given the following structure and
cannot figure out how to read the elements. I can get the edge table
but not the data in the format I am looking for. I believe there
should be 4 elements:
service_request_num, activity_code, outcome_code, details
I've been beating my head against the wall and the flat spots are
starting to show!
Can someone help?
TIA,
IDriveFast
DECLARE @.idoc int
DECLARE @.doc varchar(8000)
SET @.doc =
'<?xml version="1.0" encoding="UTF-8"?>
<aaa:aaa-msg
xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb">
<aaa:header>
<aaa:eid/>
<aaa:reference_id>1234</aaa:reference_id>
<aaa:event_date>JAN 10, 2003 02:07:21
PM</aaa:event_date>
<aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
<aaa:event_type_code>REQUEST</aaa:event_type_code>
<aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
<aaa:source_code>SIDEWALK</aaa:source_code>
<aaa:target_code>PRIMAPP</aaa:target_code>
<aaa:status_code />
<aaa:details/>
</aaa:header>
<aaa:body>
<bbb:document>
<bbb:header>
<bbb:system>bbb</bbb:system>
<bbb:doc_name>NA</bbb:doc_name>
<bbb:version>3.9.0</bbb:version>
<bbb:dml_event/>
<bbb:comment/>
</bbb:header>
<bbb:service_request>
<bbb:service_request_num>
<bbb:value>03-00002269</bbb:value>
</bbb:service_request_num>
<bbb:sr_activities>
<bbb:sr_activity>
<bbb:activity_code>
<bbb:value>ACTIVIT3</bbb:value>
</bbb:activity_code>
<bbb:outcome_code>
<bbb:value>LINKSR</bbb:value>
</bbb:outcome_code>
<bbb:details>
<bbb:value>Details go here</bbb:value>
</bbb:details>
</bbb:sr_activity>
</bbb:sr_activities>
</bbb:service_request>
</bbb:document>
</aaa:body>
</aaa:aaa-msg>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
SELECT *
FROM OPENXML (@.idoc, '/',2)Tia,
There are three things that you need to do to get this to work.
1) Define all the namespaces that are required.
2) Select a xpath that is pass to the OPENXML function to filter your data.
3) Use the WITH clause to select the specific data you are interested in.
I've supplied two example queries that seem to meet your requirements. One
assume that only one activity occurs per request and one that assume there
can be multiple activities per request.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
'<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
xmlns:bbb="http://www.company.com/Schema/bbb"/>'
-- service request based
SELECT *
FROM
OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
WITH
(
service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
activity_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
outcome_code nvarchar(25)
'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
details nvarchar(4000)
'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
)
-- activity based
SELECT *
FROM
OPENXML (@.idoc,
'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/bb
b:sr_activity',2)
WITH
(
service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value'
,
activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
details nvarchar(4000) 'bbb:details/bbb:value'
)
EXEC sp_xml_removedocument @.idoc
"I Drive Fast" wrote:

> I am new to using XML. I have been given the following structure and
> cannot figure out how to read the elements. I can get the edge table
> but not the data in the format I am looking for. I believe there
> should be 4 elements:
> service_request_num, activity_code, outcome_code, details
> I've been beating my head against the wall and the flat spots are
> starting to show!
> Can someone help?
> TIA,
> IDriveFast
> DECLARE @.idoc int
> DECLARE @.doc varchar(8000)
> SET @.doc =
> '<?xml version="1.0" encoding="UTF-8"?>
> <aaa:aaa-msg
> xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb">
> <aaa:header>
> <aaa:eid/>
> <aaa:reference_id>1234</aaa:reference_id>
> <aaa:event_date>JAN 10, 2003 02:07:21
> PM</aaa:event_date>
> <aaa:sr_type_code>DIRTYWAT</aaa:sr_type_code>
> <aaa:event_type_code>REQUEST</aaa:event_type_code>
> <aaa:eai_event_code>XAPPSR</aaa:eai_event_code>
> <aaa:source_code>SIDEWALK</aaa:source_code>
> <aaa:target_code>PRIMAPP</aaa:target_code>
> <aaa:status_code />
> <aaa:details/>
> </aaa:header>
> <aaa:body>
> <bbb:document>
> <bbb:header>
> <bbb:system>bbb</bbb:system>
> <bbb:doc_name>NA</bbb:doc_name>
> <bbb:version>3.9.0</bbb:version>
> <bbb:dml_event/>
> <bbb:comment/>
> </bbb:header>
> <bbb:service_request>
> <bbb:service_request_num>
> <bbb:value>03-00002269</bbb:value>
> </bbb:service_request_num>
> <bbb:sr_activities>
> <bbb:sr_activity>
> <bbb:activity_code>
> <bbb:value>ACTIVIT3</bbb:value>
> </bbb:activity_code>
> <bbb:outcome_code>
> <bbb:value>LINKSR</bbb:value>
> </bbb:outcome_code>
> <bbb:details>
> <bbb:value>Details go here</bbb:value>
> </bbb:details>
> </bbb:sr_activity>
> </bbb:sr_activities>
> </bbb:service_request>
> </bbb:document>
> </aaa:body>
> </aaa:aaa-msg>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc , '<aaa
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
> SELECT *
> FROM OPENXML (@.idoc, '/',2)
>|||
Thanks for both answers!
On Tue, 31 Jan 2006 12:19:30 -0800, "Galex Yen [MSFT]"
<galexy@.online.microsoft.com> wrote:
>Tia,
>There are three things that you need to do to get this to work.
>1) Define all the namespaces that are required.
>2) Select a xpath that is pass to the OPENXML function to filter your data.
>3) Use the WITH clause to select the specific data you are interested in.
>I've supplied two example queries that seem to meet your requirements. One
>assume that only one activity occurs per request and one that assume there
>can be multiple activities per request.
>EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc ,
> '<aaa xmlns:aaa="http://www.company.com/Schema/aaa"
> xmlns:bbb="http://www.company.com/Schema/bbb"/>'
>-- service request based
>SELECT *
>FROM
>OPENXML (@.idoc, '/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request',2)
>WITH
>(
>service_request_num nvarchar(25) 'bbb:service_request_num/bbb:value',
>activity_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:activity_code/bbb:value',
>outcome_code nvarchar(25)
> 'bbb:sr_activities/bbb:sr_activity/bbb:outcome_code/bbb:value',
>details nvarchar(4000)
>'bbb:sr_activities/bbb:sr_activity/bbb:details/bbb:value'
> )
>-- activity based
>SELECT *
>FROM
>OPENXML (@.idoc,
>'/aaa:aaa-msg/aaa:body/bbb:document/bbb:service_request/bbb:sr_activities/b
bb:sr_activity',2)
>WITH
>(
>service_request_num nvarchar(25) '../../bbb:service_request_num/bbb:value
',
>activity_code nvarchar(25) 'bbb:activity_code/bbb:value',
>outcome_code nvarchar(25) 'bbb:outcome_code/bbb:value',
>details nvarchar(4000) 'bbb:details/bbb:value'
> )
>EXEC sp_xml_removedocument @.idoc
>"I Drive Fast" wrote:
>