Oracle BI Publisher Blog

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.

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.)

You will then be prompted for the source and target XSDs

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.