Wednesday, March 3, 2010

OBIEE: XML File as Data Source

I received a question in the form of a comment on using XML files here. Fittingly, we were discussing this exact topic yesterday and I decided I would try it out.

So here goes.

You'll need 2 files: a XML file and a XSL stylesheet (which defines the XML file). I found said files here. I'll also provide them here (cdcatalog.xml) and here (cdcatalog.xls).

In the Administration tool, go to File --> Import --> from Database

file import from database

When you prompted to select a datasource, select XML and the window should look like this

XML blank

For URL, browse to the location you saved cdcatalog.xml, do the same for XSLT

XML Full

Hit OK and you'll be prompted with the Import dialog

Import Dialog

Import the "table" and you're done.

XML Table! FTW!
at

5 comments:

  1. EmbeegeeMarch 5, 2010 at 5:22 PM

    Thanks for showing it was simple as that. The XML I had was a bit complex and the first time I'd imported it I'd got an error (nQSerror 64026: The structure of the XML file is not currently supported). After looking at your example, I simplified my XML and the import worked. To be more specific there were two hierarchies within the root node - when reduced to one, the import worked.
    Now, I've to look up something for the business modeling :). I am new to it!

    -MBG

    ReplyDelete
  2. AkkiDaddySeptember 5, 2010 at 11:34 AM

    Nice post. Thank you.
    Can you help me know how to load multiple xml files at once to a Physical layer in OBIEE Admin tool.

    I tried, but it needs me to import one at a time with a new connection pool everytime.

    Thanks,
    Mahesh.

    ReplyDelete
  3. oraclenerdSeptember 7, 2010 at 1:38 PM

    @AkkiDaddy,

    I've never done that. I might have to give it a whirl soon and see how it might be possible. If you figure it out before I do, feel free to post it here. I'd even let you do a guest post if you were so inclined.

    chet

    ReplyDelete
  4. ArtFebruary 15, 2011 at 4:52 PM

    Do you have any advice on mixed sources such as a table with some relational columns and an XMLType column (we are using Oracle 11gR1 and 11gR2). Thanks much.

    ReplyDelete
  5. oraclenerdFebruary 16, 2011 at 1:29 PM

    @art

    I do not have any experience using the XML type data-type with OBIEE, in fact, I'm not sure if I have even used it at all.

    Would be interesting to figure it out though. I imagine a similar concept would apply; in that you would need some sort of xsd document that describes the column structure. I might have to put this on the research list...

    ReplyDelete