Download to read offline
Using database object relational storage
- 1. sysco.no Using database Object-Relationalstorage for storing and manipulating XML data in an integration project Welcome to the Tour
- 2. sysco.no Oracle SOA/ESB commondatabase problems • Store large ammounts of XML data in database • Picewise update of different XML complex types in database • No direct support from SOA/ESB platform to save or read XML data from database • Different database storage models for XML related data. Which to chose? • Oracle database XML DB extension About SYSCO
- 3. sysco.no Read and writeXML data to database • Relational model – One table for each XML complex type • XMLType view – Efficient in one direction • Storing XML data directly – CLOB – Binary XML – Object-Relational storage
- 4. sysco.no Storing XML datain prepared database schema • Huge task for database designer – One table for each XML complex type • Familiarity with reading and understanding XML schema document by database designer. – Compatibility and constraints • A lot of database relations – Primary to foreign key relationships
- 5. sysco.no Excerpt from WorkOrder.xsdXML Schema
- 6. sysco.no E-R diagram producedfrom complex XML Schema file
- 7. sysco.no Relational database modelproblems • Complex XML Schema = complex database model – Substantial time to prepare – Data type translation (string to varchar2 limitation) • Many atomic XA database transactions. – A lot of ESB Business Services or SOA partner links – The whole transaction logic handled by SOA/ESB layer – A lot of XPath expressions and transformations
- 8. sysco.no What is XMLDB? • XML DB Introduction • XML DB Advantages • XMLType view • XML DB storage options • SOA access to XML DB • Object-Relational storage
- 9. sysco.no XML DB Introduction Byleveraging Object Types, Oracle has added to standard relational database XML processing support. For that the special Object Type has been used: XMLType. Around XMLType Oracle has created whole stack of technology add-ons to support XMLType data processing.
- 10. sysco.no XML DB Introduction ImportantXML DB technology additions to Oracle database: 1. XMLType data type, 2. XML DB repository, 3. CLOB mapping, 4. XML Binary mapping, 5. Object-Relational storage, 6. Relationships, 7. Indexing, 8. XMLType views, 9. Relational view from XMLType data 10. XQuery support, 11. XML Schema validation 12. XML Schema extension, 13. DOM fidelity, 14. Etc.
- 11. sysco.no XML DB advantages Advantagesof using XML DB for storing and manipulating XML data: 1. Faster than many native XML databases (Object-relational storage, Relationships, Indexing) 2. XML Schema validation, 3. Conversion from XML to relational data and back, 4. Virtual, versioned file system support, 5. Multiple protocol support for data exchange, 6. Read-Write one transaction support for whole XML document, 7. XML Piecewise update based on XPath expressions, 8. Relational data database Views based on XPath/XQuery expressions, 9. Support for updates of XML from relational views by using Instead Of triggers and XQuery expressions.
- 12. sysco.no Using XMLType view •Better performance – Whole XML inserted in one transaction – SQL: INSERT, UPDATE and DELETE operations accept whole XML document
- 13. sysco.no SQL: INSERT, UPDATEand DELETE operations on XMLType object.
- 14. sysco.no Using XMLType view •Preparation steps 1. Create XMLType View 2. Create PL/SQL wrapper faction, that will convert XML data from database large string format (CLOB) to XMLType format 3. Create INSTEAD OF trigger attached to the XMLType view that will perform actual update of database data stored in relational tables
- 15.
- 16. sysco.no INSTEAD OF triggerattached to the XMLType View example
- 17. sysco.no Using XMLType view •Disadvantages: 1. Still requires classic relational storage for storing XML data 2. The INSTEAD OF trigger attached to the XMLType view is considerably big and has to contain more or less the same XPath expressions that we would otherwise had to add inside Enterprise Service Buss Pipeline or inside SOA BPEL process
- 18. sysco.no Using XML DBstorage options for storing XML data • XML DB various storage options: – CLOB – Binary XML – XML DB Repository – Object-Relational
- 19. sysco.no Using XML DBstorage options for storing XML data • CLOB storage option advantages: – Simple to prepare (single database table for whole XML) – Simple to implement • CLOB storage option disadvantages: – Space occupation – For every update, insert or delete on any XML element, the whole XML document needs to be rewritten – Only text based indexing
- 20. sysco.no Using XML DBstorage options for storing XML data • Binary XML storage option advantages: – Simple to prepare (single database table for whole XML) – Simple to implement – Better space occupation – Better indexing options – Faster than Object-Relational storage for whole XML inserts/updates • Binary XML storage option disadvantages: – Piecewise update – Lack of BTree indexes – More complex XPath for creating relational type views out of the nested XML Schema complex types
- 21. sysco.no Using XML DBstorage options for storing XML data • Object-Relational storage option advantages: – Storage optimization – Better indexing options – Fast for XML piecewise updates – Various indexing options – Schema validation – XML DOM fidelity – Check and unique constraints – Automatic relationships generation – Simpler for creating relational table views on nested XML Schema complex types • Object-Relational storage option disadvantages: – Slower than Binary XML for whole XML insert/update operations – Potential dead locking in RAC environment during the update operations
- 22. sysco.no SOA access toXML DB Why XML DB with SOA? 1. Single transaction for Read and Write whole SOAP/REST XML, 2. Just one “Exposed Service” creation, 3. No need to prepare classic relational schema, 4. Speed and simplicity!
- 23. sysco.no SOA access toXML DB SOA support for XML DB? 1. No native support, 2. Some preparation steps, 3. PL/SQL knowledge requirement.
- 24. sysco.no Using Object-Relational storagefor storing XML data How-To XML Schema Registration Upgrade XML Schema with XML DB annotations Stored procedure Create stored procedure to enable one transaction support DB Adapter Prepare DB adapter to use stored procedure XQuery mapping Prepare XQuery mapping to map XML to stored procedure parameter DB Views Create DB View to expose XML data as relational
- 25. sysco.no Upgrade XML Schemawith XML DB annotations
- 26.
- 27. sysco.no Object-Relational storage perspective •XML Schema registration procedure creates and registers all database objects for storing XML data. – Every XML complex type becomes one XMLType table – For every XML complex type one Object Type is generated that validates XML data against XML schema and can be tuned • Object-Relational storage is still of XMLType and has to be treated as such – To access data as relational prepare views with XQuery syntax – INSTEAD OF trigger that will use XQuery syntax to modify underlying XMLType table
- 28. sysco.no Database view withXQuery syntax to extract XMLType data
- 29. sysco.no Database procedure forstoring XML data
- 30. sysco.no Database procedure forreading XML data
- 31.
- 32. sysco.no Graphical representation ofXQuery mapper file that maps XML to string
- 33. sysco.no XQuery mapper codethat maps XML to string
- 34.
- 35.
- 36. sysco.no Conclusion Go for it! 1.Best option for storing large XML files with the need to perform piecewise XML insert, update, delete 2. PL/SQL knowledge requirement for executing preparation steps 3. Most of preparation steps is one time job applicable to different projects 4. Full XQuery support 5. User friendly for integration developers
- 37. sysco.no Have a niceand inspirational day! SYSCO 2016