X

An Oracle blog about BI Publisher

  • XSL
    June 5, 2009

Building XSL templates

Spent some time over the last few days helping Bryan out on a project for a customer, I say helped but really just pushed him in the right direction, he joined the rather disparate dots and came up with the solution. The customer needed regular report outputs ie PDF, RTF, etc but they also needed an XML transformation. We tend to forget sometimes that BIP is very capable of transforming from one XML format to another format. After all, at its heart is an XSLT engine and what do they normally do? Transform XML of course!

So Bryan had the query and the ROWSET/ROW XML but needed to get to the customers format, they possessed an XML schema (XSD) for their format, he just needed the XSL stylesheet, simple right? Not quite so, without either investing in really learning XSL or purchasing an XSL mapper.

I had been looking at the BPEL engine recently and remembered that some of their tutorials had me use an XSL mapper inside JDeveloper. Just needed to fire it up and find it, I have been using 10.1.3.x JDev for my projects and searching through their documentation I found what I needed.

xslmapper.jpg

Its pretty neat and provides just about everything you might need in terms of mapping, it generates an XSL file for you and above all ... it's free!

If you want to try it out, you'll need the source XSD and target XSD and thereby hangs a tail. How to generate the XSD? they are not very nice to hand code and again you often need to invest in a tool to help build it. There are some options, a little known feature of the data engine in BIP is an API that will spit out a schema file based on a data template ... ahhh but you need to build out the data template first ... dang! Another option, Bryan found this little gem, is a free XML to XSD service out on the intertubes - http://www.flame-ware.com/products/xml-2-xsd/. Give it an XML and it will pass back an XSD. The XSD is only going to be as good as your input XML and you might need to tinker with it but for the purposes of mapping it should be more than sufficient. So, now you are armed with your two XSDs you can get the mapper fired up.

You just need to create an application and project in JDev then add a new XSL Map (New -> Integration Tier -> XML.)

xslmapper2.jpg

You will then be prompted for the source and target XSDs

xslmapper3.jpg

Use the Import Schema File ... button to bring your schemas into the project. The mapper UI will then pop up and you can start dragging and dropping source elements/attributes onto the target schema. You can 'run' the conversion at any time to check you are getting the desired result. Once happy, click the 'Source' tab, at the bottom of the mapping window to get to the XSL stylesheet.

Now its just a case of loading the XSL as a new template and setting the type to ensure BIP is expected to generate XML and you are off to the races as they say back in dear ol'Blighty.

Join the discussion

Comments ( 10 )
  • tony miller Monday, June 8, 2009
    Tim,
    So you are saying with jdeveloper and the xml service out on the web, you could take an xml stream and actual build a workable xsl stylesheet?
    Can you do any real layout with it? or is it just a straight dump of data?
    Why I am asking is, there is a growing demand from the APEX developer community for a low cost way to build PDF Files. (I LOVE BI Publisher [the apps I building & maintaining are hosted on a 3rd party site and they pay the cost of the product]).
    However, most folks are having a problem with the COST of Bi Publisher and want to use FOP or Cocoon, but they are looking for a visual editor (like the WONDERFUL one for BI Publisher via Word).. They are out there, just VERY Expensive..
    Thank you,
    Tony Miller
    Webster, TX
  • Ike Wiggins Tuesday, June 30, 2009
    Wow, this is an awesome post Tim! I didn't know this was available in JDeveloper!
    Recently, I had to create an xsl and this made the process so much easier vs manually doing it. It took about 5 minutes to create a basic xsl!
    Thank you,
    Ike Wiggins
    Minneapolis, MN
  • Cathy Saturday, October 17, 2009
    Hi Tim,
    I posted a question on the BIP forum about how to map my data to schema documents given to me, in order to upload the file to a website and you gave me the link to this post.
    I have gotten as far as expanding the Project Schema Files folder and I see the schema docs that I added to my project. The only problem is that the ones that were given to me have an exception.... Exception: Uable to parse schema xxx. When I expand the error I see "Can not build schema 'http://xxxx" located at "file:C\\xxx".
    So basically I am stuck and it won't allow me to create the xsl file. Have you run into this before where the schema doc you are trying to map to imports namespaces? Maybe it's just a setting that I haven't done in JDev? I have only used that to write my data source xml files before so I'm a newbie.
    Any help would be awesome because I'm on my own with this project!
    Thanks - Cathy
  • Angshuman Mukherjee Friday, May 28, 2010
    Hi Tim,
    I am new to BI Publisher. I am planning to generate pdf invoice from xml using xsl stylesheet. I already have xsl style sheet. However, I can not generate pdf invoice. It is only giving the output in html format.
    Am I missing any configuration. Please help.
    Thanks
    Angsuman
  • Tim Dexter Friday, May 28, 2010
    Hi Angsuman
    If you have written an XSL stylesheet that will only generate HTML then that is all that will be generated. You should either write an XSL-FO template or even better for you and your users, an RTF template. Both of these will generate other formats
    Tim
  • Angshuman Mukherjee Friday, May 28, 2010
    Hi Tim,
    Thank you for your reply. I also tried with xsl-fo but failed to generate pdf. I have installed BI Publisher in HP Unix and it is basic standalone installation. When I was laoding the template, it was showing successfully uploaded but actually it was not showing the file after uploading . Also. when I tried to save the template, it wa giving error.
    Next, I checked the directory where the xsl-fo was uploading and I could see the file in that directory in server.
    What might be the cause of this?
    Thanks
    Angsuman
  • Harnam Singh Tuesday, June 29, 2010
    I designed a BIP report in RTF template and used XSL template to display recursive data. Like below:
    Template Recursive
    For Each Xa Attribute Value Next Xa
    For Each Child Quote Item
    Call Template
    Next Child
    End Template
    For Top Level Quote Item
    Call Template
    Next Top Level Quote Item
    It works fine from rtf but when configuring in Siebel CRM, it always gives blank data.
  • Harnam Singh Tuesday, June 29, 2010
    URGENT HELP is highly appreciated:
    designed a BIP report in RTF template and used XSL template to display recursive data. Like below:
    Template Recursive
    For Each Xa Attribute Value Next Xa
    For Each Child Quote Item
    Call Template
    Next Child
    End Template
    For Top Level Quote Item
    Call Template
    Next Top Level Quote Item
    It works fine from rtf but when configuring in Siebel CRM, it always gives blank data.
  • Ignacia Wernz Sunday, September 12, 2010
    I'm getting lots of interesting info from this site.
  • agnes Friday, June 21, 2013

    Hi Tim,

    I have some issues with xsl file.

    The xsl could not display more than 200 rows. My view has more than 200 rows but only the first 200 rows are displayed.

    can you help.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content