XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Paul HermansSubject: Error when using two nested sql/xml selects
Author: Paul Hermans
Date: 16 Mar 2006 02:19 PM
We have an SQL/XML file (attached) for accessing a MS Access database (attached).

We have two nested SELECT statements in this file.
When using only one of those two SELECTS the query works.
If using them together we get a "data type mismatch in criteria" error (attached).
We tested this with different SELECT statements, even with different databases and always getting this error when using more than one nested select.


Documenterrordatatypemismatch.png
error message

Documentdatatypemismatch.txt
query

Postnext
Ivan PedruzziSubject: Error when using two nested sql/xml selects
Author: Ivan Pedruzzi
Date: 16 Mar 2006 02:58 PM
Hi Paul,

Could you please tell us what type are the columns used in the WHERE condition?

Do you have primary keys defined in the 3 tables?

It seems like that the sub-queries are identical is that so?


Thank You
Ivan Pedruzzi
Stylus Studio Team

Postnext
Paul HermansSubject: Error when using two nested sql/xml selects
Author: Paul Hermans
Date: 16 Mar 2006 03:12 PM
I attach the datamodel.
The test is on integers.
The included code does the same select twice, but we tested this also with other selects related to different tables.


Imagedatamodel.png
datamodel

Postnext
Ivan PedruzziSubject: Error when using two nested sql/xml selects
Author: Ivan Pedruzzi
Date: 16 Mar 2006 04:03 PM
Hi Paul,

You need to help the query engine to do the right thing :)
Please start your query with the following line

/*{jxtr-hints rewrite_algorithm=nested_loop */
SELECT
XMLELEMENT(NAME "row",
...

For more details on algorithm hints see

http://media.datadirect.com/download/docs/connectsqlxml/csxug/concepts.html#wp131898

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Postnext
Paul HermansSubject: Error when using two nested sql/xml selects
Author: Paul Hermans
Date: 16 Mar 2006 05:02 PM
Thanks for the, as always, prompt and effective replies.

I'll give it a try tomorrow.


Posttop
Paul HermansSubject: Error when using two nested sql/xml selects
Author: Paul Hermans
Date: 20 Mar 2006 09:40 AM
Originally Posted: 18 Mar 2006 03:57 PM
Tested but getting now:<br> Error message:<br> java.lang.OutOfMemoryError: Java heap space; <br> BUT extending the heap space does work. Problem solved.

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.