Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


More on XML Publisher - Part II
January 18, 2006
Mark Rittman

Next up then I loaded up Oracle Reports Builder (part of the most recent BI Tools download bundle) and built a report against the Scott/Tiger schema. The way Reports Builder works is that you first select the type of report layout you want:

Then you select your datasource, and then your SQL query, Reports Builder then lays out the report in the format you've selected.

What I wanted to do was to generate a type of report that I think XML Publisher, with it's basic SQL query dialog, would find hard to support, something like this:

with the idea being that I'd get Reports Builder to export the report results as XML, and see whether XML Publisher could use this as a data source. From the Reports Builder menu, you can select File > Generate to File > XML from the toolbar, which outputs an XML file that in my case looked like this:

<MODULE1>
- <LIST_G_DNAME>
- <G_DNAME>
  <DNAME>ACCOUNTING</DNAME>
- <LIST_G_ENAME>
- <G_ENAME>
  <ENAME>CLARK</ENAME>
  <MGR>7839</MGR>
  <HIREDATE>09-JUN-81</HIREDATE>
  <JOB>MANAGER</JOB>
  <DNAME1>ACCOUNTING</DNAME1>
  <DEPTNO>10</DEPTNO>
  <DEPTNO1>10</DEPTNO1>
  </G_ENAME>
- <G_ENAME>
  <ENAME>KING</ENAME>
  <MGR />
  <HIREDATE>17-NOV-81</HIREDATE>
  <JOB>PRESIDENT</JOB>
  <DNAME1>ACCOUNTING</DNAME1>
  <DEPTNO>10</DEPTNO>
  <DEPTNO1>10</DEPTNO1>
  </G_ENAME>
- <G_ENAME>
  <ENAME>MILLER</ENAME>
  <MGR>7782</MGR>
  <HIREDATE>23-JAN-82</HIREDATE>
  <JOB>CLERK</JOB>
  <DNAME1>ACCOUNTING</DNAME1>
  <DEPTNO>10</DEPTNO>
  <DEPTNO1>10</DEPTNO1>
  </G_ENAME>
  </LIST_G_ENAME>
  </G_DNAME>

... etc

Bingo. Now, I went over to XML Publisher, brought in the document as the datasource for the report, and then selected Insert > Field from the toolbar. The field selector looked like this:

Pretty much as I expected to see - employee details embedded within the Employee node, Employees embedded within Departments and departments embedded within the "Module 1" node.

The next step then was to use this data to lay out a report, as close to the Reports Builder version as possible. To do this, I used the Insert > Table / Form option rather than the Insert > Field option, as this is what you'd need to create a report with repeating rows.

Note how I brought both the Dname and Ename nodes - this will allow the report to break on department, and then display rows consisting of employee details. After adding the items to the template, and having a fiddle around with the layout, it looked like this:

Note how the breaking and bursting bit works - the XML Publisher template builder add-in adds macro text into the document (the "for-each G_DNAME" bit) which then tells XML Publisher how to split out the sections. In my case I've chosen to have all departments on the same page, but you could just as easy have each department on it's own page, with a page break in-between. It took about 10 minutes to work out how to do it, a lot less time than it takes to learn how to do it using Oracle Reports Builder (see here for some notes on that, a very painful exercise), not bad actually once you work it out. Anyway, when you go to preview the document, it looks like this:

So, what have I worked out from this. Well, firstly, although XML Publisher does support SQL as a datasource, it's all a bit primitive and will probably be considerably revamped in a future version. If you want to use the tool properly though, you're probably better off trying to format your data as an XML document, either using Oracle Reports or something like XQuery that can format your data properly. Looking at the XML that XML Publisher requires, it's not all that complex and there's no need for things like DTDs, XML Schemas and so on - it's almost as simple as HTML in it's layout. In terms of how you use the tool, things like bursting, grouping and data and so on are done though a combination of the structure of the data itself - the data you're going to burst will be hierarchically embedded in the data you're going to burst it by - together with the Insert > Table/Form dialog and the page formatting features within Word itself. I guess in the end it just comes down to the inherent structural difference between XML data and database data - one's hierarchical and self-describing, whilst the other is just rowsets.

Nuno Souto posted a comment on yesterday's posting asking how you go about putting XML Publisher reports into production. That's the next thing I'll be looking at, when I take a look at the server element of XML Publisher.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational