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
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 15 Feb 2007 02:56 PM
I have an Xquery that gets multiple rows from an XML document and retreives data from a relational database based on the data in the row.

The query is getting the relational data for the first row but not the second. I am using the DataDirect Processor. I have attached the source XML file (txt2xml2.xml), the query code (MyXQuery001.rtf), and a screen shot of the summarized results.

Can anyone tell me why it doesn't access the relational database for the second row?

Thank you.
James.


Applicationtxt2xml2.xml
source XML file

ApplicationMyXQuery001.rtf
XQuery Source file

Imagessss02.gif
Screen shot of xquery results

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 15 Feb 2007 03:29 PM
I guess the problem is caused by the BVCUST column in the database being defined as a field of 8 characters; which means in the second row you try matching "MI-AL03" (XML) with "MI-AL03 " (DB); try using the rtrim() function:

declare function ddtek-sql:rtrim($namevalue as xs:string) as xs:string external;

<Root>
{
for $row in doc('file:///c:/Stylus/txt2xml2.xml')/root/row
return
<all>
{
for $CUST in collection("MYDB.MYLOC.CUST")/CUST
where ddtek-sql:rtrim($CUST/BVCUST) = $row/shipto
return
<Cust>
<CustNum>
{$CUST/BVCUST/text()}
</CustNum>
<CustNam>
{$CUST/BVNAME/text()}
</CustNam>
{
for $OCRHX71 in collection("MYDB.MYLOC.OCRHX7")/OCRHX7
where $OCRHX71/DCSCUS = $CUST/BVCUST
return
<Order>
<OrdNum>
{$OCRHX71/DCORDN/text()}
</OrdNum>
<OrdDat>
{$OCRHX71/DCODAT/text()}
</OrdDat>
<OrdTyp>
{$OCRHX71/DCOTYP/text()}
</OrdTyp>
<OrdPO>
{$OCRHX71/DCPO/text()}
</OrdPO>
</Order>
}
</Cust>
}
</all>
}
</Root>

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 16 Feb 2007 07:57 AM
You are on to something. I think it is related to the space character. When I swap the rows in the txt2xml2.xml file then the query works for the key with the space character. It won't work with the rtrim function. Thanks for the suggestion though.

I haven't worked with the debug capabilities yet, so I will attempt to set a watchpoint to see what happens with the key.

Thanks for any suggestions.

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 16 Feb 2007 10:38 AM
When using DataDirect XQuery, you want be able to do step by step debugging; you can do that using the built-in processor, but then the semantics of how strings are matched might be sllightly different (DataDirect XQuery pushes the "where" condition to the SQL database; the built-in processor resolves that in memory).

To make sure spaces are not an issue, you can add this declaration at the beginning of the XQuery (when running with DataDirect XQuery):

declare option ddtek:sql-options "ignore-trailing-spaces=yes

If it still doesn't work, can you share with us details about the type of the columns of the CUST table?

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 16 Feb 2007 02:15 PM
It worked! Only once though.

I added the "ignore-trailing-spaces=yes" option. The query brought back both customers. The strange thing is that if I run the query again (hit the green arrow), I am back to only the first customer. If I close Stylus Studio and start over, it will again give me both customers for the first query, but only the first customer for subsequent queries.

Thanks for the help.

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 16 Feb 2007 03:03 PM
James,
I tried reproducing the behavior you are describing without luck.

Do you get the same result if you run the XQuery using the built-in processor (removing the ddtek: option first, of course)?

Can you email us the XQuery and XML files you are currently using? Can you also describe the exact column types in the database schema you are querying?

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 16 Feb 2007 03:49 PM
Hi Minollo,

I have attached the following files.

MyXQuery002.rtf - the query from Stylus Studio.
OCRHX7(layout).txt - file layout from the IBM ISeries.
ssss01.gif - the screen shot of stylus studio results first time.
ssss02.gif - the screen shot of stylus studio results subsequent runs.
txt2xml2.xml - the source XML file.

Thanks for looking at this.

James.


Imagessss01(1).gif
screen shot 1

Imagessss02(1).gif
screen shot 2

DocumentMyXQuery002.rtf
XQuery

DocumentOCRHX7layout.txt
DB2 layout

Unknowntxt2xml2(1).xml
XML file

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 16 Feb 2007 03:56 PM
Sorry, the layout file on the previous post was difficult to read when I opened it. It is re-posted here.

James.


DocumentOCRHX7.txt
Iseries file layout

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 21 Feb 2007 10:13 AM
Originally Posted: 16 Feb 2007 05:29 PM
Still no luck trying reproducing this on a DB2 table with similar schema.

A couple of things to try:

- As I mentioned above, can you try running the Stylus Studio built-in processor and see what the behavior is? (you'll need to remove the ddtek: option)

- Can you change the XML to this and see if you get consistently two rows?
<root>
<row>
<shipto>PEL-KNNR</shipto>
<status>C</status>
</row>
<row>
<shipto>PEL-KNNR</shipto>
<status>C</status>
</row>
</root>

- Can you select XQuery > Generate Java Code..., generate, compile and run Java code and see if you get the same result you get in Stylus?

Thanks.

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 19 Feb 2007 09:20 AM
Using the built-in processor takes about 15 minutes and results in an empty set for both customers.
The modified XML did not improve the results with either processor.

When I compiled the query java code using the datadirect processor, it consistantly brings back all data.

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 19 Feb 2007 09:31 AM
This is getting more and more intriguing.

Two things:
- Can you email us the .java code generated by Stylus Studio, so that we can modify it to execute multiple times the same XQuery using the same DB connection, to simulate the same behavior that Stylus Studio has? (stylus-field-report@progress.com)

- Do you notice anything "interesting" executing the XQuery step by step when running the built-in processor in debug (add a breakpoint on the first instruction, and do F5; make sure to bring up the variable window to see the value of all the variables in place)

Thanks.

Postnext
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 19 Feb 2007 09:51 AM
The java code has been sent.

I will attempt the debug with the built-in processor. I haven't done that yet.

Thanks.
James.

Postnext
Minollo I.Subject: No results for second row of source document
Author: Minollo I.
Date: 21 Feb 2007 10:15 AM
Turns out the problem was caused by spurious 0xFEFF characters in the original XML files being used; you can see those characters opening the XML documents attached to this post with Stylus Studio.

Removing the 0xFEFF characters fixed the strange issue.

Thanks James for your great help in understanding this.

Posttop
James HansonSubject: No results for second row of source document
Author: James Hanson
Date: 21 Feb 2007 10:17 AM
Thanks to Ivan and Carlo at datadirect.com for their work on this problem. They found that a character (0xFEFF) in the source XML files was causing the funky query behavior. How that got there is still a mystery too me. After removing the offending characters, it works with consistant results.

 
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.