XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
search
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Home » Boards » Stylus Studio Developer Network » Stylus Studio Technical Forum » Using Data Direct xQuery to agregate data from various sources including RDBs into xML
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> + Varying Length Multiple Record... (2)
-> + Flat Flie to xml - validation (2)
-> + File Association (2)
-> + Generate XSL to transform from... (2)
-> + Installation Problem (3)
-> + compile error with generated j... (4)
-> + Help with Result of "Create Sc... (2)
-> + Region start position (line ve... (3)
-> + XSLT Mapper calling wrong temp... (3)
-> + Convert XSL to XSL:FO (5)
-> + Problem after downloading the ... (2)
-> + Moving node up / down (4)
-> + License transfer problem (2)
-> + Long element names in EDIFACT ... (4)
-> + Convert XML Schema to DB Table... (2)
-> + Connecting to DB2/400 through ... (2)
-> - Uninstall doesn't work (1)
-> + Pound (#) symbols appear inste... (3)
-> + Multiple file regions - dynami... (3)
-> + Cannot start Stylus studio pro... (2)
-> + XQuery missing Data when run (3)
-> + I need Help with a symple type... (2)
-> + Error while opening XML file f... (4)
-> + xquery variable substitution (2)
-> + Tab delemited Flat file to XML (17)
-> + Error on XSchema validation: "... (3)
-> + How to delete a file from a pr... (3)
-> + Generate Java Binding Classes (2)
-> + stylusstudio2008 suddenly stop... (2)
-> + Please Help (2)
-> - Looking in one tag to get the ... (1)
-> + Entity 'nbsp' was not found (2)
-> + Create 1 xml file for each lin... (4)
-> + SchemaLocation attribute (3)
-> + page break (4)
-> + name attribute of wsdl:fault e... (3)
-> + Cannot use my custom converter (3)
-> + java.lang.ClassNotFoundExcepti... (16)
-> + Witch product to buy (2)
-> + Trial version giving errors (2)
-> + Building and Integrating modul... (2)
-> + How to deploy pipeline (4)
-> + Is it possible to create xml f... (4)
-> + XML - CSV - XML Help! (2)
-> - xml file with many xsl style s... (1)
-> + Crash when using XML with DOCT... (2)
-> + Calling .Net classes from XSL ... (6)
-> + datadirect coverter result dif... (4)
-> + .net ConverterFactory().Create... (2)
-> + Dynamically created xsl used i... (4)
-> + Help with XSD attributes (3)
-> + Duplicated XML tag name in XML... (2)
-> + XML Publisher Repeater Bug wit... (4)
-> + HTML / FO selection (2)
-> + Command line interface to gene... (2)
-> + Provider org.apache.xerces.jax... (3)
-> + Problems with xsl:variable (2)
-> + any way to stop auto declarati... (2)
-> + Holding information in memory (9)
-> + Error whenever we try to read ... (4)
-> + Evaluation Versio Stylus Studi... (3)
-> + Convert linefeed-separated tex... (2)
-> + Transferring Stylus Studio fro... (3)
-> + Activation working on only one... (4)
-> + Populating Database from XML (2)
-> + Inserting Annotation (3)
-> + Project Window (2)
-> + DTD to Schema conversion gener... (2)
-> - Can't add multiple faults to a... (1)
-> + Soap fault name element (2)
-> + xml files supported outside of... (2)
-> + how to allow null values for d... (2)
-> + xml report query (5)
-> + Stylus Studio automatically ad... (3)
-> + Can we can stylus studio autom... (2)
-> + is it possible to insert into ... (3)
-> + Java Heap Space error (4)
-> + custom validation engines (3)
-> + W3C schema for enumerated taxo... (3)
-> + Using Stylus to generate elect... (2)
-> + WSDL editor and drop downs in ... (2)
-> + XSLT Mapper "forgetting" setti... (2)
-> + StylusStudio Crash: Error: Get... (7)
-> + Error in installation (2)
-> + Applying constraints & naming ... (5)
-> + Windows Installer prevents ins... (5)
-> + XML Schema to XML producing in... (8)
-> + calling javascript function in... (2)
-> + Repairin .xml file (2)
-> + HIgh CPU on a laptop after hib... (2)
-> - conditional rendering in XSL.. (1)
-> + Way to replace codes by labels... (4)
-> + Strange 'invisible' characters (4)
-> + syntax (2)
-> + Problem using mySQL (2)
-> + accessing Javascript variable ... (2)
-> + problem when openning files (2)
-> + Free Form Text (2)
-> + Cant open the database (4)
-> + Problem using MS Access (4)
-- Previous [1021-1040] [1041-1060] [1061-1080] Next
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
+ XQuery Help and Discussion (2017)
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
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.
   
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.