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
sebastien vinchonSubject: Using Data Direct xQuery to agregate data from various sources including RDBs into xML
Author: sebastien vinchon
Date: 31 Mar 2008 01:01 PM
Hello xQuery Gurus,

I am working on putting together for a large telecom company a demo (to be delivered on Wednesday) showing how the DataDirect xQuery engine can be used to retrieve data from various sources including RDBs to generate XML.

I created two MS SQL 2005 DBs called db1 and db2 owned by a user called db with db as the password.
db1 contains a unique table called Investments with the following fields: StockName, StockCode, SharesCount, CustomerId.
db2 contains a unique table called Accounts with the following fields: CustomerId, AccountLabel, AccountType.
I am using Stylus Studio Enterprise Edition 2007.

Could someone provide me with a sample xQuery code to generate XML containing the list of Investments and Accounts for a specific Customer Id?

Are there specific things to be careful about so that the performances are good even if the amount of records in each table is large?

Many thanks in advance for any help.

Sebastien Vinchon

Postnext
Minollo I.Subject: Using Data Direct xQuery to agregate data from various sources including RDBs into xML
Author: Minollo I.
Date: 31 Mar 2008 01:20 PM
Something like this should get you started; to test it in Stylus Studio, use File Explorer to connect to the database server, and drag&drop db1 and db2 to the panel to the right of your XQuery source editor. You can also ask Stylus Studio to generated Java code (XQuery -> Generate code) to see how you would use DataDirect XQuery in your code to setup connections and execute the query. Of course you can also make "$customerID" an external variable, and bind it dynamically from your Java code.

You shouldn't need to worry about performance; DataDirect XQuery is able to optimize the way your XQuery interact with a RDBMS data source without the user having to think about it. If you are interested in more details about how that process works, some technical description is available in this white paper: http://www.xquery.com/white_papers/generating_sql/index.html

let $customerID := "xyz"
return
<report customer="{$customerID}">
<accounts> {
for $account in collection("db2.dbo.Accounts")/Accounts
where $account/CustomerId = $customerID
return $account
} </accounts>
<investments> {
for $investment in collection("db1.dbo.Investments")/Investments
where $investment/CustomerId = $customerID
return $investment
} </investments>
</report>

Postnext
sebastien vinchonSubject: Using Data Direct xQuery to agregate data from various sources including RDBs into xML
Author: sebastien vinchon
Date: 01 Apr 2008 04:43 AM
You guys are really impressive at providing good feedback rapidly.

After I am done making tons of money working for my current company, I want to work with you guys :-)

Posttop
sebastien vinchonSubject: Using Data Direct xQuery to agregate data from various sources including RDBs into xML
Author: sebastien vinchon
Date: 01 Apr 2008 05:39 AM
Originally Posted: 01 Apr 2008 05:27 AM
I just had to replace:
- "$a/CustomerId = $customerID" by "where starts-with($a/CustomerId, $customerID)"
- "$i/CustomerId, $customerID" by "where starts-with($i/CustomerId, $customerID)"

Because there were some trailing spaces in the CustomerId field.

And it now works just great.

Many thanks.

 
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.