Integrating a Heterogeneous Environment using XML

1. Integrating a Heterogeneous Environment using XML Sandeepan Banerjee Director, Oracle Server Technologies

2. Synthesized Information Excel Files on Disk Financial Application CRM Application E-Mail and Document Repositories Contract Management Application The Problem • Multiple domain-specific applications • Manufacturing, Inventory, Supply Chain, Financial, … • Information is trapped within these applications How does an organization get a consolidated view of its information – in real time ?

3. Technical Challenges • Domain-specific information • Replication does not make sense • Independent operation of applications • Access to information has to be in real-time • Different access method for each application • Each application has its own protocol and access method What architecture can best accommodate my present and future needs ? • Complexity: Avoid the n by m matrix • Flexibility: Add new sources easily • Time to market: Within days, not months

4. What is XML-based Data Integration? XML-based Data Integration or Enterprise Information Integration (EII): • Create aggregated views using XQuery • Get information from diverse sources in XML • Consume synthesized information

5. XML Data Integration Example XQuery Engine Synthesized Information Order Tracking J2EETM CA EIS JDBC HTTP Parts Inventory Shipment Tracking Database Web Service

6. XQuery Engine Technologies Involved • Why XML? • Different data formats • Why XQuery ? • Declarative way to query XML documents • Why J2EETM ? • Standards-based infrastructure platform • Why XML Database ? • Native XML storage • XML data management • Performance optimizations XML-based Data Integration J2EETM Platform XML Database

7. Comparison with Existing Technologies

8. XQuery Status • XQuery is emerging as the consensus ‘native’ query language for XML • Expected W3C Recommendation in late 2005 • Oracle 10gR2 is the first mainstream commercial database release to support XQuery • Plan to release under an event in 10gR2

9. XQuery Example Assume a document – emp.xml <empset> <emp empno=“21” ename=“SCOTT” salary=“120000”/> <emp empno=“22” ename=“JONES” salary=“344000”/> </empset> To get the names of employees with salary > 200000 for $i in document(‘emp.xml’)/empset let $j = 200000 where $i/@salary > $j return $i/@ename Result (attribute node) JONES

10. Another Example Customer/Address/Zip ShipTo/Address/Zip Supplier/Location/AddressForTaxCalculation/Zip Customer/Address/Work/City/Zip Customer/Address/Home/City/Zip Shipper/DropOffLocation/US/California/SFO/Zip + any other zip nested to any unanticipated level count ( for $i in doc("contacts.xml")/Contact where $i//zip eq 94065 return $i)

11. Example III: Auction/Bids • Say we have 3 heterogeneous data sources that yield their contents as XML • Item Description comes from a Filesystem • User Information comes from LDAP/DB • Bid Information comes from an App Server -- For all bicycles, list the item number, description, highest bid (if any), ordered by item no. for $i in doc("items.xml")//item_tuple let $b := doc("bids.xml")//bid_tuple[itemno = $i/itemno] where contains($i/description, "Bicycle") order by $i/itemno return <item_tuple> { $i/itemno} { $i/description } <high_bid>{ max($b/bid) }</high_bid> </item_tuple>

12. XQuery and SQL • Existing relational applications will continue to use SQL, and Oracle will remain the industry’s best implementation of SQL • New applications based on XML will use XQuery, and Oracle will be the industry’s best implementation of XQuery • Oracle will support XQuery both in the database and in the mid-tier • Use the mid-tier engine when you want to query non-database sources • Intelligent ‘query pushdown’ from mid-tier to db when possible • Can mix-and-match XQuery and SQL in same query

13. XQuery is different from SQL • Navigation-oriented (using Xpath expressions) • Different type system (XMLSchema based simple types) • Identity-based (XML Node identities and document order) • Namespace aware name-resolution (functions, variables, element creation) • XML-Item based vs Row-based • Results are heterogeneous sequences • Does not have all SQL extensions (e.g. data warehousing etc..)

14. XQuery Mid-tier Architecture Other Data sources XQJ API Driver XQuery Java Engine XQuery XQueryX XPath JDBC Driver DB Drivers Java XMLType SQL + XQuery or XQueryX

15. XQuery DB Architecture XQuery Parser Compiler XQuery Type check Normalization XQJ API XQuery Aware SQL Engine SQL Compiler Mid-Tier XQuery Engine XMLQuery, XMLTable XQuery User SQL Optimizer, Execution Engine SQLX/XPath

16. D E M O N S T R A T I O N XML Query

17. Oracle XML Database (XML DB) • Native XML storage • Available since Oracle Database Release 9.2 • Inherits RDBMS features: Security, Transaction,… • XML-specific features • XML indexing, XPath & XSLT support, XML schema validation, XML partial update • Supports SQL/XML • Allows blending relational and XML data operations

18. Leveraging Oracle XML DB • XML DB can be an XQuery source • Can define XML views of relational data • XQuery engine can rewrite query into SQL/XML • XML DB could also be used for caching • Efficient storage & indexing for large data sets • Can leverage security framework of XML DB • For both source and cache

19. Datasources • Databases • Relational+ XML Views, Object-Relational, CLOB, Compact XML (future) • Mid-tier sources • files, cache, JCA datasources • Bind (an existing DOM) • xmldatasrc – Oracle language addition • Datasource API • initialize • describe • execute • Fetch

20. iAS XDS Architecture Query Builder Tool Applications using XDS e.g. Portals, Reports etc XDS Client API’s EJB JSP Tags Web Service XML Data Synthesis J2EE Security Framework Cached XML Data Source XDS Cache Security Meta-data Repository XQuery Result XQuery Subsystem XQ4J/JXQI XDS Caching Service Oracle Enterprise Manager XQuery Engine XMLDataSource modules Stored Query XML DB Cached XML Data Source In-Memory XML Data source adaptors CCI-XML JCache File system J2CA EAI RDBMS Files HTTP Web Cache Java Functions Oracle Apps Web Services JMS SAP

21. Example – XDS usage • User registers webservice as datasource • XDS creates an XQuery module automatically • User Query for querying webservice import module namespace wss=“datasrc/stockws”; for $i in wss:getcompanies() return wss:get_stock_price($i/name) wss – namespace prefix for the loaded module JNDI lookup to get datasource implementation XDS adapters implement datasource

22. All Your Searches! • Search with anylanguage – SQL/XQuery/XSL • Search anywhere - mid tier or backend • Search anything -any XML/relational content • Search everything • XML visualization of all data (backend) • XML based adapters provide XML content for all data in mid-tier (XDS) • Search any form – text based/structured • Search any size - Scalable solution • Search any time - Unbreakable solution

23. More Information • XML in general • http://www.oracle.com/technology/tech/xml/index.html • XML Query • http://www.oracle.com/technology/tech/xml/xquery/index.html • XML DB • http://www.oracle.com/technology/tech/xml/xmldb/index.html

24. Q & Q U E S T I O N S A N S W E R S A