Subject: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?
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"
for $account in collection("db2.dbo.Accounts")/Accounts
where $account/CustomerId = $customerID
for $investment in collection("db1.dbo.Investments")/Investments
where $investment/CustomerId = $customerID