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)
-> + element type must not contain ... (3)
-> + XML to .txt file (7)
-> + Modifying a XSL-FO report (5)
-> + Retrievning multiple values...... (2)
-> + does Stylus Enterprise work wi... (2)
-> + MSSQL to 837P conversion (4)
-> + Generating XML files accesing ... (6)
-> + How to bold text using XSL (6)
-> + Different heading based on pag... (3)
-> + Code folding (2)
-> + Bad param=value pair on comman... (2)
-> + Saving an XML file (2)
-> + stylus adding random non print... (2)
-> + Displaying Base64Binary as Ima... (6)
-> - probleme de representation d'i... (1)
-> + SS 2008 error message not disp... (8)
-> + XQuery external variable conve... (4)
-> + encodings available (3)
-> + stand-alone application file n... (4)
-> + Windows Installer Dialog Box a... (4)
-> + Block search and replace (2)
-> + Problem with validation (3)
-> + ISA12 vs GS08 (5)
-> + Web Service Call failure... an... (11)
-> + Quotes in xpath query (3)
-> + Java Built-in processor no lon... (6)
-> + Dynamic input and output names (8)
-> + Java built-in parser (3)
-> + Contains an invalid path (2)
-> + Wizard XML Schema to XML (2)
-> + XML to EDI (14)
-> + Computer with original install... (2)
-> + using Custom Tool - passing ar... (3)
-> + Non-printable special characte... (3)
-> + Transfer Options (Settings, Pr... (2)
-> + Enterprise Edition, Rel. 3, Bu... (9)
-> + Need help with xsl file and xm... (2)
-> + Upgrading and retaining settin... (4)
-> + saxon9.dll etc. missing (2)
-> - Error on using XQuery File in ... (1)
-> - Strange Dialog Box (1)
-> + Studio Crashes in schema edito... (3)
-> + Xml to XMl Conversion (2)
-> + Unable to create a complex typ... (2)
-> + Error message wen trying run j... (2)
-> - XSLT Mapper question... (1)
-> + Dcument Wizard XSLT Editor Tab... (2)
-> + Beginner Question - XPath with... (2)
-> + Trying to generate .net code w... (2)
-> + duplicate values in variable w... (5)
-> + Java Runtime Environment libra... (4)
-> + programmatically generate XML ... (4)
-> + Help Loading Oracle BI Publish... (4)
-> + [French] xsl <=> report (3)
-> + XML Diff (3)
-> + StylusStudio 2008, saxon saxon... (6)
-> + Msql Table err:FODC0004 Table ... (2)
-> + The evaluation period for Data... (2)
-> + Mysql to XML (2)
-> + Is there a Collapse All nodes ... (2)
-> + flat file conversion with CDAT... (4)
-> + Struzzo.exe eats up %40 CPU ba... (2)
-> + schematron support? (11)
-> + XML Generator did not generate... (2)
-> + Nesting XML files (2)
-> + Web Service Call fails - targe... (5)
-> + getting not valid for content ... (2)
-> - element modem was referenced i... (1)
-> + xerces-c++ xml parser (2)
-> + movie.dtd (2)
-> + HELP! Stylus Studio 2008 Enter... (3)
-> + Xalan problem with xsl:attribu... (2)
-> + Breakpoints being ignored (12)
-> + Need a new key for Stylus Stud... (2)
-> + Global parameters are not reco... (4)
-> + mySQL DB Connection 2 (10)
-> - Using Data Direct xQuery to ag... (4)
-> ->Using Data Direct xQuery ...
-> ->Using Data Direct xQuery ...
-> ->Using Data Direct xQuery ...
-> + stylusXslt command line -- XSL... (15)
-> + XSLT processor using EXSLT fun... (2)
-> + Integrating Omni Mark (2)
-> + EDI Standards to gXML (2)
-> + flat file conversion (3)
-> + EDI to XML: Invalid URI error (3)
-> + Validating and XML Schema (6)
-> + XSD to DDL (MS-SQL/2005) (6)
-> + xml converter cant encode 0x19 (4)
-> + XSD Help Needed (2)
-> + How do you delete a field in a... (2)
-> + Disable page breaks (3)
-> + XSD semantic structure compari... (3)
-> + Studio Crashes On File Open (7)
-> + Which parser is used by Stylus... (2)
-> + How to DIFF XML windows with d... (3)
-> + Automation using Visual Studio (7)
-> + CSV to XML conversion help (2)
-> + XML tree help (3)
-> + java.lang.NullPointerException... (2)
-> + Creating schema from xml - how... (3)
-> + Mismatch between intellisense ... (4)
-> + Using FOP 0.94 in XML Pipeline (2)
-- Previous [1081-1100] [1101-1120] [1121-1140] 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.