Agenda 01 – History of the framework

Agenda

• 01 – History of the framework

• 02 – Oracle XML overview

• 03 – TVD-XORA Working environment

• 04 – TVD-XORA Demo

• 05 – What’s next ?

History of the framework

• Hoffmann La Roche ltd Basel

• Master Data Management Services : Trivadis SLA

• WebDB  Portal ?

• WEBDB has some strong limitations (Rowid, aggregated views, HTP, Excel or PDF output, etc . . .)
• The new Release is integrated in IAS Portal

• New Projects

• New requirements : Excel, PDF, SVG, Multiple updates, etc ....
• Low budgets, rapid development
• Basic know how : PL/SQL, Webdb, javascript, XML
• Web server is an old OAS
• Other options : java + Struts + Cocoon

Agenda

• 01 – History of the framework

• 02 – Oracle XML overview

• 03 – TVD-XORA Working environment

• 04 – TVD-XORA Demo

• 05 – What’s next ?

Oracle XML Components

• Oracle XML Developer's Kit (XDK)

• XML Parser and XML Schema Processor
• XSLT Processor
• XML SQL Utility (XSU)
• XSQL Servlet

• Oracle9i features

• New datatypes XMLTYPE and URITYPE
• The PL/SQL package DBMS_XMLGEN
• Oracle XML DB

• Oracle Text, Advanced Queuing, etc ….

XDB

• Foldering

• Versioning

• ACL

• Webdav, FTP

• Schema Validation

• SQLX functions

• Etc ...

Oracle9i XML Generation : Overview

XDB Features used in our framework (1)

• XMLType datatype

• To manipulate, store, validate, transform XML data in the database
• The object type XMLType provides a number of useful methods that operates on XML content

• DBMS_XMLSchema

• To register an XML Schema in the database
• XML documents can than be validated against it
• An XMLType can be associated with an XML Schema to store data in an Object-relational way instead as a CLOB

XDB Features used in our framework (2)

• DBMS_XMLGEN Package

• to generate a result set based on a select statement

• SQLX functions

• to aggregate XML outputs

• XML Stylesheets transformation capabilities in the Database

• XMLType Views

Agenda

• 01 – History of the framework

• 02 – Oracle XML overview

• 03 – TVD-XORA Working environment

• 04 – TVD-XORA Demo

• 05 – What’s next ?

XML tool : XMLSpy

• Maintain XML Documents

• Maintain XML Schemas

• Maintain Stylesheets

• Test result set transformation with stylesheets

• Stylesheets generator : HTML, PDF

• Strong integration of Oracle XDB features

What is an XML Schema ?

• Commonly used as a validation mechanism

• Gradually replacing the traditional DTD

• A rich grammar describes XML documents

• XML Parser checks XML document against grammar  valid

XML Stylesheet transformation

• An XML Stylesheet transforms an XML document into another XML document, or another type of textual document.

• This transformation can take place in the Database, on the application server or on the client

• XSLT is a language for transforming XML documents into HTML, Text, XML, SVG, Excel, Word, etc …

• XSL Formatting Objects (XSL-FO) is a vocabulary for formatting XML documents into PDFs

What is an SVG ?

• It‘s an XML based graphic representation

• It‘s a W3C recommendation

• Data can be extracted from the DB in an XML format and transformed with a stylesheet into an SVG.

• SVG Graphics requires a viewer such as Adobe SVGViewer

XML DB and XML Schema

• XML Schema controls all aspects of processing

• Storage mappings
• In-memory representations
• Language Bindings

• XML Schema Registration Process

• Associates XML Schema with URL
• Generates Object types, Nested tables and Varrays
• Creates default tables

• XMLType column can be constrained to a global element of registered schema

Object-Relational Storage of XML Documents

• An XMLType table can store XML documents as a CLOB or as Object-Relational.

• The XML documents are ’broken up (decomposed)’ into object-types, nested tables, varrays and CLOBs.

• Native XMLType instances contain hidden columns that store this extra information that does not quite fit in the SQL object model

• An XML Schema must be registered in order to store the documents as Object-Relational

XML Schema Registration

• XML Schema are registered by using procedure DBMS_XMLSCHEMA.REGISTERSCHEMA()

XML Schema Deletion

• The package DBMS_XMLSCHEMA contains a procedure to delete the XML schema

• Deleteschema removes all related objects to this XML schema (Tables, collections, object types)

• The cleanup doesn‘t work properly in some cases.

• Bug !

XML Schema : Oracle User-Defined Mapping

• XDB uses annotated XML Schema as metadata, that is, the standard XML Schema definitions along with several Oracle XML DB-defined attributes

• These attributes are in a different namespace and control how instance documents get mapped into the database
• The Oracle XML DB namespace: http://xmlns.oracle.com/xdb

XML Views

• XML views (known as XMLType views as XML document represented using built-in XMLType)

• wrap existing relational as well as object-relational data into XML formats

XML Schema validated views

• The output is an XMLType result set

DBMS_XMLGEN Package

• DBMS_XMLGEN generates XML documents from any SQL query

• It returns the XML document as a CLOB or XMLType

• Provides a "fetch" interface very useful for pagination requirements in Web applications

• setMaxRows(): specifies the maximum of rows returned by the Web Application
• SetSkipRows(): specifies the amount of rows to skip

DBMS_XMLGEN Package

• "fetch" interface

• setNullHandling(): if set to 1, the resulting XML document contains empty elements when a value is NULL. The becomes very handy for reports generated out of the resulting XML document
• getNumRowsProcessed(): returns the number of rows processed. This count does not include the number of rows skipped before generating the XML
• setConvertSpecialChars(): to replace characters such as <, > etc.. by their codes (<, > etc..)

• The "fetch" interface is very performant

DBMS_XMLGEN Package

Generate HTML Output with XMLTransform

Agenda

• 01 – History of the framework

• 02 – Oracle XML overview

• 03 – TVD-XORA Working environment

• 04 – TVD-XORA Demo

• 05 – What’s next

Web reporting Application

• This XDB based Web Reporting framework generates public reports as well as maintenance reports

• The demo application is based on this technology and uses the Oracle HR schema to demonstrate the use of such an application

• The reports/forms technical descriptions as well as the documentation are stored in schema-validated XML documents

Parameter Screen and Menu

Generated HTML output

HTML Maintenance Form

HTML Report with Blocks

Excel output

PDF Output

Master Detail HTML Output

HTML Output based on XMLType Views

SVG Graphs

Web reporting : concept

XDB Application : transformation

Web reporting Application

• The development and maintenance of the XML reporting documents is done with XMLSpy

• Each document is validated against an XML Schema (Report.xsd) inside and outside the database

• The load process of the XML documents in the XMLType table is done via FTP

• Note: XMLSpy version 5.0 has the option to directly read or update XML documents stored in XMLType tables

• A PL/SQL package generates the reports, forms and SELECT statements based on the information contained in the XML document

Web Reporting : XML Schema

• The report.xsd XML schema describes the structure, content and semantics of all Reporting XML documents

• Special attributes in the XML schema are interpreted as instructions by ORACLE XML DB

Web reporting : implementation

Web reporting : Database storage

• The stylesheets are stored in a relational table: TVDXORA_XSLT

• Only one column of this table is of type XMLType

• The tree menu is stored in a separate XMLType table: XML_MENU

• The reports in an XMLType schema-validated table: XML_REPORTS

The TVD-XORA Application

Views and Instead of triggers

• The select statement use to generate the XML result set is generally based on a VIEW associated with an INSTEAD OF TRIGGER.

• There is still some HTML coding in theses view, but future releases will remove it.

• The HTML error messages are defined in the Instead of trigger

The XML Result set

SQLX aggregation

TVD-XORA Application: Stylesheets

• The stylesheet s01_select.xslt generates the SQL SELECT statement

• The SELECT statement sent do DBMS_XMLGEN is only keeping the selected options in the where clause for better performances

TVD-XORA Application Summary

• The application is easy to implement

• About 20 Stylesheets
• 2 XML Schemas
• A central PLSQL packages in 1 DB Schema export and import possible

• It’s a simple and fast way to generate forms and reports for reporting environments

• Only XMLSpy knowledge and some SQL knowledge is required to design reports

• The application documentation is automatically generated

• Interesting for validated applications

• Better performances when compared to WEBDB/Portal

Agenda

• 01 – History of the framework

• 02 – Oracle XML overview

• 03 – TVD-XORA Working environment

• 04 – TVD-XORA Demo

• 05 – What’s next ?

TVD-XORA Success story

• ROCHE

• Meanwhile we have implemented this Framework in three new projects
• More XML Based projects are in the pipeline
• XDB + Mod/PLSQL
• XDB + Cocoon

• ORANGE Lausanne

• An application based on this framwork is now running productive

• Our experience shows that Oracle XML DB is a robust platform for building XML applications

Planned improvements

• Possibility to access the application with JSP instead of mod_plsql

• The final stylesheet transformation will take place on the application server

• COCOON Integration with XDB

• It‘s a XML based technology

• New stylesheets,documentation, better naming conventions, etc ... but still maintaining back compatiblity